多表多列聚合汇总查询

Question

group by convert(jx_sj,varchar(10),120) 这样

Answer 1

select year(shijian),sum(wages) from wages where u_name='王志辉' group by year(shijian) order by year(shijian)
union all
select year(fl_sj),sum(jine) from fuli where u_name='王志辉' group by year(fl_sj) order by year(fl_sj)
union all
select year(jx_sj),sum(jine) from jixiao where u_name='王志辉' group by year(jx_sj) order by year(jx_sj) 

Answer 2

2楼的代码执行提示以下错误
服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'union' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'union' 附近有语法错误。我用这样的办法，会把重复的记录相加
SELECT wages.u_name,YEAR(wages.shijian) AS wagesyear,  
      SUM(wages.wages) AS wages, SUM(fuli.jine) AS fulijine, SUM(jixiao.jine) 
      AS jixiaojine
FROM wages left JOIN
      jixiao ON wages.u_name = jixiao.u_name AND YEAR(wages.shijian) 
      = YEAR(jixiao.jx_sj) left JOIN
      fuli ON wages.u_name = fuli.u_name AND YEAR(wages.shijian) = YEAR(fuli.fl_sj)
where wages.u_name='王志辉'
GROUP BY wages.u_name, YEAR(wages.shijian)
order by year(wages.shijian)

Answer 3

哦，把前面2个的order by 去掉就行

Answer 4

--just try
select a.time as N'年份',
       a.wages as N'工资总额',
       b.jine as N'福利总额',
       c.jine as N'绩效总额'
  from (select u_name,
               year(shijian) as time,
               sum(wages) as wages 
          from wages 
         where u_name='王志辉' 
      group by year(shijian) 
      order by year(shijian)) a 
    inner join (select u_name,
                       year(fl_sj) as time,
                       sum(jine) as jine
                  from fuli 
                 where u_name='王志辉' 
              group by year(fl_sj) 
              order by year(fl_sj)) b on a.u_name = b.u_name and a.time = b.time
    inner join (select u_name,
                       year(jx_sj) as time,
                       sum(jine) as jine
                  from jixiao 
                 where u_name='王志辉' 
              group by year(jx_sj) 
              order by year(jx_sj)) c on c.u_name = a.u_name and a.time = c.time

Answer 5

试试:
select '年份'=a.shijian,'工资总额'=a.wages,'福利总额'=b.jine,'绩效总额'=c.jine
from
(select year(shijian),username,'wages'=sum(wages) from wages group by year(shijian),username) a 
left join 
(select year(fl_sj),username,'fine'=sum(fine) from fl_sj group by year(fl_sj),username) b
on a.username=b.username
left join
(select year(jx_sj),username,'fine'=sum(fine) from jx_sj group by year(jx_sj),username) c
on a.username=c.username
where a.username='王志辉'

Answer 6

wages表
*********
id  
u_name(人名)
shijian（工资发放时间）
wages（月薪）jixiao表
*********
id
u_name（人名）
xiangmu（具体绩效名称）
jx_sj（绩效发放时间）
jine（金额）
fuli表
*********
id
u_name
xiangmu（具体福利名称）
fl_sj（福利发放时间）
jine（金额）
我是要按年汇总人力资源成本

Answer 7

Sorry
select '年份'=a.shijian,'工资总额'=a.wages,'福利总额'=b.jine,'绩效总额'=c.jine
from
(select year(shijian),u_name,'wages'=sum(wages) from wages group by year(shijian),u_name) a 
left join 
(select year(fl_sj),u_name,'fine'=sum(fine) from fuli group by year(fl_sj),u_name) b
on a.u_name=b.u_name
left join
(select year(jx_sj),u_name,'fine'=sum(fine) from jixiao group by year(jx_sj),u_name) c
on a.u_name=c.u_name
where a.u_name='王志辉'

Answer 8

declare @wages table([id] int identity(1,1),u_name varchar(10),shijian datetime,wages numeric(6,2))
insert @wages(u_name,shijian,wages)
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10--select * from @wages order by u_namedeclare @jixiao table(u_name varchar(10),jx_sj datetime,jine numeric(6,2))
insert @jixiao
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10declare @fuli table(u_name varchar(10),fl_sj datetime,jine numeric(6,2))
insert @fuli
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10select '年份'=a.shijian,a.u_name,'工资总额'=a.wages,'福利总额'=b.jine,'绩效总额'=c.jine
from
     (select 'shijian'=year(shijian),u_name,'wages'=sum(wages) from @wages group by year(shijian),u_name)a 
     left join 
     (select 'fl_sj'=year(fl_sj),u_name,'jine'=sum(jine) from @fuli group by year(fl_sj),u_name)b
     on a.u_name=b.u_name and a.shijian=b.fl_sj
     left join
     (select 'jx_sj'=year(jx_sj),u_name,'jine'=sum(jine) from @jixiao group by year(jx_sj),u_name)c
     on a.u_name=c.u_name and a.shijian=c.jx_sj
--where a.u_name='王志辉'
/*
年份          u_name     工资总额                                     福利总额                                     绩效总额                                     
----------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
2004        王志辉        2000.32                                  2000.32                                  2000.32
2005        王志辉        24.20                                    24.20                                    24.20
2006        王志辉        1222.10                                  1222.10                                  1222.10
2005        张三         12.10                                    12.10                                    12.10
2006        张三         12.10                                    12.10                                    12.10
2007        张三         12.10                                    12.10                                    12.10*/

Answer 9

declare @wages table([id] int identity(1,1),u_name varchar(10),shijian datetime,wages numeric(6,2))
insert @wages(u_name,shijian,wages)
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10--select * from @wages order by u_namedeclare @jixiao table(u_name varchar(10),jx_sj datetime,jine numeric(6,2))
insert @jixiao
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10declare @fuli table(u_name varchar(10),fl_sj datetime,jine numeric(6,2))
insert @fuli
select '王志辉','2004-1-6',2000.32
union all select '王志辉','2005-11-14',12.10
union all select '张三','2005-6-4',12.10
union all select '王志辉','2006-9-4',1222.10
union all select '张三','2007-6-4',12.10
union all select '王志辉','2005-6-4',12.10
union all select '张三','2006-6-24',12.10select '年份'=a.shijian,a.u_name,'工资总额'=a.wages,'福利总额'=b.jine,'绩效总额'=c.jine
from
     (select 'shijian'=year(shijian),u_name,'wages'=sum(wages) from @wages group by year(shijian),u_name)a 
     left join 
     (select 'fl_sj'=year(fl_sj),u_name,'jine'=sum(jine) from @fuli group by year(fl_sj),u_name)b
     on a.u_name=b.u_name and a.shijian=b.fl_sj
     left join
     (select 'jx_sj'=year(jx_sj),u_name,'jine'=sum(jine) from @jixiao group by year(jx_sj),u_name)c
     on a.u_name=c.u_name and a.shijian=c.jx_sj
--where a.u_name='王志辉'/*（所影响的行数为 7 行）
（所影响的行数为 7 行）
（所影响的行数为 7 行）年份          u_name     工资总额                                     福利总额                                     绩效总额                                     
----------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
2004        王志辉        2000.32                                  2000.32                                  2000.32
2005        王志辉        24.20                                    24.20                                    24.20
2006        王志辉        1222.10                                  1222.10                                  1222.10
2005        张三         12.10                                    12.10                                    12.10
2006        张三         12.10                                    12.10                                    12.10
2007        张三         12.10                                    12.10                                    12.10（所影响的行数为 6 行）
*/

Answer 10

应该还要加上时间限制，为 year(shijian)等指定一个别名

Answer 11

再改下:
select '年份'=a.shijian,a.u_name,'工资总额'=isnull(a.wages,0),'福利总额'=isnull(b.jine,0),'绩效总额'=isnull(c.jine,0)
from
     (select 'shijian'=year(shijian),u_name,'wages'=sum(wages) from @wages group by year(shijian),u_name)a 
     left join 
     (select 'fl_sj'=year(fl_sj),u_name,'jine'=sum(jine) from @fuli group by year(fl_sj),u_name)b
     on a.u_name=b.u_name and a.shijian=b.fl_sj
     left join
     (select 'jx_sj'=year(jx_sj),u_name,'jine'=sum(jine) from @jixiao group by year(jx_sj),u_name)c
     on a.u_name=c.u_name and a.shijian=c.jx_sj
--where a.u_name='王志辉'