求教sql 分组问题

[life is strange_]

table A
name age
a 20
b 30
c 20

select
"F6" AS name,
sum(case when age between 20 and 29 then 1 else 0 end) as VALUE,
"U6" as name,
sum(case when age between 30 and 39 then 1 else 0 end) as value
from A

现在的效果是
name value name1 value1
F6 2 U6 1
我想要的效果是
name value
F6 2
U6 1
请问sql应该怎么实现啊 麻烦了

[二月十六]

--测试数据
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

[二月十六]

试试这样：

--测试数据
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

[二月十六]

用这个：

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

[中国风]

e.g.

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
*/

[life is strange_]

引用 1 楼 sinat_28984567 的回复:

--测试数据
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

你好 谢谢你 可是我们项目不让用union 请问还有实现方法吗

[顺势而为1]

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)

[life is strange_]

引用 3 楼 sinat_28984567 的回复:

试试这样：

--测试数据
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

你好 不知道是我举得例子不对 还是我没写好 我们原sql是这样的： select "F6" AS name, sum(case when ch.change_zjrylb between 1 and 15 then 1 else 0 end)as VALUE, "U6" as name, sum(case when ch.change_zjrylb=22 then 1 else 0 end) as value from bc_aops_change ch , bc_sys_person p,bc_aops_dept dept where p.dw_id=dept.id and ch.change_personid=p.id; 效果是 name value name1 value1 F6 0 U6 0 现在改成： SELECT CASE WHEN ch.change_zjrylb BETWEEN 1 AND 15 THEN 'F6' WHEN ch.change_zjrylb =22 THEN 'U6' ELSE '' END AS name , COUNT(1) AS VALUE from bc_aops_change ch , bc_sys_person p,bc_aops_dept dept where p.dw_id=dept.id and ch.change_personid=p.id; GROUP BY ch.change_zjrylb; 效果变成这样了 name value 2

[二月十六]

引用 6楼我是你的主体 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:] 试试这样：

--测试数据
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

你好 不知道是我举得例子不对 还是我没写好 我们原sql是这样的： select "F6" AS name, sum(case when ch.change_zjrylb between 1 and 15 then 1 else 0 end)as VALUE, "U6" as name, sum(case when ch.change_zjrylb=22 then 1 else 0 end) as value from bc_aops_change ch , bc_sys_person p,bc_aops_dept dept where p.dw_id=dept.id and ch.change_personid=p.id; 效果是 name value name1 value1 F6 0 U6 0 现在改成： SELECT CASE WHEN ch.change_zjrylb BETWEEN 1 AND 15 THEN 'F6' WHEN ch.change_zjrylb =22 THEN 'U6' ELSE '' END AS name , COUNT(1) AS VALUE from bc_aops_change ch , bc_sys_person p,bc_aops_dept dept where p.dw_id=dept.id and ch.change_personid=p.id; GROUP BY ch.change_zjrylb; 效果变成这样了 name value 2[/quote]没有值的也要显示F6 U6吗？