Sql Join查询问题 [英] Issue with Sql Join query

问题描述

有两个表-Table1和Table2.

表1具有以下字段

vId，sId
1 1
1 16

表2具有以下字段

sId，SDate SUnits
1 15-10-2004 1.5
1 14-08-2004 1.20

我的查询应为table2中的特定sId的Max(date)选择vId，sId，SUnit.如果Table2中不存在sId，则它仍应显示一行且列的值为空.
所需的最终结果是-

vId sId SUnits
1 1 1.5
1 16空

我写了下面的查询.该查询不带第二行(1,16，空).
无法从Table2检索没有sId(16)匹配行的记录.

There are two tables - Table1 and Table2.

Table1 has the following fields

vId, sId
1 1
1 16

Table2 has the following fields

sId, SDate SUnits
1 15-10-2004 1.5
1 14-08-2004 1.20

My query should select the vId, sId, SUnits for the Max(date) for a particular sId in table2. If a sId is not present in Table2, it should still display a row with null values for columns.
Final result that is required is -

vId sId SUnits
1 1 1.5
1 16 Null

I have written the below query. The query does not bring the second row (1,16,null).
It fails to retrieve records from Table2 for which there are no matching rows for sId(16).

Select Table1.vId,Table1.sId,sUnits
from Table1 Left Join Table2
ON Table1.sId=Table2.sId
where sDate=(
SELECT MAX(sdate) FROM  Table2 AS S
      WHERE S.sId = Table2.sId or sDate is null)
 and vId=1

有任何想法吗?

Any thoughts?

推荐答案

将"sDate为null"移到方括号之外并且有效.

Moved "sDate is null" to outside the bracket and that worked.

Select Table1.vId,Table1.sId,sUnits
from Table1 Left Join Table2
ON Table1.sId=Table2.sId
where (
sDate=(SELECT MAX(sdate) FROM Table2 AS S
WHERE S.sId = Table2.sId)
OR sDate is null)
and vId=1

这篇关于Sql Join查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！