create table te(PLACE varchar(20),AGE int,JOB varchar(20),YE int,MON int) insert into te values ('济南',21,'信息',4,2500)
insert into te values ('大连',22,'物业',2,3600)
insert into te values ('北京',23,'信息',3,3700)
insert into te values ('济南',24,'财务',4,4800)
insert into te values ('北京',32,'物业',5,2500)
insert into te values ('上海',30,'信息',6,2600)
insert into te values ('北京',33,'财务',2,3800)
insert into te values ('济南',34,'财务',3,3700)
insert into te values ('济南',21,'业务',4,2500)
insert into te values ('烟台',22,'业务',4,3600)
insert into te values ('上海',23,'财务',5,4700)
insert into te values ('济南',24,'物业',4,2800)
insert into te values ('大连',33,'销售',3,3500)
insert into te values ('济南',34,'销售',2,4600)
insert into te values ('烟台',31,'财务',2,2800)
insert into te values ('济南',30,'销售',3,2700)
insert into te values ('北京',30,'销售',4,3700) 想用SQL语句实现以下内容,高手们帮忙我。谢谢了
insert into te values ('大连',22,'物业',2,3600)
insert into te values ('北京',23,'信息',3,3700)
insert into te values ('济南',24,'财务',4,4800)
insert into te values ('北京',32,'物业',5,2500)
insert into te values ('上海',30,'信息',6,2600)
insert into te values ('北京',33,'财务',2,3800)
insert into te values ('济南',34,'财务',3,3700)
insert into te values ('济南',21,'业务',4,2500)
insert into te values ('烟台',22,'业务',4,3600)
insert into te values ('上海',23,'财务',5,4700)
insert into te values ('济南',24,'物业',4,2800)
insert into te values ('大连',33,'销售',3,3500)
insert into te values ('济南',34,'销售',2,4600)
insert into te values ('烟台',31,'财务',2,2800)
insert into te values ('济南',30,'销售',3,2700)
insert into te values ('北京',30,'销售',4,3700) 想用SQL语句实现以下内容,高手们帮忙我。谢谢了
insert into te values ('大连',22,'物业',2,3600)
insert into te values ('北京',23,'信息',3,3700)
insert into te values ('济南',24,'财务',4,4800)
insert into te values ('北京',32,'物业',5,2500)
insert into te values ('上海',30,'信息',6,2600)
insert into te values ('北京',33,'财务',2,3800)
insert into te values ('济南',34,'财务',3,3700)
insert into te values ('济南',21,'业务',4,2500)
insert into te values ('烟台',22,'业务',4,3600)
insert into te values ('上海',23,'财务',5,4700)
insert into te values ('济南',24,'物业',4,2800)
insert into te values ('大连',33,'销售',3,3500)
insert into te values ('济南',34,'销售',2,4600)
insert into te values ('烟台',31,'财务',2,2800)
insert into te values ('济南',30,'销售',3,2700)
insert into te values ('北京',30,'销售',4,3700)
SELECT *
FROM (
SELECT PLACE,CAST(AGE AS VARCHAR(50)) AS AGE,JOB,CAST(YE AS VARCHAR(50)) AS YE,MON
FROM te
UNION ALL
SELECT PLACE,'','','',SUM(MON) AS MON
FROM TE
GROUP BY PLACE
) T
ORDER BY PLACE,CASE WHEN AGE='' AND JOB='' AND YE='' THEN 1 ELSE 0 END
/*
北京 23 信息 3 3700
北京 33 财务 2 3800
北京 32 物业 5 2500
北京 30 销售 4 3700
北京 13700
大连 22 物业 2 3600
大连 33 销售 3 3500
大连 7100
济南 24 物业 4 2800
济南 21 信息 4 2500
济南 24 财务 4 4800
济南 34 财务 3 3700
济南 21 业务 4 2500
济南 34 销售 2 4600
济南 30 销售 3 2700
济南 23600
上海 23 财务 5 4700
上海 30 信息 6 2600
上海 7300
烟台 22 业务 4 3600
烟台 31 财务 2 2800
烟台 6400
*/
FROM (
SELECT 1 AS NUM,PLACE,CAST(AGE AS VARCHAR(50)) AS AGE,JOB,CAST(YE AS VARCHAR(50)) AS YE,MON
FROM te
UNION ALL
SELECT 2 AS NUM,PLACE,'','','',SUM(MON) AS MON
FROM TE
GROUP BY PLACE
UNION ALL
SELECT 3 AS NUM,'合计','','','',SUM(MON) AS MON
FROM TE
) T
ORDER BY
CASE WHEN PLACE='合计' THEN 1 ELSE 0 END,
PLACE,NUM,CASE WHEN AGE='' AND JOB='' AND YE='' THEN 1 ELSE 0 END
/*
北京 23 信息 3 3700
北京 33 财务 2 3800
北京 32 物业 5 2500
北京 30 销售 4 3700
小计 13700
大连 22 物业 2 3600
大连 33 销售 3 3500
小计 7100
济南 24 物业 4 2800
济南 21 信息 4 2500
济南 24 财务 4 4800
济南 34 财务 3 3700
济南 21 业务 4 2500
济南 34 销售 2 4600
济南 30 销售 3 2700
小计 23600
上海 23 财务 5 4700
上海 30 信息 6 2600
小计 7300
烟台 22 业务 4 3600
烟台 31 财务 2 2800
小计 6400
合计 58100
*/