如何在 MS SQL 中的单个 SELECT 查询中创建新的"shared"组

Question

我正在尝试像您通常所做的那样对 SELECT 进行分组 - 同时创建一个新的"共享/聚合组"，将其添加到原始结果集中，而无需辅助 SELECT 和 UNION。

辅助 SELECT 和 UNION 是不可能的，因为它的真正用途是一些非常大的表，有很多连接，所以放慢速度会很慢。所以UNION的方式绝对是不可能的。

我已经尽力用下面的简化示例来说明这一点：

BEGIN TRAN
CREATE TABLE #MyTable
(
    id      INT,
    name    VARCHAR(255)
)
INSERT INTO #MyTable VALUES (1,'cola');
INSERT INTO #MyTable VALUES (2,'cola');
INSERT INTO #MyTable VALUES (3,'cola');
INSERT INTO #MyTable VALUES (4,'fanta');
INSERT INTO #MyTable VALUES (5,'fanta');
INSERT INTO #MyTable VALUES (6,'fanta');
INSERT INTO #MyTable VALUES (7,'water');
INSERT INTO #MyTable VALUES (8,'water');
INSERT INTO #MyTable VALUES (9,'water');
INSERT INTO #MyTable VALUES (10,'cola');
INSERT INTO #MyTable VALUES (11,'cola');
SELECT
    CASE 
        WHEN name = 'cola' OR name = 'fanta'
            THEN 'soda'
        ELSE
            name
    END as name,
    COUNT(distinct id) as count
FROM #MyTable
GROUP BY name
ROLLBACK TRAN

Actual output:
soda    5
soda    3
water   3
Desired output:
cola    5
fanta   3
soda    8 <- this is the "shared/aggregate group"
water   3

Answer 1

正如Panagiotis Kanavos在上面的评论中正确指出的那样，这可以使用ROLLUP来完成：

BEGIN TRAN
CREATE TABLE #BeverageType
(
    name    VARCHAR(255)
)
INSERT INTO #BeverageType VALUES ('Soda');
INSERT INTO #BeverageType VALUES ('Other');
CREATE TABLE #UserBeverage
(
    id      INT,
    name    VARCHAR(255)
)
INSERT INTO #UserBeverage VALUES (1,'cola');
INSERT INTO #UserBeverage VALUES (2,'cola');
INSERT INTO #UserBeverage VALUES (3,'cola');
INSERT INTO #UserBeverage VALUES (1,'fanta'); -- <- NOTE: user 1 drinks both cola and fanta so the as intended the user is only counted 1 time in the ROLLUP 'Soda' group (7)
INSERT INTO #UserBeverage VALUES (5,'fanta');
INSERT INTO #UserBeverage VALUES (6,'fanta');
INSERT INTO #UserBeverage VALUES (7,'water');
INSERT INTO #UserBeverage VALUES (8,'water');
INSERT INTO #UserBeverage VALUES (9,'water');
INSERT INTO #UserBeverage VALUES (10,'cola');
INSERT INTO #UserBeverage VALUES (11,'cola');
SELECT ub.name, bt.name AS groupName, COUNT(distinct id) as uniqueUserCount
FROM #UserBeverage as ub
JOIN #BeverageType as bt
ON CASE
    WHEN (ub.name = 'water')
        THEN 'Other'
    ELSE
        'Soda'
    END = bt.name
GROUP BY ROLLUP(bt.name, ub.name)
ROLLBACK TRAN

输出：

cola    Soda    5
fanta   Soda    3
water   Other   3
NULL    Other   3
NULL    Soda    7
NULL    NULL    10

Answer 2

你应该在任何地方重复 CASE 语句。

SELECT
    CASE WHEN name = 'cola' OR name = 'fanta'
                    THEN 'soda' ELSE name END as name,
    COUNT((CASE WHEN name = 'cola' OR name = 'fanta'
                    THEN 'soda' ELSE name END)) as count
FROM #MyTable
GROUP BY CASE WHEN name = 'cola' OR name = 'fanta'
                    THEN 'soda' ELSE name END

+-------+-------+
| name  | count |
+-------+-------+
| soda  | 8     |
+-------+-------+
| water | 3     |
+-------+-------+

我可以建议使用子查询吗：

SELECT name, count(*) AS count
FROM (SELECT CASE WHEN name = 'cola' OR name = 'fanta'
             THEN 'soda' ELSE name END as name
      FROM #MyTable) x
GROUP BY name;

Answer 3

如果您需要聚合以及单个产品，那么另一种方法是使用 UNION 并选择聚合作为第二个查询。

选择名称，计数(非重复 id(作为计数从 #MyTable按名称分组

联盟

选择"苏打水"，计数(非重复 ID(作为计数从 #MyTable其中名称 = "可乐"或名称 ="芬达">

如果需要更多分组，还可以使用 Søren Høyer Kristensen 的汇总表来获取聚合名称。