SQL查询在SQL Server 2008中
问题描述:
下面我有2个表,我需要得到不用其他的临时表输出SQL查询在SQL Server 2008中
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 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
(如果你需要后来变得更加复杂,并希望做其他事情的初步结果)
+0
是的(没有足够的咖啡在身体..将编辑) – BugFinder 2012-08-17 07:15:39
+0
谢谢。第二个查询工作 – Somashekhar 2012-08-17 07:18:24
答
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
答
这将同样的工作
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
答
使用一个更多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
或
别的行NUM
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
的'Status'的'WorkItem'和'WorkItemTrack'两混淆 - 你想过滤哪一个?他们是否也应该加入? – Lucero 2012-08-17 07:08:27