左连接只有第二个表中的一行 [英] Left join with only one row from second table
本文介绍了左连接只有第二个表中的一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表,我想从第一个表中选择所有行,从第二个表中选择最新日期的行。例如:
表1
某栏 | |
1 | sfafsda |
2 | trwe |
3 | gfdgsd |
表2
ID | FK_Table1 | 日期 < /td> |
1 | 1 | 22.5.2014 |
2 | 1 | 2015年1月2日 |
3 | 1 | 2015年1月1日 |
4 | 2 | 2015年1月1日 |
5 | 2 | 2013年1月1日 |
6 | 3 | 2010年1月1日 |
我需要的表格:
ID | Table1_ID | Table2_ID | 日期 |
1 | 1 | 2 | 2015年1月2日 |
2 | 2 | 4 | 2015年1月1日 |
3 | 3 | 6 | 1.1.2010 |
我正在尝试这个但该解决方案不适用于我:
SELECT Table1。*,tmp。*
FROM 表1
LEFT JOIN (
SELECT s。* FROM 表2 as s ORDER BY s。日期 DESC
) as tmp ON Table1.ID = tmp.FK_Table1
GROUP BY Table1.ID
因为ORDER BY子句而有错误 -
ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效,除非还指定了TOP,OFFSET或FOR XML。
有没有人有想法解决这个问题(对于MSSQL)?
谢谢!
SELECT t1。*,t2 。*
FROM 表1 AS t1 LEFT JOIN 表2 AS t2 ON t1.ID = t2.FK_Table1
ORDER BY t2。日期 DESC
如果您只想获得最新日期,请尝试:
SELECT t1。*,t2。*
FROM 表1 AS t1 < span class =code-keyword> LEFT JOIN (
SELECT * ,ROW_NUMBER() OVER ( PARTITION BY FK_Table1 ORDER BY [日期] DESC ) AS RowNo
FROM 表2
) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo = 1
经测试:
<温泉n class =code-keyword> SET DATEFORMAT dmy;
DECLARE @ table1 TABLE (ID INT ,SomeColumn VARCHAR ( 30 ))
INSERT INTO @ table1 (ID,SomeColumn)
VALUES ( 1 ,' sfafsda'),
( 2 ,' trwe'),
( 3 ,' gfdgsd')
DECLARE @ table2 TABLE (ID INT ,FK_Table1 INT ,[日期] DATE )
INSERT INTO @ table2 ( ID,FK_Table1,[日期])
VALUES ( 1 , 1 ,' 22 / 05/2014'),
( 2 , 1 ,' 01/02/2015'),
( 3 , 1 ,' 01/01 / 2015'),
( 4 , 2 ,' 01/01/2015'),
( 5 , 2 ,' 01/01/2013'),
( 6 , 3 , ' 01/01/2010')
SELECT t1。*,t2。*
FROM @ table1 AS t1 LEFT JOIN (
SELECT *,ROW_NUMBER() OVER ( PARTITION BY FK_Table1 ORDER BY [ 日期] DESC ) AS RowNo
FROM @ table2
) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo = 1
Hi, I have two tables and I want select all rows from first table and rows from second table with latest date. Example:
Table1
ID | Some column |
1 | sfafsda |
2 | trwe |
3 | gfdgsd |
Table2
ID | FK_Table1 | Date |
1 | 1 | 22.5.2014 |
2 | 1 | 1.2.2015 |
3 | 1 | 1.1.2015 |
4 | 2 | 1.1.2015 |
5 | 2 | 1.1.2013 |
6 | 3 | 1.1.2010 |
Table which I need:
ID | Table1_ID | Table2_ID | Date |
1 | 1 | 2 | 1.2.2015 |
2 | 2 | 4 | 1.1.2015 |
3 | 3 | 6 | 1.1.2010 |
I was trying with this but that solution dont work for me:
SELECT Table1.*, tmp.*
FROM Table1
LEFT JOIN (
SELECT s.* FROM Table2 as s ORDER BY s.Date DESC
) as tmp ON Table1.ID = tmp.FK_Table1
GROUP BY Table1.ID
There is an error because "ORDER BY" clause -
ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Has anyone an idea to solve this problem (for MSSQL)?
Thanks!
解决方案
Try this:
SELECT t1.*, t2.* FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.ID = t2.FK_Table1 ORDER BY t2.Date DESC
In case you want to get only latest date, try this:
SELECT t1.*, t2.* FROM Table1 AS t1 LEFT JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY FK_Table1 ORDER BY [Date] DESC) AS RowNo FROM Table2 ) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo=1
Tested on:
SET DATEFORMAT dmy; DECLARE @table1 TABLE (ID INT, SomeColumn VARCHAR(30)) INSERT INTO @table1 (ID, SomeColumn) VALUES(1, 'sfafsda'), (2, 'trwe'), (3, 'gfdgsd') DECLARE @table2 TABLE(ID INT, FK_Table1 INT, [Date] DATE) INSERT INTO @table2 (ID, FK_Table1, [Date]) VALUES(1, 1, '22/05/2014'), (2, 1, '01/02/2015'), (3, 1, '01/01/2015'), (4, 2, '01/01/2015'), (5, 2, '01/01/2013'), (6, 3, '01/01/2010') SELECT t1.*, t2.* FROM @table1 AS t1 LEFT JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY FK_Table1 ORDER BY [Date] DESC) AS RowNo FROM @table2 ) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo=1
这篇关于左连接只有第二个表中的一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文