求一个分组求和的SQL查询语句

Question

WITH cte AS(
 SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL
 SELECT '1234','23-DF',NULL      UNION ALL
 SELECT '1343','24-DF','大白兔2' UNION ALL
 SELECT '1234','23-DF','大白兔1' UNION ALL
 SELECT '1343','24-DF','白兔1'   UNION ALL  
 SELECT '1234','23-DF','大白兔1' 
)
--我想统计一下同一个lsh,dm下有多少种不同的商品,如果商品名称相同则算作是只有一种
SELECT  lsh ,
        dm ,
        COUNT(DISTINCT ( CASE WHEN ISNULL(sp, '') = '' THEN NULL
                              ELSE sp
                         END ))
FROM    cte
GROUP BY lsh ,
        dm
-----------------
--结果
lsh  dm    
---- ----- -----------
1234 23-DF 1
1343 24-DF 2
-----------------
--但如果数据量很多的话,速度很慢,求其他方法

Answer 1

用2次聚合函数？WITH cte AS(  
SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL 
SELECT '1234','23-DF',NULL      UNION ALL 
SELECT '1343','24-DF','大白兔2' UNION ALL 
SELECT '1234','23-DF','大白兔1' UNION ALL 
SELECT '1343','24-DF','白兔1'   UNION ALL   
SELECT '1234','23-DF','大白兔1' ) 
,ctte as(
select lsh,dm,a=count(1) from cte where sp is not null group by lsh,dm,sp)
select lsh,dm,count(1) from ctte group by lsh,dm

Answer 2

WITH cte AS(  
SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL 
SELECT '1234','23-DF',NULL      UNION ALL 
SELECT '1343','24-DF','大白兔2' UNION ALL 
SELECT '1234','23-DF','大白兔1' UNION ALL 
SELECT '1343','24-DF','白兔1'   UNION ALL   
SELECT '1234','23-DF','大白兔1' ) 
select lsh,dm,count(1) from (select lsh,dm,a=count(1) from cte where sp is not null group by lsh,dm,sp)a
 group by lsh,dm

Answer 3

WITH cte AS(  
SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL 
SELECT '1234','23-DF',NULL      UNION ALL 
SELECT '1343','24-DF','大白兔2' UNION ALL 
SELECT '1234','23-DF','大白兔1' UNION ALL 
SELECT '1343','24-DF','白兔1'   UNION ALL   
SELECT '1234','23-DF','大白兔1' ) 
select lsh,dm,count(distinct lsh+dm+sp) from cte group by lsh,dm
这样？

Answer 4

哪个效率高点？能不能不用distinct?

Answer 5

distinct 排序消耗的开销太大了

Answer 6

语句建议改为下面的SELECT  lsh , dm , COUNT(DISTINCT sp)
FROM    cte
WHERE   sp <> ''
GROUP BY lsh , dm你的cte 是个里面是个查询还是就是你写的用union all填充数据
考虑改成临时表，在lsh , dm 列上建一个联合索引就ok了

Answer 7

05 以上可以试下 下面的索引方式create index on tb(lsh , dm) include(sp)

Answer 8

问题没描述清晰，我是想统计相同的lsh,dm下面有“多少种”sp

Answer 9

那你里面的case when 是什么个情况
其实这个没有太大影响你这个关键的从索引着手。

Answer 10

case when 的是另外一个字段，我忘记加了

Answer 11

这个直接貌似没那么复杂吧。
CREATE CLUSTERED INDEX b ON t(lsh,dm)
CREATE NONCLUSTERED INDEX a ON t(lsh,dm,sp)
SELECT lsh , 
        dm , 
        COUNT(DISTINCT (sp)) 
FROM t
GROUP BY lsh,dm
我把你的数据放到T表里面

Answer 12

case when 是肯定要要的，when后面的是另外一个字段，我忘记写了，我想把distinct给去了，不知道怎么搞。

Answer 13

WITH cte AS(
 SELECT lsh='1234',dm='23-DF',sp='大白兔1',bz=1 UNION ALL
 SELECT '1234','23-DF',NULL,2      UNION ALL
 SELECT '1343','24-DF','大白兔2',0 UNION ALL
 SELECT '1234','23-DF','大白兔1',-1 UNION ALL
 SELECT '1343','24-DF','白兔1',2   UNION ALL  
 SELECT '1234','23-DF','大白兔1',1 
)
--我想统计一下同一个lsh,dm下有多少种不同的商品,如果商品名称相同则算作是只有一种
SELECT  lsh ,
        dm ,
        COUNT(DISTINCT ( CASE WHEN bz<0 THEN NULL
                              ELSE ISNULL(sp,'')
                         END ))
FROM    cte
GROUP BY lsh ,
        dm

Answer 14

我不想要distinct啊，能不能换换？

Answer 15

那你把商品名称也放到group by中咯，不知道合不合你的需求

Answer 16

我只想统计lsh跟dm下面有多少种sp

Answer 17

你的查询case when又不能去掉，好像改不了了。慢的确是在distinct上面，加索引可以加快，也考虑一下这样写的数据是否一样：
SELECT  lsh ,
        dm ,
        COUNT(DISTINCT ( sp ))
FROM    cte
WHERE   bz >= 0
        AND sp IS NOT NULL
GROUP BY lsh ,
        dm
        

Answer 18

呵呵 20分就想解决这么大的事情，在3个字段上都无脑的加上索引应该会快点。
distinct 应该是去不掉的，没有你的数据像这种统计信息当以一句话直接得出的话，很难去搞懂sql自身的优化逻辑。

Answer 19

莫法改了SELECT  lsh , dm ,COUNT(DISTINCT ( CASE WHEN ISNULL(sp, '') = '' THEN NULL 
ELSE sp  END )) FROM cte GROUP BY lsh ,  dm 

Answer 20

SELECT  lsh , dm ,COUNT(1) as Number FROM cte GROUP BY lsh ,  dm ,sp 这样行不