在sql server中根据日期条件获取记录 [英] get records based on date condition in sql server

问题描述

我在sql server中有一个问题



表1：Emp

I have a question in sql server

Table 1 :Emp

id  |Flag | Sdate      | Edate      | dname
1   |2    | 2015-09-14 | 2015-09-25 |ceo
1   |3    | 2015-09-16 |2015-09-23  |hr
1   |1    | 2015-07-21 |2015-09-28  | ce
2   |3    | 2014-04-20 |2014-05-24  |Hr
3   |3    |2013-05-24  |2013-08-21  |hm
4   |2    |2015-07-28  |2015-07-30  |ho

表2：emp1

Table 2 : emp1

id    |  Loc   | date
1     | hyd    |2015-09-19
1     | chen   |2015-05-24
1     | ben    |2015-07-21
2     | pune   |2015-04-23
3     |ce      |2013-05-26

这里如果emp1表相关日期与emp表sdate和edate然后带来如果没有为所需列传递空值，则需要colummns形成emp1 tabel。

当我们从第2个表中检索loc列时，我们必须考虑sdate和edate之间的日期

如果满足然后我们检索loc列数据其他明智的loc数据列认为是null



基于以上两个表我想要输出如下和

here if emp1 table related date foll betwwn emp table sdate and edate then bring required colummns form emp1 tabel if not pass null values for required columns.
when we retrive loc column from 2nd table that time we must consider date between sdate and edate
if satisfy then we retrive loc column data other wise loc data column consider as null

based on above two tables I want output like below and

id    |  Flage    | dname   | loc
1     | 2         | ceo     | hyd
1     | 3         | hr      |hyd
1     | 1         | ce      | ben
2     | 3         | hr      |null
3     | 3         |hm       | ce
4     | 2         | ho      |null

我试过以下

I tried like below

select a.id, a.flag, a.dname, b.loc 
from emp a 
left join emp1 b on a.id = b.id 
and  b.date between a.sdate and a.edate 

此查询未给出预期结果。请告诉我如何在sql server中执行查询以执行此任务

this query not give expected result . please tell me how to wirte query to achive this task in sql server

推荐答案

尝试以下查询：

Try below query:

SELECT id, Flag, dname, 
(SELECT TOP 1 loc FROM Emp1 WHERE [date] BETWEEN tempTab.Sdate AND tempTab.Edate) AS loc 
FROM Emp AS tempTab

首先，你必须使用 INNER JOIN 而不是 LEFT JOIN 。



为什么？请阅读： SQL连接的可视化表示 [ ^ ]

On the first look, you have to use INNER JOIN instead of LEFT JOIN.

Why? Please, read this: Visual Representation of SQL Joins[^]

这篇关于在sql server中根据日期条件获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！