复杂的SQL查询。任何方法来简化?
问题描述:
我正在查询。我有以下表格:复杂的SQL查询。任何方法来简化?
CREATE TABLE #House
(
HouseName Varchar(1),
Address Varchar(255)
)
CREATE TABLE #Events
(
Id Integer,
HouseName Varchar(1),
EventName Varchar(255),
Date Date,
Family Varchar(255)
)
INSERT #House VALUES('A', '1 Market St')
INSERT #House VALUES('B', '2 Market St')
INSERT #House VALUES('X', '3 Market St')
INSERT #House VALUES('D', '4 Market St')
INSERT #Events VALUES(1,'A','MoveIn','2005-01-31','Smith')
INSERT #Events VALUES(2,'A','Remodel','2005-03-31','Smith')
INSERT #Events VALUES(3,'A','Remodel','2008-03-31','Smith')
INSERT #Events VALUES(4,'A','CleanUp','2008-05-31','Smith')
INSERT #Events VALUES(5,'B','MoveIn','2005-01-31','Newton')
INSERT #Events VALUES(6,'B','MoveOut','2008-05-01','Newton')
INSERT #Events VALUES(7,'B','MoveIn','2008-12-01','Einstein')
INSERT #Events VALUES(8,'D','MoveIn','2007-08-31','Soo')
INSERT #Events VALUES(9,'D','MoveOut','2010-08-13','Soo')
我希望得到以下结果:
HouseName Address MostRecentResident MoveInDate
A 1 Market StSmith 2005-01-31
B 2 Market St Einstein 2008-12-01
D 4 Market St Soo 2007-08-31
注:楼B有两个MoveIn事件。
这是查询。你认为有更简单的方法来获得相同的结果吗?
Select
a.HouseName, a.Address, b.Family as [MostRecentResident],
mid.MoveInDate
from
#House a, #Events b,
--MoveInDate of the last family:
(Select HouseName, Max(MoveInDate) MoveInDate
from
(Select HouseName, Family, MIN(Date) MoveInDate
From #Events
Group by HouseName, Family) ff
group by HouseName) mid,
--Distinct house and the last move in date:
(Select HouseName, MAX(Date) LastMoveInDate
From #Events
Group by HouseName) lmid
where
a.HouseName = b.HouseName
and b.HouseName = lmid.HouseName
and b.Date = lmid.LastMoveInDate
and b.HouseName = mid.HouseName
答
我觉得你只是想一些有条件的聚集联接:
select h.housename, h.address,
max(case when seqnum = 1 and eventname = 'Movein' then family end) as MostRecentResident,
max(case when seqnum = 1 and eventname = 'Movein' then date end) as MoveInDate
from #house h join
(select e.*,
row_number() over (partition by e.housename, e.eventname order by date desc) seqnum
from #event e
) e
on h.housename = e.housename
group by h.housename,h.address;
编辑:
以上可以进一步简化为:
select h.housename, h.address,
max(case when seqnum = 1 then family end) as MostRecentResident,
max(case when seqnum = 1 then date end) as MoveInDate
from #house h join
(select e.*,
row_number() over (partition by e.housename, e.eventname order by date desc) seqnum
from #event e
where eventname = 'Movein'
) e
on h.housename = e.housename
group by h.housename,h.address;
或邮寄至:
select h.housename, h.address,
e.family as MostRecentResident,
e.date as MoveInDate
from #house h outer apply
(select top 1 e.*
from #event e
where e.eventname = 'Movein' and h.housename = e.housename
) e;
这似乎是最简单的方法。
+0
“爱因斯坦”是'B'中的最大移动日期。但是您的查询显示了牛顿。 – Mansoor
答
我认为这是完全不可能像输出
HouseName Address MostRecentResident MoveInDate
A 1 Market StSmith 2005-01-31
B 2 Market St Einstein 2008-12-01
D 4 Market St Soo 2007-08-31
数据集作为MostRecentResident包含StSmith,如果是错字,它应该是史密斯。
那么也许你想
select a.HouseName,a.Address,b.Family as MostRecentResident,b.Date as MoveInDate from #House a
CROSS APPLY (SELECT TOP(1) Family,Date FROM #Events WHERE EventName='MoveIn' and HouseName=a.HouseName order by Date desc) as b
应用交叉应用可以简化这个查询。
+0
是的,这是一个错字。并且非常感谢你! –
答
SELECT * FROM (
SELECT h.*,e.Family AS MostRecentResident,e.Date AS MoveInDate,ROW_NUMBER()OVER(PARTITION BY h.HouseName ORDER BY e.Date desc) AS rn
FROM #House AS h INNER JOIN #Events AS e ON h.HouseName=e.HouseName
WHERE e.EventName='MoveIn'
) AS t WHERE t.rn=1
HouseName Address MostRecentResident MoveInDate rn --------- -------------------- -------------------- ---------- ---------- A 1 Market St Smith 2005-01-31 1 B 2 Market St Einstein 2008-12-01 1 D 4 Market St Soo 2007-08-31 1
提示:使用别名,帮助,例如'从#House作为H,#Events作为E'。而更现代的'JOIN'语法将是一个好主意,例如'从#House作为H内部连接#Events作为E在...'。 – HABO
[踢坏的习惯:使用旧式JOIN](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins。 aspx) - 在ANSI - ** 92 ** SQL标准(**超过20年前的**)中,旧式*逗号分隔的表*样式列表被替换为* proper * ANSI'JOIN'语法并且不鼓励使用它 –