我需要查询帮助...... [英] I need help with a query...

问题描述

给出下表：
$


CREATE TABLE Test（idjob int，TeamName nvarchar（50），StartDate datetime，StopDate datetime）

$
插入测试（idjob，TeamName，StartDate，StopDate）值（1，'团队1'，'2019-02-20'，'2019-02-20'）

插入测试（idjob，TeamName，StartDate，StopDate）值（2，'团队2'，'2019-02-21'，'2019-02-21'）

插入Test（idjob，TeamName，StartDate，StopDate）值（3，'team 2'，'2019-02-22'，'2019-02-24'）

插入Test（idjob） ，TeamName，StartDate，StopDate）值（4，'team 3'，'2019-02-22'，'2019-02-22'）





我需要一个查询来返回以下结果：

idjob | TeamName |日期

1，团队1，'2019-02-20'

2，团队2，'2019-02-21'

2 ，第2队，'2019-02-22'
$
2，第2队，'2019-02-23'

3，第2队，'2019-02-24 '$
4，团队3，'2019-02-22'
$


a记录每天的StartDate和StopDate

请原谅我的英文并提前感谢您！

The following table is given:

CREATE TABLE Test (idjob int, TeamName nvarchar(50), StartDate datetime, StopDate datetime)

insert into Test (idjob, TeamName, StartDate, StopDate) values (1,'team 1','2019-02-20','2019-02-20')
insert into Test (idjob, TeamName, StartDate, StopDate) values (2,'team 2','2019-02-21','2019-02-21')
insert into Test (idjob, TeamName, StartDate, StopDate) values (3,'team 2','2019-02-22','2019-02-24')
insert into Test (idjob, TeamName, StartDate, StopDate) values (4,'team 3','2019-02-22','2019-02-22')


I need a query to return the following result:
idjob | TeamName | Date
1, team 1, '2019-02-20'
2, team 2, '2019-02-21'
2, team 2, '2019-02-22'
2, team 2, '2019-02-23'
3, team 2, '2019-02-24'
4, team 3, '2019-02-22'

a record for each day of the range StartDate and StopDate

Pls excuse my english and Thank you in advance!

推荐答案

CREATE TABLE Test (idjob int
, TeamName nvarchar(50)
, StartDate datetime
, StopDate datetime)

insert into Test (idjob, TeamName, StartDate, StopDate) 
values (1,'team 1','2019-02-20','2019-02-20')
, (2,'team 2','2019-02-21','2019-02-21')
, (3,'team 2','2019-02-22','2019-02-24')
, (4,'team 3','2019-02-22','2019-02-22')

 

declare @startdate date
declare @enddate date 
  
Select @startdate = min(StartDate),@enddate = max(StopDate) from test 
  
--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
  
 --  
,myidjobDate as
(
select  idjob,dateadd(day,n-1,@startdate) dt from Nums 
Cross join (Select Distinct idjob From  test ) a
where dateadd(day,n-1,@startdate)<=@enddate) 
select t.idjob,TeamName, [Date] from test t
cross apply (
select idjob, dt from  myidjobDate ) d(idjob, [Date]) 
WHERE t.idjob=d.idjob and d.[Date] between StartDate and StopDate 
 
  
drop TABLE Test

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