SQL服务器

SQL服务器 - 获取最新的连接记录(SQL server - Get newest record of join)

在阅读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

id

downloads

id componentid (FK to components#id) downloaddate

imports

id downloadid (FK to downloads#id) status

What 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 49280

Instead, 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 49280

Does 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;