sql查询问题请帮忙 [英] sql query problem please help

问题描述

大家好，

create table #leave 
( 
        LeaveId INT IDENTITY(1,1) 
        ,EmployeeId INT 
        ,FromDate Datetime 
        ,ToDate Datetime 
        ,[Status] VARCHAR(50) 
) 
 
insert into #leave values(1,'2011-10-25','2011-10-27','Approved') 
insert into #leave values(2,'2011-10-26','2011-10-27','Approved') 
insert into #leave values(3,'2011-10-27','2011-10-27','Rejected') 
insert into #leave values(1,'2011-11-01','2011-10-01','Approved') 
 
 
select * from #leave 

谁能建议我以这种方式返回表的查询

can anyone suggest me the query to return table in this way

EmployeeId       Date                        Status
-----------     -----------------------      --------------
1                 2011-10-25 00:00:00.000  Approved
1                 2011-10-26 00:00:00.000  Approved
1                 2011-10-27 00:00:00.000  Approved
2                 2011-10-26 00:00:00.000  Approved
2                 2011-10-27 00:00:00.000  Approved
3                 2011-10-27 00:00:00.000  Rejected
1                 2011-11-01 00:00:00.000  Approved

FromDate和ToDate之间的日期也与EmployeeId和Status分开显示
在此先感谢

the date between FromDate and ToDate are also diplayed with EmployeeId and Status
thanks in advance

推荐答案

尝试这些查询..

TRY WITH THESE QUERIES..

create table #leave
(
        LeaveId INT IDENTITY(1,1)
        ,EmployeeId INT
        ,FromDate Datetime
        ,ToDate Datetime
        ,[Status] VARCHAR(50)
)

insert into #leave values(1,'2011-10-25','2011-10-27','Approved')
insert into #leave values(2,'2011-10-26','2011-10-27','Approved')
insert into #leave values(3,'2011-10-27','2011-10-27','Rejected')
insert into #leave values(1,'2011-11-01','2011-10-01','Approved')


select * from #leave

IF OBJECT_ID('#sample') IS NOT NULL
    DROP TABLE #sample 
 
SELECT * INTO #sample FROM #leave WHERE 1=2

DECLARE @LeaveID int, @EmpID int
DECLARE @StDate DATETIME, @EndDate DATETIME
DECLARE @status varchar(50)
DECLARE cur CURSOR FOR SELECT * FROM #leave
OPEN cur
FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
PRINT @EndDate
WHILE(@@FETCH_STATUS = 0)
BEGIN
    WHILE(@StDate <= @EndDate)
    BEGIN
        INSERT INTO #sample(EmployeeId,FromDate,[Status]) VALUES(@EmpID,@StDate,@status)
        SET @StDate = DATEADD(DD,1,@StDate)
    END
    FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
END
CLOSE cur

SELECT EmployeeId,FromDate as 'Date',[Status] FROM #sample

希望它能起作用....

HOPE IT WORKS....

这里是:

Here it is :

with a(i, j, k, l , m)
as
(
  select * from #leave
), b(i, j, k, l , m)
as
(
select a.i, a.j, a.k , a.l , m  from a
union all
select b.i, b.j, DATEADD(d, 1, b.k) , cast(b.l as datetime), b.m  from b
where b.k<b.l
)
select j EmployeeId, k Date, m Status from b
order by i, k

感谢CTE的魔力；)

也请原谅我不方便的命名方式.

祝你好运.

Thanks to the magic of CTEs ;)

Also excuse me for the inconvenient naming style.

Good Luck.

select EmployeeId,DateDiff(d,FromDate,ToDate) as [Difference in Day],[Status] from #leave

这将根据您的需要显示结果.

This will shows the result as your need.

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