如何根据一个列的多个值选择distinct

Question

我觉得这是一件非常简单的任务，我想得太多了。我看了又看，似乎没有什么能回答我的问题。我的问题很简单，如何根据另一列的多个值在一列上选择distinct:

表:

tbody> <<tr>鲍勃萨利萨利山姆山姆汤姆汤姆

顾客	水果
鲍勃	橙色
	苹果
	橙色
	柠檬
	柠檬
	苹果
	橙色
	石灰

假设您不关心用户是否拥有所有3个属性，您仍然希望返回它们。或者如果它们有多于2个，但至少是橙色和其他2个中的一个。

只是使用一个简单的连接。买橙色的和苹果或柠檬的——>加入——比;不同。

SELECT Distinct A.Customers
FROM dbo.yourTable A
INNER JOIN dbo.yourTable B
on A.Customer = B.Customer
WHERE A.Fruit = 'Orange'
and B.Fruit in ('Apple','Lemon')

假设在dbo.yourTable.Fruit…应该很快。

像这样?

SELECT Customers
FROM dbo.YourTable
GROUP BY Customers
HAVING (COUNT(CASE Fruit WHEN 'Orange' THEN 1 END) >= 1 AND COUNT(CASE Fruit WHEN 'Apple' THEN 1 END) >= 1)
OR (COUNT(CASE Fruit WHEN 'Orange' THEN 1 END) >= 1 AND COUNT(CASE Fruit WHEN 'Lemon' THEN 1 END) >= 1);

使用条件聚合:

SELECT Customer
FROM tablename
WHERE Fruit IN ('Orange', 'Apple', 'Lemon')
GROUP BY Customer
HAVING COUNT(CASE WHEN Fruit = 'Orange' THEN 1 END) > 0 -- the customer bought 'Orange' at least once
AND COUNT(DISTINCT Fruit) > 1 -- the customer bought at least 2 of the 3 fruits

如果您想排除顾客购买了所有3种水果的情况:

SELECT Customer
FROM tablename
WHERE Fruit IN ('Orange', 'Apple', 'Lemon')
GROUP BY Customer
HAVING COUNT(CASE WHEN Fruit = 'Orange' THEN 1 END) > 0 -- the customer bought 'Orange' at least once
AND COUNT(DISTINCT Fruit) = 2 -- the customer bought exactly 2 of the 3 fruits

一个有趣的选择，使用不被重视的INTERSECT

SELECT Person FROM YourTable WHERE Fruit = 'Apple' 
INTERSECT -- INTERSECT discards duplicates
SELECT Person FROM YourTable WHERE Fruit = 'Orange'
UNION -- UNION also does an implicit DISTINCT
SELECT Person FROM YourTable WHERE Fruit = 'Lemon' 
INTERSECT 
SELECT Person FROM YourTable WHERE Fruit = 'Orange'

Select Customers, count (distinct Fruit)
from YourTable 
having count (distinct Fruits) > 1
group by Customers