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

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
-----------------
--但如果数据量很多的话,速度很慢,求其他方法

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

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

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

[解决办法]

那你先把完整的语句搞出来
[解决办法]
如果你的查询暂时改不了的话，把你的表结构和索引情况贴出来，你这个语句开销100%都分布在排序上面
[解决办法]
好像没啥好改的
[解决办法]
那你把商品名称也放到group by中咯，不知道合不合你的需求
[解决办法]
你的查询case when又不能去掉，好像改不了了。慢的确是在distinct上面，加索引可以加快，也考虑一下这样写的数据是否一样：
SELECT  lsh ,
        dm ,
        COUNT(DISTINCT ( sp ))
FROM    cte
WHERE   bz >= 0
        AND sp IS NOT NULL
GROUP BY lsh ,
        dm
        
[解决办法]
引用:
SQL code?12345678910111213141516171819202122232425WITH cte AS( SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL SELECT '1234','23-DF',NULL      UNION ALL SELECT '1343','24-DF','大白兔2' UNIO…… 
 

呵呵 20分就想解决这么大的事情，在3个字段上都无脑的加上索引应该会快点。
distinct 应该是去不掉的，没有你的数据像这种统计信息当以一句话直接得出的话，很难去搞懂sql自身的优化逻辑。
[解决办法]
引用:
引用:引用:SQL code?12345678910111213141516171819202122232425WITH cte AS( SELECT lsh='1234',dm='23-DF',sp='大白兔1' UNION ALL SELECT '1234','23-DF',NULL    ……

 开个玩笑而已就是告诉你其实大家都不看重分数，只是在于会不会而已，会的就帮忙解答一下，不会看分少会也不告诉你。
[解决办法]
莫法改了

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

[解决办法]
SELECT  lsh , dm ,COUNT(1) as Number FROM cte GROUP BY lsh ,  dm ,sp 这样行不