如何比较没有关系的两个表(1个表格与40k记录，另一个具有7K记录的记录)

Question

我尝试使用交叉加入，但需要5分钟才能运行，还有另一种方法可以做到吗？笔记：我正在比较日期和int字段，表1具有系统的记录，表2存储了工作日历日期。SQL Server。

b文件

c文件

试图实现

samplecode

Answer 1

您不简单，但这是一个有效的解决方案。将来，尝试在您的问题中使用这种代码，以便我们可以专注于查询和解决方案。我已经编辑了您的一些日期，以使示例起作用。

编辑：新代码

declare @cal table (
    calID int not null
,   date_ date not null
,   isWeekday bit not null
,   isHoliday bit not null
,   year_  int not null
);
insert into @cal (calID, date_, isWeekday, isHoliday, year_)
select 1,    '1-Jan-2010',  1,  1,  2010 union all
select 2,    '2-Jan-2010',  0,  0,  2010 union all
select 3,    '3-Jan-2010',  0,  0,  2010 union all
select 4,    '4-Jan-2010',  1,  0,  2010 union all
select 5,    '5-Jan-2010',  1,  0,  2010 union all
select 6,    '6-Jan-2010',  1,  0,  2010 union all
select 7,    '7-Jan-2010',  1,  0,  2010 union all
select 8,    '8-Jan-2010',  1,  0,  2010 union all
select 9,    '9-Jan-2010',  0,  0,  2010 union all
select 10,  '10-Jan-2010',  0,  0,  2010 union all
select 11,  '11-Jan-2010',  1,  0,  2010 union all
select 12,  '12-Jan-2010',  1,  0,  2010 union all
select 13,  '13-Jan-2010',  1,  0,  2010 union all
select 14,  '14-Jan-2010',  1,  0,  2010 union all
select 15,  '15-Jan-2010',  1,  0,  2010 union all
select 16,  '16-Jan-2010',  0,  0,  2010 union all
select 17,  '17-Jan-2010',  0,  0,  2010 union all
select 18,  '18-Jan-2010',  1,  1,  2010 union all
select 19,  '19-Jan-2010',  1,  0,  2010 union all
select 20,  '20-Jan-2010',  1,  0,  2010 union all
select 21,  '21-Jan-2010',  1,  0,  2010 union all
select 22,  '22-Jan-2010',  1,  0,  2010 union all
select 23,  '23-Jan-2010',  0,  0,  2010 union all
select 24,  '24-Jan-2010',  0,  0,  2010 union all
select 25,  '25-Jan-2010',  1,  0,  2010 union all
select 26,  '26-Jan-2010',  1,  0,  2010;
declare @date table(
    dateID int identity(1,1) not null
,   date2 date null
,   date3 date null
,   date4 date null
,   date5 date null
);
insert into @date (date2, date3, date4, date5)
select '6/20/2009', NULL,   NULL,   '7/19/2009'   union all
select '1/2/2010',  NULL,   NULL,   '1/19/2010'   union all
select '1/4/2010',  NULL,   NULL,   '1/15/2010'  union all
select '1/2/2010',  NULL,   NULL,   '1/22/2010'  union all
select '9/17/2009', NULL,   NULL,   '10/26/2009'  union all
select '6/4/2009',  NULL,   NULL,   '6/24/2009';
;with cte as (
        select dateid
             , b.date_
          from @date
         cross apply ( 
                        Select Top (DateDiff(DAY,date2,IsNull(date5,date2))+1) DateAdd(DAY, -1+Row_Number() Over (Order By 1/0),date2) date_
                          from master..spt_values n1
                        ) b
            )
select distinct b.dateID
     , c.date2
     , c.date5
     , count(*) over(order by b.dateid) cnt
  from @cal a
  join cte b
    on a.date_ = b.date_
  join @date c
    on b.dateid = c.dateid
 where isWeekday = 1
   and isHoliday = 0

您可以更改from master..spt_values n1

这样的东西：

 ;with E00(n) as (select 1 union all select 1)
     , E02(n) as (select 1 from E00 a, E00 b)
     , E04(n) as (select 1 from E02 a, E02 b)
     , E08(n) as (select 1 from E04 a, E04 b)
     , E16(n) as (select 1 from E08 a, E08 b)
     , E32(n) as (select 1 from E16 a, E16 b)
, cteTally(d) as (select row_number() over (order by n) from E32)
        , cte as (
       select dateid
            , b.date_
         from @date
  cross apply   ( 
                        select top (datediff(day,date2,isnull(date5,date2))+1) dateadd(day, -1+row_number() over(order by 1/0),date2) date_
                          from cteTally
                        ) b
                )
       select distinct b.dateID
            , c.date2
            , c.date5
            , count(*) over(order by b.dateid) cnt
         from @cal a
         join cte b
           on a.date_ = b.date_
         join @date c
           on b.dateid = c.dateid
        where isWeekday = 1
          and isHoliday = 0