SQL问题求解决

[pingo888]

table1
ID ListID ListName
1 TL1 方案1
2 TL2 报告

table2
ID PK_ListID TypeID Date
1 TL1 1 2006-1-1
2 TL1 2 2006-1-2
3 TL1 3 2006-1-3
4 TL2 1 2006-2-1
5 TL2 2 2006-2-2
说明：TypeID为“操作类别”，1为填写，2为审核，3为审批，分别对应操作的日期Date

想得到结果：
ListName 填写日期 审核日期 审批日期
方案1 2006-1-1 2006-1-2 2006-1-3
报告 2006-2-1 2006-2-1

请各位帮助。

[pingo888]

结果错了，对不起，应该是
想得到结果：
ListName 填写日期 审核日期 审批日期
方案1 2006-1-1 2006-1-2 2006-1-3
报告 2006-2-1 2006-2-2

[dulei115]

select a.ListID, min(case b.TypeID when 1 then b.Date end) as 填写日期,
min(case b.TypeID when 2 then b.Date end) as 审核日期,
min(case b.TypeID when 3 then b.Date end) as 审批日期
from talbe1 a join table2 b ona a.ListID = b.PK_ListID
group by a.ListID

[roundman]

select T01.ListName,
T02.date as 填写日期,
T03.date as 审核日期,
T04.date as 审批日期
from table1 T01
left join table2 T02
on T01.ListID = T02.PK_ListID
and T02.TypeID = 1
left join table2 T03
on T01.ListID = T03.PK_ListID
and T03.TypeID = 2
left join T04
on T01.ListID = T04.PK_ListID
and T04.TypeID = 3

[jacobsan]

select min(ListName), min(case b.TypeID when 1 then b.Date end) as 填写日期,min(case b.TypeID when 2 then b.Date end) as 审核日期,min(case b.TypeID when 3 then b.Date end) as 审批日期 from talbe1 a,table2 b where a.ListID = b.PK_ListID group by a.ListID

[marco08]

create table A(ID int, ListID nvarchar(10), ListName nvarchar(10))
insert A select 1, 'TL1', '方案1'
union all select 2, 'TL2', '报告'

create table B(ID int, PK_ListID nvarchar(10), TypeID int, [Date] datetime)
insert B select 1, 'TL1', 1, '2006-1-1'
union all select 2, 'TL1', 2, '2006-1-2'
union all select 3, 'TL1', 3, '2006-1-3'
union all select 4, 'TL2', 1, '2006-2-1'
union all select 5, 'TL2', 2, '2006-2-2'

select A.ListID, A.ListName,
填写日期=max(case when TypeID=1 then [Date] end),
审核日期=max(case when TypeID=2 then [Date] end),
审批日期=max(case when TypeID=3 then [Date] end)
from B
right join A on A.ListID=B.PK_ListID
group by A.ListID, A.ListName
order by A.ListID

--result
ListID ListName 填写日期 审核日期 审批日期
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
TL1 方案1 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000
TL2 报告 2006-02-01 00:00:00.000 2006-02-02 00:00:00.000 NULL

(2 row(s) affected)

[proglovercn]

declare @t1 table(ID int,ListID char(3),ListName nvarchar(5))
insert into @t1
select 1,'TL1',N'方案1'
union all
select 2,'TL2',N'报告 '
select * from @t1

declare @t2 table(ID int,PK_ListID char(3),TypeID int,Date datetime)
insert into @t2
select 1,'TL1',1,'2006-1-1'
union all
select 2,'TL1',2,'2006-1-2'
union all
select 3,'TL1',3,'2006-1-3'
union all
select 4,'TL2',1,'2006-2-1'
union all
select 5,'TL2',2,'2006-2-2'
select * from @t2

select
T1.ListName As 'ListName'
,(Select Date From @T2 Where PK_listID=T1.ListID And TypeId=1) As N'填写日期'
,(Select Date From @T2 Where PK_listID=T1.ListID And TypeId=2) As N'审核日期'
,(Select Date From @T2 Where PK_listID=T1.ListID And TypeId=3) As N'审批日期'
from
@t1 T1
--原始数据：
表1
ID ListID ListName
-------------------------------
1 TL1 方案1
2 TL2 报告

表2
ID PK_ListID TypeID Date
-----------------------------------------------
1 TL1 1 2006-01-01 00:00:00.000
2 TL1 2 2006-01-02 00:00:00.000
3 TL1 3 2006-01-03 00:00:00.000
4 TL2 1 2006-02-01 00:00:00.000
5 TL2 2 2006-02-02 00:00:00.000
--运行结果：
ListName 填写日期 审核日期 审批日期
---------------------------------------------------
方案1 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000
报告 2006-02-01 00:00:00.000 2006-02-02 00:00:00.000 NULL

[sgucxc0]

select A.ListName,min(case when B.TypeId=1 then [date] end) 填写日期,
min(case when B.TypeId=2 then [date] end) 审核日期,
min(case when B.TypeId=3 then [date] end) 审批日期
from A inner join B on A.ListId=B.PK_ListId group by A.ListName,A.ListID order by A.ListID
/*
ListName 填写日期 审核日期 审批日期
-------- ----------------------- ------------------------ -----------------------
方案1 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000
报告 2006-02-01 00:00:00.000 2006-02-02 00:00:00.000 NULL

（所影响的行数为 2 行）
*/