这样的主从表查询语句如何编写？
现有两个主从明细表，表结构如下：
table1：
MasterID Name
1 aaa
2 bbb
3 ccc
-------------------------------------------
table2：
IDMasterID StateCreatedDate
12开始2007-6-6
22执行中2007-6-8
32完成2007-6-10
43开始2007-6-7
53执行中2007-6-9
--------------------------------------------
现在想得到如下的查询结果：
MasterID NameLaststate LastDate
1aaa未开始
2bbb完成2007-6-10
3ccc执行中2007-6-9
---------------------------------------------
也就是说如果从表（table2）中，没有从记录,则Laststate字段就显示“未开始”，LastDate留空。如果有从记录的，就只把最后时间的记录与主表（table1）合并。
这样的查询语句如何编写？谢谢！
[解决办法]
select
a.MasterID,
a.Name,
isnull(b.State, '未开始 ') as LastState,
b.CreatedDate as LastDate
from
table1 a
left join
table2 b
on
a.MasterID=b.MasterID
and
not exists(select 1 from table2 where MasterID=b.MasterID and CreatedDate> b.CreatedDate)
[解决办法]
Select a.MasterID,a.Name,IsNULL(b.State, '为开始 '),b.CreatedDate as LastDate
from table1 as a Left Join table2 as b on a.MasterID=b.MasterID
and not exists(Select * from table2
where MasterID=b.MasterID and CreatedDate> b.CreatedDate)
[解决办法]
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Inner Join (Select MasterID, Max(CreatedDate) As CreatedDate From table2 Group By MasterID) C On B.MasterID = C.MasterID And B.CreatedDate = C.CreatedDate) D
On A.MasterID = D.MasterID
[解决办法]
--方法一
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Inner Join (Select MasterID, Max(CreatedDate) As CreatedDate From table2 Group By MasterID) C On B.MasterID = C.MasterID And B.CreatedDate = C.CreatedDate) D
On A.MasterID = D.MasterID
--方法二
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Where Not Exists(Select MasterID From table2 Where MasterID = B.MasterID And CreatedDate > B.CreatedDate)) D
On A.MasterID = D.MasterID
--方法三
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Where CreatedDate = (Select Max(CreatedDate) From table2 Where MasterID = B.MasterID)) D
On A.MasterID = D.MasterID
[解决办法]
select table1.MasterID ,Name,(case when table2.State is null then N '未开始 ' else table2.State end ) as Laststate ,
a.CreatedDate as LastDate
from table1
left outer join (
select MasterID ,max(CreatedDate) as CreatedDate
from table2
group by MasterID) a
on table1.MasterID =a.MasterID inner join table2
on a.MasterID=table2.MasterID and a.CreatedDate=table2.CreatedDate
[解决办法]
SELECT table1.MasterID, Name, (CASE WHEN table2.State IS NULL
THEN N '未开始 ' ELSE table2.State END) AS Laststate,
a.CreatedDate AS LastDate
FROM table1 LEFT OUTER JOIN
(SELECT MasterID, MAX(CreatedDate) AS CreatedDate
FROM table2
GROUP BY MasterID) a ON table1.MasterID = a.MasterID LEFT OUTER JOIN
table2 ON a.MasterID = table2.MasterID AND a.CreatedDate = table2.CreatedDate
MasterID Name Laststate LastDate
----------- -------------------- -------------------- ------------------------------------------------------
1 aaa 未开始 NULL
2 bbb 完成 2007-06-10 00:00:00.000
3 ccc 执行中 2007-06-09 00:00:00.000
(3 row(s) affected)
[解决办法]
create table hiyun1
(
MasterID int,
Namevarchar(10)
)
create table hiyun2
(
IDint,
MasterID int,
Statevarchar(10),
CreatedDate datetime
)
insert into hiyun1
select 1, 'aaa ' union all select
2, 'bbb ' union all select
3, 'ccc '
insert into hiyun2
select
1,2, '开始 ', '2007-6-6 ' union all select
2,2, '执行中 ', '2007-6-8 ' union all select
3,2, '完成 ', '2007-6-10 ' union all select
4,3, '开始 ', '2007-6-7 ' union all select
5,3, '执行中 ', '2007-6-9 '
select A.MasterID ,A.Name, isnull(B.State, '未开始 ') as Laststate,
--(case when B.State= 'null ' then '未开始 ' else B.State end ) ,
B.CreatedDate as LastDate
from
hiyun1 A left join (select * from hiyun2 A where not exists(
select CreatedDate from hiyun2 B where B.MasterID =A.MasterID and A.CreatedDate <B.CreatedDate)) B
on A.MasterID = B.MasterID
===============================
MasterID NameLaststate LastDate
1aaa未开始
2bbb完成2007-6-10
3ccc执行中2007-6-9
[解决办法]
select
A.MasterID ,
A.Name,
isnull(B.State, '未开始 ') as Laststate,
B.CreatedDate as LastDate
from
hiyun1 A
left join
(select * from hiyun2 A where
not exists(select CreatedDate from hiyun2 B
where B.MasterID =A.MasterID and A.CreatedDate <B.CreatedDate))
B
on A.MasterID = B.MasterID
