日期:2014-05-17 浏览次数:20465 次
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