sql查询语句的编写

Question

2张表，为父子关系表： 1 emp表  [员工]   id        Name        Age   1        张三            31   2        李四            28   3        王五            40 --------------------------------- 2 wages表  [工资] Emp_id            wageName(工资项)        wage(工资金额 )        1                      基本工资                        1.00                1                      补助                                2.00                1                      津贴                              3.00                2                      基本工资                        4.00                2                      补助                                5.00                2                      津贴                                6.00                3                      基本工资                          7.00                3                      补助                                8.00                3                      津贴                9.00                ------------- 要求查询得到:   id        Name      Age    基本工资        补助    津贴      合计   1        张三          31              1              2            3            6   2        李四          28              4              5            6            15   3        王五          40              7              8            9            24 我这样实现： select emp.*, 
  '基本工资'=(select wage from wages where wages.emp_id=emp.id and ages.wageName='基本工资'), 
  '补助'=(select wage from wages where wages.emp_id=emp.id and wages.wageName='补助'), 
  '津贴'=(select wage from wages where wages.emp_id=emp.id and wages.wageName='津贴'), 
  '合计'=(select sum(wage) from wages where wages.emp_id=emp.id) 
from emp left join wages on(emp.id=wages.emp_id) 
group by id,Name,Age能得到结果，但总感觉不太理想,本人数据库soso,请大家赐教，给出更清晰的实现~ 

Answer 1

SELECT ..
MAX(CASE WHEN wageName='基本工资' THEN WAGE ELSE 0 END )AS '基本工资'
......
FROM TB JOIN ...

Answer 2

select e.*,w.[基本工资],w.[补助],w.[津贴],[合计]=w.[基本工资]+w.[补助]+w.[津贴] from
(select emp_id,[基本工资],[补助],[津贴] from wages pivot (max(wage) for wageName in ([基本工资],[补助],[津贴])) pvt) w,emp e
where w.emp_id=e.id

Answer 3

create table emp(id int ,[Name] nvarchar(10), Age int)
go
insert into emp select
  1,        '张三',31 union all select 
  2,       '李四',28 union all select 
  3,        '王五',40 create table wages(Emp_id int,wageName nvarchar(10),wage decimal(18,2))
go
insert into wages select 
1,'基本工资',1.00 union all select                
1,'补助',2.00     union all select               
1,'津贴',3.00     union all select              
2,'基本工资',4.00 union all select                             
2,'补助',5.00 union all select            
2,'津贴',6.00 union all select               
3,'基本工资',7.00   union all select             
3,'补助',8.00 union all select               
3,'津贴',9.00   select ID,[Name],age,[基本工资],[补助],[津贴],合计=([基本工资]+[补助]+[津贴])  from 
(
select * from emp
inner join wages on emp.id=wages.emp_id
)  A pivot (max(wage) for wageName in ([基本工资],[补助],[津贴])) B/*ID          Name       age         基本工资                                    补助                                      津贴                                      合计
----------- ---------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           张三         31          1.00                                    2.00                                    3.00                                    6.00
2           李四         28          4.00                                    5.00                                    6.00                                    15.00
3           王五         40          7.00                                    8.00                                    9.00                                    24.00(3 行受影响)*/

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-10 21:19:43
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[emp]
if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([id] int,[Name] varchar(4),[Age] int)
insert [emp]
select 1,'张三',31 union all
select 2,'李四',28 union all
select 3,'王五',40
--> 测试数据：[wages]
if object_id('[wages]') is not null drop table [wages]
go 
create table [wages]([Emp_id] int,[wageName] varchar(8),[wage] numeric(3,2))
insert [wages]
select 1,'基本工资',1.00 union all
select 1,'补助',2.00 union all
select 1,'津贴',3.00 union all
select 2,'基本工资',4.00 union all
select 2,'补助',5.00 union all
select 2,'津贴',6.00 union all
select 3,'基本工资',7.00 union all
select 3,'补助',8.00 union all
select 3,'津贴',9.00
--------------开始查询--------------------------
select
  a.id,a.Name,a.Age,
  max(case b.wageName when'基本工资' then b.wage else 0 end )as 基本工资,
  max(case b.wageName when'补助' then b.wage else 0 end )as 补助,
  max(case b.wageName when'津贴' then b.wage else 0 end )as 津贴, 
  sum(wage) as wage
from
   [emp] a
join
   [wages] b
on
   a.id=b.Emp_id
group by
   a.id,a.Name,a.Age
----------------结果----------------------------
/*id          Name Age         基本工资                                    补助                                      津贴                                      wage
----------- ---- ----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           张三   31          1.00                                    2.00                                    3.00                                    6.00
2           李四   28          4.00                                    5.00                                    6.00                                    15.00
3           王五   40          7.00                                    8.00                                    9.00                                    24.00(3 行受影响)*/

Answer 5

trycreate table emp
(
id int primary key,
[name] varchar(16),
age int
)
gocreate table wages
(
emp_id int,
wageName varchar(32),
wage decimal(18,2)
)
goinsert into emp
select 1, '张三',31 union all select 2, '李四',28 union all select 3, '王五',40
goinsert into wages
select 1, '基本工资',1.00 union all                select 1,'补助',2.00 union all                select 1,'津贴',3.00 union all                select 2,'基本工资',4.00 union all                select 2,'补助',5.00 union all                select 2,'津贴',6.00 union all                select 3,'基本工资',7.00 union all                select 3,'补助',8.00 union all                select 3,'津贴',9.00                
select e.*,基本工资=max(case when wn.wageName='基本工资' then wage else null end),补助=max(case when wn.wageName='补助' then wage else null end),津贴=max(case when wn.wageName='津贴' then wage else null end),合计=max(case when wn.wageName='基本工资' then wage else null end)+max(case when wn.wageName='补助' then wage else null end)+max(case when wn.wageName='津贴' then wage else null end) from emp e inner join wages wn on e.id=wn.emp_id group by e.id,e.[name],e.[age]

Answer 6

if(object_id('emp') > 0)
   drop table emp
go
create table emp(
  id int,
  Name varchar(100),
  Age int
)
insert into emp
select 1,'张三',31 union all select 2,'李四',28 union all select 3,'王五',40
if(object_id('wages') > 0)
   drop table wages
go
create table wages(
   Emp_id int,wageName varchar(100), wage decimal(18,2)
)
insert into wages 
select 1,'基本工资',1.00
union all 
select 1,'补助',2.00
union all 
select 1,'津贴',3.00
union all 
select 2,'基本工资',4.00
union all 
select 2,'补助',5.00
union all 
select 2,'津贴',6.00
union all 
select 3,'基本工资',7.00
union all 
select 3,'补助',8.00
union all 
select 3,'津贴',9.00select * from emp a inner join wages b on a.id = b.Emp_id
go
select a.id,a.Name,a.Age,
 基本工资 = sum(case when b.wageName = '基本工资'   then wage else 0 end),
 补助 = sum(case when b.wageName = '补助'   then wage else 0 end),
 津贴 = sum(case when b.wageName = '津贴'   then wage else 0 end),
 合计 = sum(wage)
 from emp a inner join wages b on a.id = b.Emp_id group by a.id,a.Name,a.Age