只使用SQL,而不是选择组由
我有这个表:只使用SQL,而不是选择组由
supplier | product | qty
--------------------------
s1 | p1 | 300
s1 | p2 | 90
s2 | p3 | 89
我想找到供应商超过2种产品。 但只有选择和在哪里,没有group by
。任何建议?
为什么你想不使用组超越我,但是这可能工作:
SELECT Supplier FROM table outer WHERE
(
select count(Products) from table inner
where inner.Supplier = outer.Supplier
) > 2
请记住,该组由为东西做出这样的,应该被使用。
这使用一个*隐含* GROUP BY。你可能没有输入字母,但通过使用* COUNT(*)等*聚合函数,你会得到一个。 – MatBailie 2012-01-06 15:15:43
;WITH T AS
(
SELECT *,
COUNT(*) OVER (PARTITION BY S) AS Cnt
FROM YourTable
)
SELECT DISTINCT S
FROM T
WHERE Cnt > 2
+1 - 坚持使用聚合请求的信件。做得很好! – JNK 2012-01-06 14:51:37
在平时parts and suppliers database,这个relvar名为SP
:
SELECT DISTINCT T1.SNO
FROM SP AS T1
JOIN SP AS T2
ON T1.SNO = T2.SNO
AND T2.PNO <> T1.PNO
JOIN SP AS T3
ON T1.SNO = T3.SNO
AND T3.PNO <> T1.PNO
AND T3.PNO <> T2.PNO;
注意到,您可以使用HAVING
没有GROUP BY
:
SELECT DISTINCT T1.SNO
FROM SP AS T1
WHERE EXISTS (
SELECT 1
FROM SP AS T2
WHERE T2.SNO = T1.SNO
HAVING COUNT(*) > 2
);
与子查询:
select distinct supplier
from table a
where (select count(*)
from table b
where b.supplier = a.supplier and b.product <> a.product
) > 1
;WITH
sequenced_data AS
(
SELECT
supplier,
ROW_NUMBER() OVER (PARTITION BY supplier ORDER BY product) AS supplier_product_ordinal
FROM
YourTable
)
SELECT
supplier
FROM
sequenced_data
WHERE
supplier_product_ordinal = 3
但我希望它比使用GROUP BY更慢。
SELECT DISTINCT
supplier
FROM
yourTable
WHERE
EXISTS (SELECT * FROM yourTable AS lookup WHERE supplier = yourTable.supplier AND product < yourTable.product)
AND EXISTS (SELECT * FROM yourTable AS lookup WHERE supplier = yourTable.supplier AND product > yourTable.product);
+1我喜欢子查询的'>'和' onedaywhen 2012-01-06 15:41:11
哈哈,第二个查询很有趣,像我一样,但更聪明一步。无论如何,+1 – 2012-01-06 15:45:03
为什么你不希望通过使用组? – 2012-01-06 14:47:25
你的问题就像说“我想选择某个记录而不使用WHERE”。 GROUP BY存在是有原因的。此外,在这种情况下,它完全适用于您的需求。如果你想避免它,因为你不明白它,我建议你阅读它(W3学校有一个很好的网页),并学习如何正确编码。因为你不明白一个概念,所以一旦你的代码变得更加复杂,往往会在以后导致你成为大问题。 – 2012-01-06 14:48:54
您使用的是什么RDBMS? – Lamak 2012-01-06 14:50:29