如何通过联接两个表来获得日期差? [英] How to get the date difference by joining two tables?

问题描述

我有类似

Table1:

id  Name   date
--------------
1   xxx  01/01/2015
2   yyy  01/02/2015
3   aaa  02/03/2015

Table 2:

id Name   date
--------------
 1  xxx    07/01/1015
 2  xxx    09/01/2015
 3  yyy    05/02/2015
 4. aaa    04/03/2015

我试图通过连接两个表来获取日期差.但是当第一次出现该名称时，我必须使用Table1来获取日期差.如果名称出现重复值，则意味着仅通过使用Table2即可获得差值.请以(dd/mm/yyyy)格式记录日期 结果应如下所示，

I am trying to get the date difference by joining two tables.But I have to get the difference by using Table1 when the name is occurred first time. If the duplicate value for the name is occurred means, it gets the difference by using Table2 alone. Please note the date in (dd/mm/yyyy) format Result should look like this,

 Name  DateDifference

 xxx    6  (07/01/2015 - 01/01/2015)
 xxx    2  (07/01/2015 - 09/01/2015)
 yyy    4  (01/02/2015- 05/02/2015)
 aaa    2   (02/03/2015 -04/03/2015)

我尝试过的SQL查询是

The SQL query I have tried is,

SELECT    s.spidername,   
        T1.Date, 
        MIN(T2.Date) AS Date2, 
        DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff

FROM    Spider_DailyJobCount T1
        LEFT JOIN Spider_DailyJobCount T2
            ON T1.SpiderName = T2.SpiderName
            AND T2.Date > T1.Date
join spider s on s.spidername = t1.spidername
 where //Condition
GROUP BY   T1.Date,s.spidername

我尝试过的

LINQ查询是

LINQ query I have tried is,

var query = (from item1 in Table1
                join item2 in Table1 on item1.Name equals item2.SpiderName
                where item2.Date > item1.Date
                join origin in Table2 on item1.Name equals origin.Name
                where // condition
                group new { item1,item2 } by new {  item1.Name,item1.Date } into grp
                select new
                {                   

                    DayDiff = DbFunctions.DiffDays(grp.Key.Date, grp.Min(d => d.item2.Date)),
                }
                ).ToList();

我已经尝试了这些方法，但是无法获得所需的输出.我只尝试了Table2值.我真的不知道如何从Table1首次出现时获取值. 任何帮助或建议，表示赞赏. 在此先感谢！

I have tried these things, But could not get the needed output. I have tried only Table2 values. I really don't know how to get the value from Table1 for first occurrence. Any help or suggestion is appreciated. Thanks in Advance!

推荐答案

t-sql解决方案

declare @Table1 table
(
    id  int,
    Name    varchar(3),
    date    date
)

declare @Table2 table
(
    id  int,
    Name    varchar(3),
    date    date
)

set dateformat dmy

insert into @Table1 select 1,   'xxx',  '01/01/2015'
insert into @Table1 select 2,   'yyy',  '01/02/2015'
insert into @Table1 select 3,   'aaa',  '02/03/2015'

insert into @Table2 select 1,  'xxx',    '07/01/2015'
insert into @Table2 select 2,  'xxx',    '09/01/2015'
insert into @Table2 select 3,  'yyy',    '05/02/2015'
insert into @Table2 select 4,  'aaa',    '04/03/2015'

-- Actual Query
; with cte as
(
    select  *, rn = row_number() over (partition by Name order by date)
    from
    (
        select * from @Table1 union all select * from @Table2
    ) t
)
select  c1.Name, c1.date, c2.date, diff = datediff(day, c1.date, c2.date)
from    cte c1
    inner join cte c2   on  c1.Name = c2.Name
                and c1.rn   = c2.rn - 1

简要说明:

• 看起来您的要求实际上是查找2条记录之间的日期差异
• 这就是UNION ALL的工作.它将两个表合并为一个
• row_number()用于按日期升序为具有相同名称的行分配一个顺序编号
• 下一步是通过INNER JOIN获取日期差异

这篇关于如何通过联接两个表来获得日期差?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！