我想要自我连接的Sql查询 [英] I want a Sql Query for self join

问题描述

在我的项目中，我正在使用一个表tbloperatinghours ..表结构是这样的

In my project i am using a table tbloperatinghours..the table structure is like this

[timingId]  ,[StartTime]   ,[EndTime]    ,[ShiftId]    ,[BranchId] ,[DayId]
    1       10:00:00.000    12:00:00.000     1             112         1
    2       12:00:00.000    13:00:00.000     2             112         1
    3       13:00:00.000    14:00:00.000     3             112         1
    4       14:00:00.000    15:00:00.000     1             112         2
    5       15:00:00.000    16:00:00.000     2             112         2

我需要像这样的值

I need the values like this

           Shift1                 Shift2                       Shift3                 
10:00:00.000-12:00:00.000     12:00:00.000-13:00:00.000      13:00:00.000-14:00:00.000

我怎么能得到这个?

请帮助我....

how can i get this?

plz help me....

推荐答案

尝试一下此代码块...

Hi Try this code block...

DECLARE @DeptShiftDetails TABLE
(
[timingId] INT,[StartTime] VARCHAR(15),[EndTime] VARCHAR(15),[ShiftId] INT,[BranchId] INT,[DayId] INT
)

INSERT INTO @DeptShiftDetails VALUES
(1,'10:00:00.000','12:00:00.000',1,112,1),
(2,'12:00:00.000' ,'13:00:00.000',2 ,112 ,1),
(3,'13:00:00.000','14:00:00.000', 3, 112, 1),
(4,'14:00:00.000','15:00:00.000',1, 112, 2),
(5, '15:00:00.000','16:00:00.000', 2, 112, 2),
(6,'09:00:00.000','11:00:00.000',1,112,3);

DECLARE @strQuery VARCHAR(MAX);

WITH ShiftCTE As
(
  SELECT timingId,[StartTime]+'-'+[EndTime] ShiftTiming, 'Shift'+CAST(ShiftId as VARCHAR) ShiftName,DayId 
  FROM @DeptShiftDetails
)
SELECT DayId,ISNULL(Shift1,'No Work') Shift1,ISNULL(Shift2,'No Work') Shift2,ISNULL(Shift3,'No Work') Shift3 FROM (
SELECT DayId,ShiftTiming,ShiftName FROM ShiftCTE) Main
PIVOT
(
 MAX(ShiftTiming) FOR ShiftName IN (Shift1,Shift2,Shift3)
) PVT

谢谢

Thank You

Select case when k.shiftid=1 then  isnull(j.starttime  +' '+  j.endtime,'') end as Shift1,
case when k.shiftid=2 then  isnull(j.starttime  +' '+  j.endtime,'') end as Shift2,
case when k.shiftid=3 then  j.starttime  +' '+  j.endtime end as Shift3
from jk j
JOIN jk k
ON     j.shiftid=k.shiftid
AND    j.timingid=k.timingid
AND    j.dayid=k.dayid

请尝试上述查询.

我希望它会有用.

Try the above query.

I hope it will useful.

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