请教一个SQL (另开一贴)

[zedan]

表数据：
a b
1 10
2 20
3 30
4 40
5 50

请帮忙写个SQL，得到表里的数据 b列大于20和大于40的各有多少条数据，即

result:
>20 3
>40 1

先谢谢了。

---------------------------------
-- Author: liangCK 小梁
---------------------------------

--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50

--SQL查询如下:

SELECT
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END
HAVING CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END IS NOT NULL


这个SQL得到的结果是

大于20的 大于40的
----------- -----------
20 50
30
40
---3个 --1个
而真正要的结果是
大于20的 大于40的
----------- -----------
30 50
40
50
---3个 --1个 区别在于大于40的数据‘50’是同时属于大于20的

[yangbiqing362937154]

上面的写的太好了，受教了

[bqb]

DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50

select '>20',cnt=count(*)from @t where b>20
union all
select '>40',cnt=count(*) from @t where b>40

/*

--------------
>20 3
>40 1

*/

[jinjazz]

小梁这个没错吧

[CeShenBeiWang]

沙发 看下先

[zedan]

[Quote=引用 2 楼 jinjazz 的回复:]
小梁这个没错吧
[/Quote]

没错？
SELECT
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END ,

>40的数据，也肯定要在>20里面，现在两者都分开了

[csdyyr]

DECLARE @TB TABLE(a INT, b INT)
INSERT @TB
SELECT 1,  10 UNION ALL 
SELECT 2,  20 UNION ALL 
SELECT 3,  30 UNION ALL 
SELECT 4,  40 UNION ALL 
SELECT 5,  50

SELECT SUM(CASE WHEN B>20 THEN 1 ELSE 0 END) AS [>20],
	   SUM(CASE WHEN B>40 THEN 1 ELSE 0 END) AS [>50]
FROM @TB

[中国风]

DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50

;with c
as
(
SELECT  
	[>40]=sum(CASE WHEN B>40 THEN 1 else 0 end),
	[>20]=sum(case WHEN B>=20 THEN 1 else 0 END)
FROM @T
)
select 
Col,con
from 
c
unpivot
(con for Col in([>40],[>20]) )t


(5 個資料列受到影響)
Col                                                                                                                              con
-------------------------------------------------------------------------------------------------------------------------------- -----------
>40                                                                                                                              1
>20                                                                                                                              4

(2 個資料列受到影響)

[中国风]

去掉等於

DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50

;with c
as
(
SELECT  
	[>40]=sum(CASE WHEN B>40 THEN 1 else 0 end),
	[>20]=sum(case WHEN B>20 THEN 1 else 0 END)
FROM @T
)
select 
Col,con
from 
c
unpivot
(con for Col in([>40],[>20]) )t


(5 個資料列受到影響)
Col                                                                                                                              con
-------------------------------------------------------------------------------------------------------------------------------- -----------
>40                                                                                                                              1
>20                                                                                                                              3

(2 個資料列受到影響)

[knifewei]

--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50

SQL查询如下:

(SELECT
CASE WHEN B>20 THEN '>20'
END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>20 THEN '>20'
END
HAVING CASE WHEN B>20 THEN '>20'
END IS NOT NULL )
union all
(SELECT
CASE WHEN B>40 THEN '>40'
END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
END
HAVING CASE WHEN B>40 THEN '>40'
END IS NOT NULL )


修改的小梁的，测试通过，呵呵

[yibey84]

select a,case a when '>40' then b else a+b from (SELECT
(CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END) as a ,
COUNT(*) as b
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END
HAVING CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END IS NOT NULL )
试下这个看

[knifewei]

如果是>=20的话是20，30，40，50四个数据，呵呵

[taoistong]

DECLARE @T TABLE (a INT,b INT) 
INSERT INTO @T 
SELECT 1,10 UNION ALL 
SELECT 2,20 UNION ALL 
SELECT 3,30 UNION ALL 
SELECT 4,40 UNION ALL 
SELECT 5,50 


select '20' a,(select COUNT(1) from @T where b>20) b
union all
select '40' a,(select COUNT(1) from @T where b>40) b

何必那么麻烦

[zedan]

unpivot
听说是2005才有的，要兼容下2000的啊，
另外不希望要 ‘union all'的，性能不太好吧

[ws_hgo]

昨天不是回复了吗

[让你望见影子的墙]

select distinct a='>20', cnt=(select count(*) from tb where b>20)     
from tb
union all
select distinct '>40',cnt=(select count(*) from tb where b>40)      
from tb

 a    cnt
---- -----------
>20  3
>40  1

(2 行受影响)

[knifewei]

union all的性能不差，差的是union（因为要排序去重复）

[中国风]

2000

DECLARE @T TABLE (a INT,b INT) 
INSERT INTO @T 
SELECT 1,10 UNION ALL 
SELECT 2,20 UNION ALL 
SELECT 3,30 UNION ALL 
SELECT 4,40 UNION ALL 
SELECT 5,50 

select 
	'>'+t.col as col,count(t2.a) as con
from 
	(select '20' as col union all select '40' as col)t
cross join 
	@T t2
where t.col<t2.b 
group by t.col