交叉倍增查询
我喜欢获取尚未包含在Customer_Machine表中的序列号。但相反,这些行是交叉相乘。这是我的代码。交叉倍增查询
SELECT serial_number.serial_no
FROM product
INNER JOIN serial_number
ON product.productid = serial_number.productid
INNER JOIN customer_machine
ON NOT(customer_machine.serial_no = serial_number.serial_no)
任何帮助吗?
如果你加入Product
和serial_number
表,结果将是serial numbers which are in products but not in customer_machine
。由于上述答案处理该情况,因此此查询将使用left join
并忽略Product
表来选择serial numbers which are not in customer_machine
(如您所述)。
select s.serial_no
from serial_number s
left join customer_machine c on s.serial_no = c.serial_no
where c.serial_no is null
这应该这样做:
SELECT serial_number.serial_no
FROM product P
INNER JOIN serial_number S
ON P.productid = S.productid
WHERE NOT EXISTS (SELECT 1 FROM customer_machine
WHERE serial_no = S.serial_no)
你现在正在做的,与customer_machine
从serial_number
不与customer_machine
相同数量的匹配连接,每serial_no
;所以你正在增加你的结果。
感谢的人:)这肯定有助于:)解决我的问题。 – 2013-03-13 19:27:28
很高兴帮助你 – Lamak 2013-03-13 19:29:34
SELECT serial_number.serial_no
FROM product
INNER JOIN serial_number
ON product.productid = serial_number.productid
LEFT JOIN customer_machine
ON (customer_machine.serial_no = serial_number.serial_no)
WHERE customer_machine.serial_no IS NULL
我认为你想从连接条件中获取'customer_machine.serial_no IS NULL'条件并将它添加到'WHERE',否则它将不会过滤结果 – Lamak 2013-03-13 18:48:16
是的,你是对。固定。谢谢。 – 2013-03-13 18:53:06
你可能只是想排除已存在的特定序列号,而不是将它们连接您可以在WHERE子句中做到这一点:
SELECT serial_number.serial_no
FROM product
INNER JOIN serial_number
ON product.productid = serial_number.productid
WHERE serial_number.serial_no NOT IN (SELECT customer_machine.serial_no
FROM customer_machine)
一些示例数据,所需的输出等等 – 2013-03-13 18:35:27