[Help]有难度的左连接语句写法
A表
工号 姓名
01 小明
02 小红
......
B表
编号 工号 类型 天数 日期
1 01 请假 0.5 2007-6-1
2 01 请假 0.5 2007-6-1
3 01 休息 1 2007-6-1
4 01 出差 1 2007-7-1
5 02 请假 1 2007-6-1
......
请问如何写出以下查询结果：
工号 姓名 日期 请假 休息 出差
01 小明 2007-6-1 1 1 0
01 小明 2007-7-1 0 0 1
02 小红 2007-6-1 1 0 0
02 小红 2007-7-1 0 0 0
......
[解决办法]
select
A.工号,A.姓名,B.日期,
sum(case B.类型 when '请假 ' then B.天数 else 0 end) as 请假,
sum(case B.类型 when '休息 ' then B.天数 else 0 end) as 休息,
sum(case B.类型 when '出差 ' then B.天数 else 0 end) as 出差
from
A
left join
B
on
A.工号=B.工号
group by
A.工号,A.姓名,B.日期
[解决办法]
select 工号,姓名,日期,
sum(case 类型 when 请假 then 1 else 0 end) 请假,
sum(case 类型 when 休息 then 1 else 0 end) 休息,
sum(case 类型 when 出差 then 1 else 0 end) 出差
from A left join B on A.工号 = B.工号
group by A.工号，B.日期
[解决办法]
Select b.工号,a.姓名,b.日期,
请假=(Case when 类型= '请假 ' then 1 else 0 end),
休息=(Case when 类型= '休息 ' then 1 else 0 end),
出差=(Case when 类型= '出差 ' then 1 else 0 end)
from A表 as a Left Join B表 as b on a.工号=b.工号
[解决办法]
select
a.工号,
a.姓名,
b.日期,
请假=sum(case b.类型 when '请假 ' then b.天数 else 0 end),
休息=sum(case b.类型 when '休息 ' then b.天数 else 0 end),
出差=sum(case b.类型 when '出差 ' then b.天数 else 0 end)
from
A表 a left join B表 b
on a.工号=b.工号
group by a.工号,a.姓名,b.日期
[解决办法]
---创建测试环境
create table A(工号 varchar(8),姓名 varchar(8))
insert into A select '01 ', '小明 '
insert into A select '02 ', '小红 '
create table B(编号 int,工号 varchar(8),类型 varchar(8),天数 numeric(3,1),日期 varchar(10))
insert into B select 1, '01 ', '请假 ',0.5, '2007-06-01 '
insert into B select 2, '01 ', '请假 ',0.5, '2007-06-02 ' --时间不同但都属于2007-06
insert into B select 3, '01 ', '休息 ',1 , '2007-06-03 ' --时间不同但都属于2007-06
insert into B select 4, '01 ', '出差 ',1 , '2007-07-01 '
insert into B select 5, '02 ', '请假 ',1 , '2007-06-01 '
go
---查询结果
select
A.工号,A.姓名,Convert(Varchar(7),B.日期,120) As 日期,
sum(case B.类型 when '请假 ' then B.天数 else 0 end) as 请假,
sum(case B.类型 when '休息 ' then B.天数 else 0 end) as 休息,
sum(case B.类型 when '出差 ' then B.天数 else 0 end) as 出差
from
A
left join
B
on
A.工号=B.工号
group by
A.工号,A.姓名,Convert(Varchar(7),B.日期,120)
---删除测试环境
Drop Table A,B
---结果
/*
工号 姓名 日期 请假 休息 出差
-------- -------- ------- ------------ ---------- ---------
01 小明 2007-06 1.0 1.0 .0
01 小明 2007-07 .0 .0 1.0
02 小红 2007-06 1.0 .0 .0
（所影响的行数为 3 行）
*/
[解决办法]
请问如何写出以下查询结果：
工号 姓名 日期 请假 休息 出差
01 小明 2007-6 1 1 0
01 小明 2007-7 0 0 1
02 小红 2007-6 1 0 0
02 小红 2007-7 0 0 0
--------------------------
select
A.工号,A.姓名,B.日期,
sum(case B.类型 when '请假 ' then B.天数 else 0 end) as 请假,
sum(case B.类型 when '休息 ' then B.天数 else 0 end) as 休息,
sum(case B.类型 when '出差 ' then B.天数 else 0 end) as 出差
from
A
left join
(select 编号,工号,类型,天数, 日期=ltrim(year(日期))+ '- '+ltrim(month(日期)) from B ) as B
on
A.工号=B.工号
group by
A.工号,A.姓名,B.日期
go
[解决办法]
create table A(工号 varchar(2),姓名 varchar(10))
insert a
select '01 ', '小明 ' union all
select '02 ', '小红 '
create table B(编号 int,工号 varchar(2),类型 varchar(10),天数 decimal(3,2),日期 datetime)
insert B
select 1, '01 ', '请假 ',0.5, '2007-6-1 ' union all
select 2, '01 ', '请假 ',0.5, '2007-6-1 ' union all
select 3, '01 ', '休息 ',1, '2007-6-1 ' union all
select 4, '01 ', '出差 ',1, '2007-7-1 ' union all
select 5, '02 ', '请假 ',1, '2007-6-1 '
select C.工号,C.姓名,CONVERT(varchar(10),C.日期,121)
,sum(case 类型 when '请假 ' then 天数 else 0 end) AS 请假
,sum(case 类型 when '休息 ' then 天数 else 0 end) AS 休息
,sum(case 类型 when '出差 ' then 天数 else 0 end) AS 出差
FROM (SELECT DISTINCT A.工号,A.姓名,B.日期 FROM A CROSS JOIN
B) C LEFT JOIN B ON B.工号=C.工号 AND CONVERT(varchar(10),C.日期,121)=CONVERT(varchar(10),B.日期,121)
group by C.工号,C.姓名,C.日期
drop table A,B
