34,838
社区成员




create TABLE #TT (a INT,b INT)
INSERT INTO #TT
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) '大于20的',
sum(case when b>40 then 1 else 0 end) '大于40的'
from #TT
大于20的 大于40的
----------- -----------
3 1
(1 行受影响)
create table #tb1(a int,b int )
--drop table #tb1
insert into #tb1
select 1,10 union all
select 2,20 union all
select 3,30 union all
select 4,40 union all
select 5,50
--select * from #tb1
select
sum(case when b>20 then 1 else 0 end) as '>20',
sum(case when b>40 then 1 else 0 end) as '>40'
from #tb1
SELECT
'>20',count([b])
FROM [test2]
where [b]>20
union all
SELECT
'>40',count([b])
FROM [test2]
where [b]>40
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([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
'>20',
sum(case when b>20 then 1 else 0 end)
from [tb]
union all
select
'>40',
sum(case when b>40 then 1 else 0 end)
from [tb]
---结果---
---- -----------
>20 3
>40 1
(所影响的行数为 2 行)
---------------------------------
-- 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
select count(1) result from tab where b > 20
union
select count(1) result from tab where b > 40
SELECT
CASE WHEN B>=40 THEN '>40'
WHEN B>=20 THEN '>20' END ,
COUNT(*)
FROM tb
GROUP BY
CASE WHEN B>=40 THEN '>40'
WHEN B>=20 THEN '>20' END
create TABLE #TT (a INT,b INT)
INSERT INTO #TT
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50
select case when b>20 then b end '大于20的',
case when b >40 then b end '大于40的'
from #TT
ORDER BY b desc
/*
大于20的 大于40的
----------- -----------
50 50
40 NULL
30 NULL
NULL NULL
NULL NULL*?
(5 行受影响)