在SQL
表互斥计数，我需要查询看起来像这样在SQL
ID - Account - Product
1 - 002 - Bike
2 - 003 - Bike
4 - 003 - Motor
5 - 004 - Car
我需要能够检索所购买的每一种产品和产品组合的账户数量，像这
Bike | Car | Motor | Bike&Car | Bike&Motor | Car&Motor | Bike&Car&Motor
注意，所购产品组合的帐户将被算作1
请帮我在这个检索数据。
您可以使用两级聚合来完成此操作。一种方法是把不同的行中的值：
select has_bike, has_motor, has_car, count(*)
from (select account,
max(case when product = 'bike' then 1 else 0 end) as has_bike,
max(case when product = 'motor' then 1 else 0 end) as has_motor,
max(case when product = 'car' then 1 else 0 end) as has_car
from t
group by account
) t
group by has_bike, has_motor, has_car;
或在列：
select sum(has_bike * (1 - has_motor) * (1 - has_car)) as has_only_bike,
sum((1 - has_bike) * has_motor * (1 - has_car)) as has_only_motor,
sum((1 - has_bike) * (1 - has_motor) * has_car) as has_only_car,
. . .
from (select account,
max(case when product = 'bike' then 1 else 0 end) as has_bike,
max(case when product = 'motor' then 1 else 0 end) as has_motor,
max(case when product = 'car' then 1 else 0 end) as has_car
from t
group by account
) t;
这是否排除每个计数彼此。因为我得到了相当大的结果。我的意思是，例如，如果一个账户购买汽车和汽车，该账户不应该被计入汽车和汽车。非常感谢您的帮助！ –
@MaryRoseVillanueva。 。 。 “group by”的第一个版本绝对不会超支。第二，你必须确保表达是正确的。 –
使用第一个版本！非常感谢您的帮助！ –
如果你只有一组有限的Product S，那么你可以使用这个：
-- Create sample data
CREATE TABLE #tbl(
ID INT,
Account VARCHAR(10),
Product VARCHAR(10)
);
INSERT INTO #tbl VALUES
(1, '002', 'Bike'),
(2, '003', 'Bike'),
(3, '003', 'Motor'),
(4, '004', 'Car');
WITH Cte AS(
SELECT t1.Account, a.Products
FROM #tbl t1
CROSS APPLY (
SELECT STUFF((
SELECT '&' + t2.Product
FROM #tbl t2
WHERE t2.Account = t1.Account
ORDER BY t2.Product
FOR XML PATH(''), type).value('.[1]','nvarchar(max)'),
1, 1, '') AS Products
) a
GROUP BY t1.Account, a.Products
)
SELECT
Bike = SUM(CASE WHEN Products = 'Bike' THEN 1 ELSE 0 END),
Car = SUM(CASE WHEN Products = 'Car' THEN 1 ELSE 0 END),
Motor = SUM(CASE WHEN Products = 'Motor' THEN 1 ELSE 0 END),
[Bike&Car] = SUM(CASE WHEN Products = 'Bike&Car' THEN 1 ELSE 0 END),
[Bike&Motor] = SUM(CASE WHEN Products = 'Bike&Motor' THEN 1 ELSE 0 END),
[Car&Motor] = SUM(CASE WHEN Products = 'Car&Motor' THEN 1 ELSE 0 END),
[Bike&Car&Motor] = SUM(CASE WHEN Products = 'Bike&Car&Motor' THEN 1 ELSE 0 END)
FROM Cte;
DROP TABLE #tbl; -- Remove sample data
这个想法是为每个Account生成1行，并附逗号分隔的Products。如果执行CTE内的查询，您将获得：
Account Products
---------- ---------------
002 Bike
003 Bike&Motor
004 Car
有了这一点，你可以做一个有条件的聚集。以上使用的是静态解决方案，如果您不知道Product的数量，您可能需要提出动态方法。
所以，如果一个帐户购买了一辆自行车和一辆车，如何理货看？在自行车，汽车，自行车和汽车中是否有_three_条目，或者只有一个条目？你尝试过什么吗？ –
和别人怎么知道它是一个组合？ – Amit
@TimBiegeleisen表示一个账户只能购买一台电机，该电机将包含在电机数量中，但如果该账户还购买了一辆汽车，他将被从电机计数中删除，并将包含在汽车和汽车的计数中。购买每个产品的单个条目。 –