22,300
社区成员




if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data(
name varchar(10),
age int)
INSERT INTO #Tmp_Data
Select 'a',20 union
Select 'b', 30 union
Select 'c',20
Select Name,Sum(Value) as Value
From (
Select Case when age between 20 and 29 then 'F6' else 'U6' end as Name,
1 as Value
From #Tmp_Data
) a
Group By Name
Name Value
---- -----------
F6 2
U6 1
(2 row(s) affected)
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([name] nvarchar(21),[age] int)
Insert #A
select N'a',20 union all
select N'b',30 union all
select N'c',20
SELECT
*
FROM
(
SELECT
sum(case when age between 20 and 29 then 1 else 0 end) as F6,
sum(case when age between 30 and 39 then 1 else 0 end) as U6
from #A
) AS a
UNPIVOT
(value FOR name IN (U6,F6)) AS b
/*
value name
1 U6
2 F6
*/
SELECT CASE WHEN age BETWEEN 20 AND 29 THEN 'F6'
WHEN age BETWEEN 30 AND 39 THEN 'U6'
ELSE ''
END AS name ,
COUNT(1) AS VALUE
FROM #A
GROUP BY CASE WHEN age BETWEEN 20 AND 29 THEN 'F6'
WHEN age BETWEEN 30 AND 39 THEN 'U6'
ELSE ''
END
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([name] nvarchar(21),[age] int)
Insert #A
select N'a',20 union all
select N'b',30 union all
select N'c',20
Go
--测试数据结束
SELECT CASE WHEN age BETWEEN 20 AND 29 THEN 'F6'
WHEN age BETWEEN 30 AND 39 THEN 'U6'
ELSE ''
END AS name ,
COUNT(1) AS VALUE
FROM #A
GROUP BY age
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([name] nvarchar(21),[age] int)
Insert #A
select N'a',20 union all
select N'b',30 union all
select N'c',20
Go
--测试数据结束
select
'F6' AS name,
sum(case when age between 20 and 29 then 1 else 0 end) as VALUE
from #A
UNION
select
'U6' as name,
sum(case when age between 30 and 39 then 1 else 0 end) as value
from #A