>我有以下 2 个表,我需要获取输出临时表
WorkItem
ItemID ItemName Status
1 xyz 3
2 abc 3
3 MNO 2
WorkItemTrack
TrackID ItemID Status
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1
7 3 2
这是我的查询
select ItemName, WorkItem.Status from WorkItem Inner join WorkItemTrack ON WorkItem.ItemID=WorkItemTrack.ItemID
where WorkItemTrack.Status=2
这是我的输出
ItemName Status
XYZ 3
ABC 3
MNO 2
但我只需要状态为 2 的 MNO 的输出
即
ItemName Status
MNO 2
如何在不使用临时表
这
同样有效
Select x.ItemName,x.Status
From (Select * from WorkItem where Status = 2) x
Join (Select ItemID from WorkItemTrack where Status = 2) y
on x.ItemID=y.ItemID
你可以做
select ItemName, WorkItem.Status from WorkItem
Inner join WorkItemTrack ON WorkItem.ItemID=WorkItemTrack.ItemID
where WorkItemTrack.Status=2 and WorkItem.Status=2
或
select * from
(select ItemName, WorkItem.Status as status from WorkItem
Inner join WorkItemTrack ON WorkItem.ItemID=WorkItemTrack.ItemID
where WorkItemTrack.Status=2)
where status=2
(如果你以后需要变得更复杂,并且想用初步结果做其他事情)
select i.ItemName, i.Status
from WorkItem i
inner join WorkItemTrack t ON i.ItemID = t.ItemID
where t.Status = 2
AND i.Status = 2
你的问题模棱两可,但这可能是你想要的:
SELECT ItemName, wi.Status
FROM WorkItem AS wi
JOIN WorkItemTrack AS wit ON (wi.ItemID=wit.ItemID) AND (wi.Status=wit.Status)
WHERE wit.Status=2
或者也许
SELECT ItemName, wi.Status
FROM WorkItem AS wi
JOIN WorkItemTrack AS wit ON (wi.ItemID=wit.ItemID) AND (wit.Status=2)
WHERE wi.Status=2
再使用一个join
选项
select a.ItemName, a.Status from WorkItem a Inner join worktrack b ON a.ItemID=b.ItemID and a.status=b.status where b.Status=2
或
其他不同
select distinct ItemName, WorkItem.Status from WorkItem inner join worktrack ON WorkItem.ItemID=worktrack.ItemID where workitem.Status=2
或
否则行数
select top 1 * from (select row_number() over (partition by workitem.status order by workitem.status ) as rownum ,
ItemName, WorkItem.Status from WorkItem inner join worktrack ON WorkItem.ItemID=worktrack.ItemID
where worktrack.status=2) a
where a.rownum=1