求教sql 分组问题

life is strange_ 2017-09-28 05:26:40

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应该怎么实现啊 麻烦了

...全文
363 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
二月十六 2017-09-28
  • 打赏
  • 举报
回复
引用 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吗?
life is strange_ 2017-09-28
  • 打赏
  • 举报
回复
引用 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
顺势而为1 2017-09-28
  • 打赏
  • 举报
回复


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_ 2017-09-28
  • 打赏
  • 举报
回复
引用 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 请问还有实现方法吗
中国风 2017-09-28
  • 打赏
  • 举报
回复
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
*/
二月十六 2017-09-28
  • 打赏
  • 举报
回复
用这个:
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
二月十六 2017-09-28
  • 打赏
  • 举报
回复
试试这样:
--测试数据
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


二月十六 2017-09-28
  • 打赏
  • 举报
回复
--测试数据
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


22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧