Join
如何在子查詢中使用左連接
我在名為 order_mgmt_sys 的數據庫中有以下一組表（請注意，數據庫是靜態的，我無法更改表）
mysql> show tables; +--------------------------+ | Tables_in_order_mgmt_sys | +--------------------------+ | customer_details | | order_details | | purchase_details | +--------------------------+CREATE TABLE `customer_details` ( `cust_id` bigint(11) NOT NULL AUTO_INCREMENT, `cust_name` varchar(50) NOT NULL, `cust_contact_1` bigint(11) NOT NULL, `cust_contact_2` bigint(11) DEFAULT NULL, `cust_email_id` varchar(60) DEFAULT NULL, `cust_address` varchar(200) NOT NULL, PRIMARY KEY (`cust_id`) )CREATE TABLE `purchase_details` ( `pur_id` int(10) NOT NULL AUTO_INCREMENT, `order_id` int(10) NOT NULL, `item_type` varchar(20) NOT NULL, `qty` int(8) NOT NULL, `billing_amount` float NOT NULL, PRIMARY KEY (`pur_id`), KEY `FK_order_id` (`order_id`), CONSTRAINT `FK_order_id` FOREIGN KEY (`order_id`) REFERENCES `order_details` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE )CREATE TABLE `order_details` ( `order_id` int(10) NOT NULL AUTO_INCREMENT, `cust_id` bigint(11) NOT NULL, `delivery_address` varchar(200) NOT NULL, `delivery_date` datetime NOT NULL DEFAULT (_cp850'2019-08-12'), PRIMARY KEY (`order_id`), KEY `FK_cust_id` (`cust_id`), CONSTRAINT `FK_cust_id` FOREIGN KEY (`cust_id`) REFERENCES `customer_details` (`cust_id`) )我希望表 order_details 和 purchase_details 保持連接，並且應該只給我具有最大 billing_amount 的元組。（這裡應該注意，我對 SQL 完全陌生，並且不知道其中的大部分概念。所以請不要根據它們的愚蠢程度或它們造成的不便來判斷我的查詢。所以請忍受我的可怕查詢😅）
我就是這樣進行的：
query 1:select max(billing_amount) from purchase_details; +---------------------+ | max(billing_amount) | +---------------------+ | 486753.03125 | +---------------------+
query 2:select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details); +----------+ | order_id | +----------+ | 27 | +----------+
queries with issue:expected result: +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+ | order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount | +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+ | 27 | 21 | Esker | 2010-11-29 05:42:55 | 47 | 27 | House Decor | 77 | 486753 | +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+ select * from order_details left outer join purchase_details on purchase_details.order_id=(select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details)); +----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+ | order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount | +----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+ | 1 | 29 | Maywood | 2001-07-13 12:49:20 | 47 | 27 | House Decor | 77 | 486753 | | 4 | 37 | Hauk | 2015-07-03 09:43:44 | 47 | 27 | House Decor | 77 | 486753 | | 5 | 20 | Milwaukee | 2014-09-27 23:28:28 | 47 | 27 | House Decor | 77 | 486753 | | 7 | 46 | Westerfield | 2009-10-01 16:02:50 | 47 | 27 | House Decor | 77 | 486753 | | 8 | 43 | Bayside | 2019-02-07 01:36:52 | 47 | 27 | House Decor | 77 | 486753 | | 9 | 40 | Rieder | 2001-07-15 22:34:30 | 47 | 27 | House Decor | 77 | 486753 | | 11 | 9 | Canary | 2011-09-27 19:05:42 | 47 | 27 | House Decor | 77 | 486753 | | 12 | 34 | Transport | 2009-06-21 01:45:12 | 47 | 27 | House Decor | 77 | 486753 | | 14 | 3 | Prairieview | 2006-04-19 00:05:57 | 47 | 27 | House Decor | 77 | 486753 | | 15 | 14 | Gale | 2014-06-10 05:39:01 | 47 | 27 | House Decor | 77 | 486753 | | 16 | 5 | Porter | 2002-08-31 18:49:55 | 47 | 27 | House Decor | 77 | 486753 | | 17 | 45 | Michigan | 2011-06-15 20:43:37 | 47 | 27 | House Decor | 77 | 486753 | | 23 | 48 | Graedel | 2010-09-03 08:27:04 | 47 | 27 | House Decor | 77 | 486753 | | 24 | 22 | Bluestem | 2016-08-08 01:37:35 | 47 | 27 | House Decor | 77 | 486753 | | 26 | 24 | Barby | 2012-12-26 16:18:03 | 47 | 27 | House Decor | 77 | 486753 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 47 | 27 | House Decor | 77 | 486753 | | 32 | 19 | Cottonwood | 2009-01-06 11:24:12 | 47 | 27 | House Decor | 77 | 486753 | | 33 | 40 | Butternut | 2014-09-08 08:42:06 | 47 | 27 | House Decor | 77 | 486753 | | 34 | 5 | High Crossing | 2012-11-04 06:29:45 | 47 | 27 | House Decor | 77 | 486753 | | 35 | 42 | Stoughton | 2005-03-24 14:46:11 | 47 | 27 | House Decor | 77 | 486753 | | 38 | 17 | Hansons | 2007-09-12 06:29:07 | 47 | 27 | House Decor | 77 | 486753 | | 40 | 44 | Schiller | 2018-09-24 07:25:53 | 47 | 27 | House Decor | 77 | 486753 | | 41 | 1 | Evergreen | 2007-09-23 05:43:36 | 47 | 27 | House Decor | 77 | 486753 | | 42 | 5 | Lien | 2006-12-09 02:23:17 | 47 | 27 | House Decor | 77 | 486753 | | 43 | 16 | Sycamore | 2014-01-04 13:17:22 | 47 | 27 | House Decor | 77 | 486753 | | 44 | 39 | Dwight | 2001-12-28 04:13:28 | 47 | 27 | House Decor | 77 | 486753 | | 47 | 18 | Wayridge | 2018-11-10 02:31:10 | 47 | 27 | House Decor | 77 | 486753 | | 48 | 14 | High Crossing | 2009-03-20 20:53:11 | 47 | 27 | House Decor | 77 | 486753 | | 49 | 35 | Lighthouse Bay | 2013-11-07 16:12:59 | 47 | 27 | House Decor | 77 | 486753 | | 50 | 48 | Oriole | 2007-05-16 22:17:49 | 47 | 27 | House Decor | 77 | 486753 | +----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+ mysql> select * from order_details left outer join purchase_details on order_details.order_id=(select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details)); +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+ | order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount | +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+ | 27 | 21 | Esker | 2010-11-29 05:42:55 | 1 | 44 | Kitchware | 328 | 482370 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 2 | 50 | Electronics | 371 | 75544.5 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 3 | 24 | Furniture | 262 | 379261 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 4 | 49 | Stationery | 228 | 462698 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 5 | 32 | Electronics | 347 | 239346 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 6 | 12 | Bed-room Itenary | 154 | 363107 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 7 | 43 | Furniture | 111 | 409657 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 8 | 35 | House Decor | 231 | 102863 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 9 | 32 | Clothing | 106 | 463276 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 10 | 26 | Kitchware | 68 | 127504 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 11 | 5 | Bed-room Itenary | 57 | 159601 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 12 | 11 | Bed-room Itenary | 240 | 131163 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 13 | 38 | Electronics | 203 | 243343 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 14 | 47 | Kitchware | 338 | 44338.7 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 15 | 41 | Stationery | 427 | 50058.4 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 16 | 8 | Stationery | 441 | 256722 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 17 | 41 | Furniture | 78 | 425651 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 18 | 15 | House Decor | 477 | 182226 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 19 | 1 | Furniture | 118 | 328474 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 20 | 16 | Automobile | 219 | 474350 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 21 | 9 | Stationery | 252 | 457933 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 22 | 4 | Stationery | 433 | 471170 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 23 | 33 | Bed-room Itenary | 66 | 209335 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 24 | 17 | Automobile | 107 | 293932 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 25 | 48 | Stationery | 166 | 169077 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 26 | 7 | Kitchware | 357 | 393626 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 27 | 5 | Kitchware | 75 | 136595 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 28 | 49 | Stationery | 211 | 187452 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 29 | 15 | Automobile | 412 | 394621 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 30 | 41 | House Decor | 194 | 363274 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 31 | 14 | Furniture | 125 | 376607 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 32 | 23 | Stationery | 46 | 178141 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 33 | 48 | Automobile | 26 | 173840 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 34 | 33 | House Decor | 101 | 265796 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 35 | 48 | Electronics | 219 | 330784 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 36 | 5 | Automobile | 289 | 292930 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 37 | 9 | Bed-room Itenary | 79 | 28277.4 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 38 | 42 | Furniture | 241 | 155430 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 39 | 7 | Stationery | 159 | 150979 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 40 | 8 | Kitchware | 169 | 447978 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 41 | 35 | House Decor | 399 | 467603 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 42 | 14 | Bed-room Itenary | 484 | 305478 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 43 | 40 | Furniture | 396 | 133665 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 44 | 34 | Kitchware | 487 | 286046 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 45 | 24 | Electronics | 288 | 70668.5 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 46 | 26 | Clothing | 85 | 259455 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 47 | 27 | House Decor | 77 | 486753 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 48 | 47 | Stationery | 449 | 204334 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 49 | 15 | Furniture | 461 | 279387 | | 27 | 21 | Esker | 2010-11-29 05:42:55 | 50 | 26 | Stationery | 308 | 111537 | | 1 | 29 | Maywood | 2001-07-13 12:49:20 | NULL | NULL | NULL | NULL | NULL | | 4 | 37 | Hauk | 2015-07-03 09:43:44 | NULL | NULL | NULL | NULL | NULL | | 5 | 20 | Milwaukee | 2014-09-27 23:28:28 | NULL | NULL | NULL | NULL | NULL | | 7 | 46 | Westerfield | 2009-10-01 16:02:50 | NULL | NULL | NULL | NULL | NULL | | 8 | 43 | Bayside | 2019-02-07 01:36:52 | NULL | NULL | NULL | NULL | NULL | | 9 | 40 | Rieder | 2001-07-15 22:34:30 | NULL | NULL | NULL | NULL | NULL | | 11 | 9 | Canary | 2011-09-27 19:05:42 | NULL | NULL | NULL | NULL | NULL | | 12 | 34 | Transport | 2009-06-21 01:45:12 | NULL | NULL | NULL | NULL | NULL | | 14 | 3 | Prairieview | 2006-04-19 00:05:57 | NULL | NULL | NULL | NULL | NULL | | 15 | 14 | Gale | 2014-06-10 05:39:01 | NULL | NULL | NULL | NULL | NULL | | 16 | 5 | Porter | 2002-08-31 18:49:55 | NULL | NULL | NULL | NULL | NULL | | 17 | 45 | Michigan | 2011-06-15 20:43:37 | NULL | NULL | NULL | NULL | NULL | | 23 | 48 | Graedel | 2010-09-03 08:27:04 | NULL | NULL | NULL | NULL | NULL | | 24 | 22 | Bluestem | 2016-08-08 01:37:35 | NULL | NULL | NULL | NULL | NULL | | 26 | 24 | Barby | 2012-12-26 16:18:03 | NULL | NULL | NULL | NULL | NULL | | 32 | 19 | Cottonwood | 2009-01-06 11:24:12 | NULL | NULL | NULL | NULL | NULL | | 33 | 40 | Butternut | 2014-09-08 08:42:06 | NULL | NULL | NULL | NULL | NULL | | 34 | 5 | High Crossing | 2012-11-04 06:29:45 | NULL | NULL | NULL | NULL | NULL | | 35 | 42 | Stoughton | 2005-03-24 14:46:11 | NULL | NULL | NULL | NULL | NULL | | 38 | 17 | Hansons | 2007-09-12 06:29:07 | NULL | NULL | NULL | NULL | NULL | | 40 | 44 | Schiller | 2018-09-24 07:25:53 | NULL | NULL | NULL | NULL | NULL | | 41 | 1 | Evergreen | 2007-09-23 05:43:36 | NULL | NULL | NULL | NULL | NULL | | 42 | 5 | Lien | 2006-12-09 02:23:17 | NULL | NULL | NULL | NULL | NULL | | 43 | 16 | Sycamore | 2014-01-04 13:17:22 | NULL | NULL | NULL | NULL | NULL | | 44 | 39 | Dwight | 2001-12-28 04:13:28 | NULL | NULL | NULL | NULL | NULL | | 47 | 18 | Wayridge | 2018-11-10 02:31:10 | NULL | NULL | NULL | NULL | NULL | | 48 | 14 | High Crossing | 2009-03-20 20:53:11 | NULL | NULL | NULL | NULL | NULL | | 49 | 35 | Lighthouse Bay | 2013-11-07 16:12:59 | NULL | NULL | NULL | NULL | NULL | | 50 | 48 | Oriole | 2007-05-16 22:17:49 | NULL | NULL | NULL | NULL | NULL | +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+誰能解釋我哪裡出錯了？
您查詢：我希望表 order_details 和 purchase_details 保持連接，並且應該只給我具有最大 billing_amount 的元組。
首先，當您知道某些資訊可能在 tableA 中但不在 tableB 中的 columnX 時，通常使用 LEFT JOIN 。
在您的情況下，如果我們考慮您的架構，如果訂單存在，那麼其購買詳細資訊也應該存在，否則數據將被損壞。所以 LEFT JOIN 是不可能的。但是，如果您還想要所有客戶的詳細資訊，那麼您可以執行以下操作：
SELECT ... FROM customer_details AS cd LEFT JOIN order_details AS od ON cd.cust_id = od.cust_id LEFT JOIN purchase_details pd ON pd.order_id ON od.order_id希望這能讓您了解何時使用 LEFT JOIN。
您正在正確解決問題，但缺少概念。所以一個簡單的查詢就像：
SELECT * FROM order_details AS od INNER JOIN purchase_details AS pd ON od.order_id = pd.order_id ORDER BY billing_amount DESC LIMIT 1;希望這對您的問題有所幫助:)
測試：
WITH cte AS ( SELECT order_id, RANK() OVER (ORDER BY billing_amount DESC) rnk FROM purchase_details ) SELECT cd.*, od.*, pd.* FROM customer_details cd JOIN order_details od ON cd.cust_id = od.cust_id JOIN purchase_details pd ON od.order_id = pd.order_id JOIN cte ON od.order_id = cte.order_id WHERE cte.rnk=1 /* ORDER BY cd.cust_id, od.order_id, pd.pur_id */CTE
order_id以billing_amount遞減方式列舉所有。主查詢從所有表中收集數據並過濾組合記錄order_id中與最大值匹配的值。LEFT JOIN 在你的情況下是多餘的 - FKs 和最右邊表的條件防止不存在的部分。