問題描述

? 為什么第一個query不正確？ 求sales person: query 1: select?SalesPerson.name from Orders inner join Company on Company.com_id = Orders.com_id inner join SalesPerson on SalesPerson.sales_id = Orders.sales_id where Company.name<>'RED';query 2: select name from salesperson where sales_id not in (select orders.sales_id from orders inner join company on company.com_id=orders.com_id where company.name='RED')注：原需求是輸出salesperson表中沒有向公司RED 銷售任何東西的銷售員?

數據補充

--- 有如下表、結構及數據，計用SQL編寫出 -- 輸出salesperson表中沒有向公司RED 銷售任何東西的銷售員DROP TABLE IF EXISTS `company`; CREATE TABLE `company` (`com_id` int(11) NULL DEFAULT NULL,`NAME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4;INSERT INTO `company` VALUES (1, 'RED', 'Boston'); INSERT INTO `company` VALUES (2, 'ORANGE', 'New York'); INSERT INTO `company` VALUES (3, 'YELLOW', 'Boston'); INSERT INTO `company` VALUES (4, 'GREEN', 'Austin');DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` (`order_id` int(11) NULL DEFAULT NULL,`order_date` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`com_id` int(11) NULL DEFAULT NULL,`sales_id` int(11) NULL DEFAULT NULL,`amount` int(11) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4;INSERT INTO `orders` VALUES (1, '1/1/2014', 3, 4, 100000); INSERT INTO `orders` VALUES (2, '2/1/2014', 4, 5, 5000); INSERT INTO `orders` VALUES (3, '3/1/2014', 1, 1, 50000); INSERT INTO `orders` VALUES (4, '4/1/2014', 1, 4, 25000);CREATE TABLE `salesperson` (`sales_id` int(11) NULL DEFAULT NULL,`NAME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`salary` int(11) NULL DEFAULT NULL,`commission_rate` int(11) NULL DEFAULT NULL,`hire_date` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4;INSERT INTO `salesperson` VALUES (1, ' John ', 100000, 6, ' 4/1/2006'); INSERT INTO `salesperson` VALUES (2, ' Amy ', 120000, 5, ' 5/1/2010 '); INSERT INTO `salesperson` VALUES (3, ' Mark ', 65000, 12, ' 12/25/2008'); INSERT INTO `salesperson` VALUES (4, ' Pam ', 25000, 25, ' 1/1/2005 '); INSERT INTO `salesperson` VALUES (5, ' Alex ', 50000, 10, ' 2/3/2007 ');

解決方法

#2 解決方法： -- way1用LEFT JOIN結合IFNULL。 -- 解釋1 這里要取得銷售人員名在salesperson，所以它是主表。 -- 解釋2 通過LEFT JOIN WHERE IS NULL達到NOT IN的效果。SELECT A.name,D.sales_id FROM salesperson as A LEFT JOIN (SELECT C.sales_id FROM orders as CJOIN company as BON B.com_id = C.com_idWHERE B.name= "RED") D ON A.sales_id = D.sales_id WHERE D.sales_id IS NULL -- way2 用NOT IN select name from salesperson where sales_id not in (select orders.sales_id from orders inner join company on company.com_id=orders.com_id where company.name='RED')

問題分析

-- Query1的解釋: /* Query1 用的是INNER JOIN結果不對 1 首先記錄上不對，LEFT JOIN和INNER JOIN的結果不同 2 像 Pam既銷售給了“RED”又銷售給了“YELLOW”但INNER JOIN的where company.name<>'RED'又給“找回來”了是不對的。 */

總結

以上是生活随笔為你收集整理的SQL LEFT JOIN与IN使用案例说明的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。