求1条SQL 关于GROUP BY 高手进

Question

有一张表
机构 性别 年龄
办公室 男 60
办公室 女 60
办公室 女 60
办公室 男 60
财务部 男 60
财务部 女 60
财务部 女 60
财务部 男 60
正常情况下
SELECT 机构,性别,AVG(年龄) FROM 表 GROUP BY 机构,性别
机构 性别 年龄
办公室 男 60
办公室 女 60
财务部 男 60
财务部 女 60想要的结果是在每一个 机构下面 汇总一下平均值得出结果机构 性别 年龄
办公室 男 60
办公室 女 60
办公室 60
财务部 男 60
财务部 女 60
财务部 60
总平均 60
简单说 对第1层(共2层)的GROUP的 第一层进行分类汇总with cube 和 with rollup 好象都不行

Answer 1

create table 表(机构 nvarchar(10),性别 nvarchar(2),年龄 int)
insert into 表 select '办公室','男',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','男',60 
insert into 表 select '财务部','男',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','男',60 
SELECT isnull(机构,'汇总'),isnull(性别,''),AVG(年龄) FROM 表 GROUP BY 机构,性别 with rollup
go
drop table 表
/*
---------- ---- -----------
办公室        男    60
办公室        女    60
办公室             60
财务部        男    60
财务部        女    60
财务部             60
汇总              60
*/

Answer 2

DECLARE @T TABLE (机构 VARCHAR(10),性别 VARCHAR(10), 年龄 INT)
INSERT INTO @T
SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'财务部', '男', 60 UNION ALL SELECT 
'财务部', '女', 40 UNION ALL SELECT 
'财务部', '女', 60 UNION ALL SELECT 
'财务部', '男', 60 SELECT 机构=ISNULL(机构,'汇总') ,
性别=ISNULL(性别,''),
年龄=AVG(年龄) 
FROM @T
GROUP BY ROLLUP(机构,性别)
/*
办公室 男 60
办公室 女 60
办公室 60
财务部 男 60
财务部 女 50
财务部 55
汇总 57
*/

Answer 3

create table tt 
(office  varchar(20),
 sex   varchar(2),
 age   int)insert into tt
select '办公室', '男', 60  union all
select '办公室', '女', 55  union all
select '办公室', '男', 64  union all
select '办公室', '女', 46  union all
select '财务部', '男', 55  union all
select '财务部', '女', 62  union all
select '财务部', '女', 53  union all
select '财务部', '男', 48  union all
select '办公室', '男', 44 
select *
from (
select distinct office,sex,avg(age) over(partition by office,sex) as avge 
from tt
union
select distinct office,'',avg(age) over(partition by office)
from tt
union 
select '合计','',avg(age) from tt)a
order by office,sex desc
--------------------------------
办公室 女 50
办公室 男 56
办公室 53
财务部 女 57
财务部 男 51
财务部 54
合计 54

Answer 4

create table 表(机构 nvarchar(10),性别 nvarchar(2),年龄 int)
insert into 表 select '办公室','男',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','男',60 
insert into 表 select '财务部','男',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','男',60 
SELECT ISNULL(机构,'汇总') '机构',ISNULL(性别,'') '性别',AVG(年龄) '年龄' 
FROM 表
GROUP BY 机构,性别 with ROLLUP机构         性别   年龄
---------- ---- -----------
办公室        男    60
办公室        女    60
办公室             60
财务部        男    60
财务部        女    60
财务部             60
汇总              60(7 行受影响)

Answer 5

select *
from
(
select *
from tb
union all
select 机构,'' as 性别,avg(年龄) as 年龄
from tb
group by 机构
)T
order by 机构

Answer 6

DECLARE @T TABLE (机构 VARCHAR(10),性别 VARCHAR(10), 年龄 INT)
INSERT INTO @T
SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'财务部', '男', 60 UNION ALL SELECT 
'财务部', '女', 60 UNION ALL SELECT 
'财务部', '女', 60 UNION ALL SELECT 
'财务部', '男', 60 --方法一
SELECT * 
FROM
(
SELECT 机构, 性别,avg(年龄) AS 年龄
FROM @T GROUP BY 机构,性别
UNION ALL 
SELECT 机构,'', avg(年龄) AS 年龄
FROM @T GROUP BY 机构
UNION ALL
SELECT '合计','',avg(年龄) AS 年龄
FROM @T 
) LO 
ORDER BY LO.机构,LO.性别  desc
 
--方法二
SELECT isnull(机构,'合计'),isnull(性别,''),AVG(年龄) FROM @T GROUP BY 机构,性别 with rollup

Answer 7

不太清楚， 不过可以去工程师之家去问一下就知道了  http://bbs.baovip.com

Answer 8

 --笨方法,但保证有效
DECLARE @T TABLE (机构 VARCHAR(10),性别 VARCHAR(10), 年龄 INT)
INSERT INTO @T
SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '女', 60 UNION ALL SELECT 
'办公室', '男', 60 UNION ALL SELECT 
'财务部', '男', 60 UNION ALL SELECT 
'财务部', '女', 40 UNION ALL SELECT 
'财务部', '女', 60 UNION ALL SELECT 
'财务部', '男', 60 
select 机构,性别,年龄 
from
(select 0 no, 0 no1,机构,性别,年龄 from @t
union select 0 ,1,机构,'平均' ,avg(年龄) from @t group by 机构
union select 1,0,'总平均','',avg(年龄) from @t
) a
order by no,机构,no1

Answer 9

declare @t table (机构 varchar(10),性别 varchar(10), 年龄 int)
insert into @t
select 
'办公室', '男', 60 union all select 
'办公室', '女', 60 union all select 
'办公室', '女', 60 union all select 
'办公室', '男', 60 union all select 
'财务部', '男', 60 union all select 
'财务部', '女', 60 union all select 
'财务部', '女', 60 union all select 
'财务部', '男', 60 select isnull(机构,'汇总') 机构,isnull(性别,ltrim(avg(年龄))) 性别,case when 性别 is null then '' else ltrim(avg(年龄)) end 年龄 
from @t
group by 机构,性别 
with rollup
/*
机构         性别         年龄
---------- ---------- ------------
办公室        男          60
办公室        女          60
办公室        60         
财务部        男          60
财务部        女          60
财务部        60         
汇总         60         (7 行受影响)
*/

Answer 10

如果是 3个字段 只要第1个字段 ROLLUP呢

Answer 11

create table 表(机构 nvarchar(10),性别 nvarchar(2),年龄 int)
insert into 表 select '办公室','男',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','女',60 
insert into 表 select '办公室','男',60 
insert into 表 select '财务部','男',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','女',60 
insert into 表 select '财务部','男',60 
SELECT CASE WHEN (GROUPING(机构) = 1) THEN '汇总'
            ELSE ISNULL(机构, 'UNKNOWN')
       END AS 机构,
       CASE WHEN (GROUPING(性别) = 1) THEN ''
            ELSE ISNULL(性别, 'UNKNOWN')
       END AS 性别,
       SUM(年龄) AS 年龄
FROM 表
GROUP BY 机构,性别 WITH ROLLUP/*
机构         性别   年龄
----------　　 ---- -----------
办公室        男    120
办公室        女    120
办公室　　　　　　　240
财务部        男    120
财务部        女    120
财务部            　240
汇总             　 480(7 行受影响)*/