在阅读Stackoverflow很长一段时间之后,今天轮到我提问了!
表结构 它涉及对SQL Server 2005数据库的查询。 我有以下表格结构:
组件
ID下载
ID componentid(组件编号为FK) downloaddate进口
ID downloadid(FK下载#编号) 状态我想要得到什么 我正在尝试获取每个组件的最新下载状态。
我到目前为止 我已经开始编写这个查询,但是我的连接工作不太好:
SELECT c.id FROM components as c JOIN ( SELECT componentid, MAX(downloaddate) as downloaddate, id FROM downloads GROUP BY componentid, id ) d ON d.componentid = c.id -- JOIN on imports here...我的问题是用于连接的查询不正确。 因为我需要为连接选择ID,所以我将它添加到group by子句中,现在我为每个componentid获取多个记录,而不是最新的一个。
这是我得到的,如果我自己在联接中执行查询。
componentid downloaddate id 8882 2011-01-15 06:00:09.773 595862 8882 2011-01-16 06:00:09.847 598422 8882 2011-01-17 06:00:09.940 600971 8951 2010-11-15 03:00:22.550 450221 8951 2010-11-16 03:00:21.730 452431 8951 2010-11-17 03:00:21.920 454668 8961 2011-01-17 02:00:33.077 600532 8961 2011-01-17 02:00:59.070 600536 8983 2008-07-17 11:59:53.780 122 9005 2009-09-01 04:00:38.320 48918 9005 2009-09-02 04:00:36.260 49280相反,我需要这样的东西:
componentid downloaddate id 8882 2011-01-17 06:00:09.940 600971 8951 2010-11-17 03:00:21.920 454668 8961 2011-01-17 02:00:59.070 600536 8983 2008-07-17 11:59:53.780 122 9005 2009-09-02 04:00:36.260 49280有谁知道我应该如何编写我的连接以实现预期的结果?
提前致谢
After reading Stackoverflow for quite some time, today it's my turn to ask a question!
Table structure It's related to a query to a SQL server 2005 database. I have the following table structure:
components
iddownloads
id componentid (FK to components#id) downloaddateimports
id downloadid (FK to downloads#id) statusWhat I want to get I am trying to get the status of the latest download for each component.
Where I am so far I have started writing this query, but my join is not working quite well:
SELECT c.id FROM components as c JOIN ( SELECT componentid, MAX(downloaddate) as downloaddate, id FROM downloads GROUP BY componentid, id ) d ON d.componentid = c.id -- JOIN on imports here...My problem is that the query used for the join is not correct. Because I need to select the id for the join, I added it in the group by clause and I now get multiple records for each componentid, instead of the latest one.
Here is what I get if I execute the query in the join on its own.
componentid downloaddate id 8882 2011-01-15 06:00:09.773 595862 8882 2011-01-16 06:00:09.847 598422 8882 2011-01-17 06:00:09.940 600971 8951 2010-11-15 03:00:22.550 450221 8951 2010-11-16 03:00:21.730 452431 8951 2010-11-17 03:00:21.920 454668 8961 2011-01-17 02:00:33.077 600532 8961 2011-01-17 02:00:59.070 600536 8983 2008-07-17 11:59:53.780 122 9005 2009-09-01 04:00:38.320 48918 9005 2009-09-02 04:00:36.260 49280Instead, I need something like this:
componentid downloaddate id 8882 2011-01-17 06:00:09.940 600971 8951 2010-11-17 03:00:21.920 454668 8961 2011-01-17 02:00:59.070 600536 8983 2008-07-17 11:59:53.780 122 9005 2009-09-02 04:00:36.260 49280Does anyone know how I should write my join to achieve the expected result?
Thanks a lot in advance
最满意答案
您无法关联JOIN运算符。 改用APPLY:
SELECT c.id , d.downloaddate FROM components as c APPLY ( SELECT componentid, MAX(downloaddate) as downloaddate FROM downloads WHERE componentid = c.id ) as d;其实你也可以使用一个JOIN(因为内部查询实际上没有关联),但不要按ID进行分组,只是按组件进行分组:
SELECT c.id , d.downloaddate FROM components as c JOIN ( SELECT componentid, MAX(downloaddate) as downloaddate FROM downloads GROUP BY componentid ) as d ON c.id = d.componentid;更新 :
SELECT * FROM components as c APPLY ( SELECT TOP(1) componentid, downloaddate, id as downloadid FROM downloads WHERE componentid = c.id ORDER BY downloaddate DESC ) as d JOIN imports i on i.id = d.downloadid;You cannot correlate the JOIN operator. Use APPLY instead:
SELECT c.id , d.downloaddate FROM components as c APPLY ( SELECT componentid, MAX(downloaddate) as downloaddate FROM downloads WHERE componentid = c.id ) as d;Actually you could use a JOIN too (since the inner query is no correlated actually), but don't group by id, just group by componentid:
SELECT c.id , d.downloaddate FROM components as c JOIN ( SELECT componentid, MAX(downloaddate) as downloaddate FROM downloads GROUP BY componentid ) as d ON c.id = d.componentid;Updated:
SELECT * FROM components as c APPLY ( SELECT TOP(1) componentid, downloaddate, id as downloadid FROM downloads WHERE componentid = c.id ORDER BY downloaddate DESC ) as d JOIN imports i on i.id = d.downloadid;更多推荐
发布评论