First of all I would like to thank the friends who helped this complex and difficult query.
首先,我要感谢帮助这个复杂而困难的查询的朋友们。
I have three tables
我有三张桌子
Table 1
表格1
StaffId FirstName LastName staffType
---------------------------------------
1 Adam Sorme Student
2 Lara Sandra Teacher
3 Jack Jones Student
Table 2
表2
GateId GateName
---------------------------------------
1 frontDoor
2 superDoor
Table 3
表3
Id transitionDate GateId StaffId
---------------------------------------
1 2018-01-1 08:00:00 1 1
2 2018-01-1 10:00:00 2 1
3 2018-01-1 20:00:00 2 1
4 2018-01-2 07:00:00 1 2
5 2018-01-2 10:00:00 1 3
6 2018-01-9 12:00:00 2 2
I want the first and last movements of students for each day. Value must be set to null if no movement is available between the specified dates
我想要每天学生的第一次和最后一次动作。如果指定日期之间没有可用的移动,则必须将值设置为null
transitionDate> '2018-01-1 00:00:00 000'
and transitionDate< '2018-01-03 00:00:00 000'
OUTPUT:
OUTPUT:
Id Date MinTransitionDate MaxTransitionDate FirstGateName LastGateName StaffId StaffType
1 2018-01-01 2018-01-1 08:00:00 2018-01-1 20:00:00 frontDoor superDoor 1 Student
2 2018-01-01 null null null null 3 student
3 2018-01-02 null null null null 1 student
4 2018-01-02 2018-01-2 10:00:00 null frontDoor null 3 student
3 个解决方案
#1
3
you can try a query like below
你可以尝试下面的查询
看工作演示
create table staff(StaffId int, FirstName nvarchar(10), LastName nvarchar(10), staffType nvarchar(10))
insert into staff values
(1,'Adam','Sorme','Student')
,(2,'Lara','Sandra','Teacher')
,(3,'Jack','Jones','Student')
go
create table gate(GateId int, GateName nvarchar(10))
insert into gate values
(1,'frontDoor')
,(2,'superDoor')
go
create table logs
(Id int, transitionDate datetime, GateId int, StaffId int)
insert into logs values
(1,'2018-01-1 08:00:00',1,1)
,(2,'2018-01-1 10:00:00',2,1)
,(3,'2018-01-1 20:00:00',2,1)
,(4,'2018-01-2 07:00:00',1,2)
,(5,'2018-01-2 10:00:00',1,3)
,(6,'2018-01-9 12:00:00',2,2)
go
declare @startdate datetime, @enddate datetime
select @startdate='2018-01-1 00:00:00' , @enddate='2018-01-03 00:00:00'
; with tempSet as
(
select
transitionDatetime=l.transitionDate,
gateName=g.gateName,
staffid=l.staffid,
idx=
row_number() over(partition by l.staffid order by l.transitionDate ) -
row_number() over(partition by l.staffid,cast(l.transitionDate as date) order by l.transitionDate ),
transitionDate=cast(l.transitionDate as date)
from
logs l inner join staff s on
l.staffid=s.staffid and staffType='Student'
join gate g on g.gateid=l.gateid
)
, groupedSet as
(
select
t1.*,
FirstGateName=t2.gatename,
lastGateName=t3.gatename
from
(
select
staffid,
mintransitionDate=min(transitionDatetime),
maxtransitionDate= case when count(1)>1 then max(transitionDatetime) else null end,
transitionDate=max(transitionDate),
idx
from
tempSet
group by staffid,idx
) t1
left join
tempSet t2
on t1.idx=t2.idx
and t1.staffid=t2.staffid and t1.mintransitionDate=t2.transitionDatetime
left join
tempSet t3
on t1.idx=t3.idx
and t1.staffid=t3.staffid and t1.maxtransitionDate=t3.transitionDatetime
where t1.transitionDate between @startdate and @enddate
)
select
t.*,
g.mintransitionDate,
g.maxtransitionDate,
g.FirstGateName,
g.LastGateName
from
groupedSet g
right join
(
select
d,
staffid
from
(
select
top (select datediff(d,@startdate, @endDate))
d=dateadd(d,row_number() over(order by (select null))-1, @startDate)
from
sys.objects o1 cross join sys.objects o2
)tally
cross join
staff
where staff.stafftype='Student'
)t
on cast(t.d as date)=cast(g.transitionDate as date) and t.staffid=g.staffid
order by t.d asc, t.staffid asc
#2
1
All insert statements:
所有插入语句:
create table #Staff (StaffId int, FirstName varchar(20), LastName varchar(20), staffType varchar(20))
insert into #Staff values
(1, 'Adam', 'Sorme', 'Student'),
(2, 'Lara', 'Sandra', 'Teacher'),
(3, 'Jack', 'Jones', 'Student')
create table #Gates (GateId int, GateName varchar(20))
insert into #Gates values
(1, 'frontDoor'),
(2, 'backDoor')
create table #Transitions (Id int, transitionDate datetime, GateId int, StaffId int)
insert into #Transitions values
(1, '2018-01-1 08:00:00', 1, 1),
(2, '2018-01-1 10:00:00', 2, 1),
(3, '2018-01-1 20:00:00', 2, 1),
(4, '2018-01-2 07:00:00', 1, 2),
(5, '2018-01-2 10:00:00', 1, 3),
(6, '2018-01-9 12:00:00', 2, 2)
In order to get desired result, you'll need CTE with all days between range. Limits (dates) of range you specify in variables included in below query.
为了获得理想的结果,您需要在范围之间的所有日期进行CTE。在下面的查询中包含的变量中指定的范围的限制(日期)。
declare @maxTransitionDate datetime, @minTransitionDate datetime
select @maxTransitionDate='2018-01-10', @minTransitionDate='2018-01-01'
;with cte as (
select @minTransitionDate [Days]
union all
select dateadd(day,1,[Days]) from cte where [Days] < @maxTransitionDate
)
,cte2 as (
select [Days], [StaffId], [FirstName], [LastName] from cte cross apply #staff where stafftype ='Student'
)
select C.[Days] [Date], C.[staffid], C.FirstName, C.LastName, A.mintransitiondate, A.[maxtransitiondate], A.firstgatename, A.lastgatename, A.staffType from (
select T.[Date],
T.staffid,
T.mintransitiondate,
case when T.maxtransitiondate <> T.mintransitiondate then T.maxtransitiondate end [maxtransitiondate],
(select gatename from #gates where gateid = T.firstgateid) firstgatename,
(select gatename from #gates where gateid = T.lastgateid) lastgatename,
S.FirstName,
S.LastName,
S.staffType
from (
select [date], staffid,
max(case when rn = 1 then transitiondate end) mintransitiondate,
max(case when rn = cnt then transitiondate end) maxtransitiondate,
max(case when rn = 1 then GateId end) FirstGateId,
max(case when rn = cnt then GateId end) LastGateId
from (
select *, cast(transitiondate as date) [Date],
row_number() over (partition by staffid, cast(transitiondate as date) order by transitiondate) [rn],
count(*) over (partition by staffid, cast(transitiondate as date)) [cnt]
from #Transitions
) a group by staffid, [date]
) [T] join #Staff [S] on T.staffid = S.staffid
where S.staffType = 'Student'
) [A] right join cte2 [C] on A.[Date] = C.[Days] and A.[staffid] = C.[staffid]
#3
0
The other answers seem so complicated.
其他答案看起来很复杂。
First, use cross join
to get all the rows. Then use left join
and aggregation to get the information you want.
首先,使用交叉连接来获取所有行。然后使用左连接和聚合来获取所需的信息。
The basic query looks like this:
基本查询如下所示:
select s.staffId, d.dte,
min(t.transitionDate) as first_change,
max(t.transitionDate) as first_change,
max(case when seqnum_asc = 1 then gateId end) as first_gateid,
max(case when seqnum_desc = 1 then gateId end) as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
(select distinct cast(transitionDate as date) as dte from Transitions) d left join
(select t.*,
row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
from Transitions t
) t
on cast(t.transitiondate as date) = d.dte and
t.staffId = s.staffId and
1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte;
You can enhance the select
to bring back more columns.
您可以增强选择以恢复更多列。
Here is a SQL Fiddle.
这是一个SQL小提琴。
#1
3
you can try a query like below
你可以尝试下面的查询
看工作演示
create table staff(StaffId int, FirstName nvarchar(10), LastName nvarchar(10), staffType nvarchar(10))
insert into staff values
(1,'Adam','Sorme','Student')
,(2,'Lara','Sandra','Teacher')
,(3,'Jack','Jones','Student')
go
create table gate(GateId int, GateName nvarchar(10))
insert into gate values
(1,'frontDoor')
,(2,'superDoor')
go
create table logs
(Id int, transitionDate datetime, GateId int, StaffId int)
insert into logs values
(1,'2018-01-1 08:00:00',1,1)
,(2,'2018-01-1 10:00:00',2,1)
,(3,'2018-01-1 20:00:00',2,1)
,(4,'2018-01-2 07:00:00',1,2)
,(5,'2018-01-2 10:00:00',1,3)
,(6,'2018-01-9 12:00:00',2,2)
go
declare @startdate datetime, @enddate datetime
select @startdate='2018-01-1 00:00:00' , @enddate='2018-01-03 00:00:00'
; with tempSet as
(
select
transitionDatetime=l.transitionDate,
gateName=g.gateName,
staffid=l.staffid,
idx=
row_number() over(partition by l.staffid order by l.transitionDate ) -
row_number() over(partition by l.staffid,cast(l.transitionDate as date) order by l.transitionDate ),
transitionDate=cast(l.transitionDate as date)
from
logs l inner join staff s on
l.staffid=s.staffid and staffType='Student'
join gate g on g.gateid=l.gateid
)
, groupedSet as
(
select
t1.*,
FirstGateName=t2.gatename,
lastGateName=t3.gatename
from
(
select
staffid,
mintransitionDate=min(transitionDatetime),
maxtransitionDate= case when count(1)>1 then max(transitionDatetime) else null end,
transitionDate=max(transitionDate),
idx
from
tempSet
group by staffid,idx
) t1
left join
tempSet t2
on t1.idx=t2.idx
and t1.staffid=t2.staffid and t1.mintransitionDate=t2.transitionDatetime
left join
tempSet t3
on t1.idx=t3.idx
and t1.staffid=t3.staffid and t1.maxtransitionDate=t3.transitionDatetime
where t1.transitionDate between @startdate and @enddate
)
select
t.*,
g.mintransitionDate,
g.maxtransitionDate,
g.FirstGateName,
g.LastGateName
from
groupedSet g
right join
(
select
d,
staffid
from
(
select
top (select datediff(d,@startdate, @endDate))
d=dateadd(d,row_number() over(order by (select null))-1, @startDate)
from
sys.objects o1 cross join sys.objects o2
)tally
cross join
staff
where staff.stafftype='Student'
)t
on cast(t.d as date)=cast(g.transitionDate as date) and t.staffid=g.staffid
order by t.d asc, t.staffid asc
#2
1
All insert statements:
所有插入语句:
create table #Staff (StaffId int, FirstName varchar(20), LastName varchar(20), staffType varchar(20))
insert into #Staff values
(1, 'Adam', 'Sorme', 'Student'),
(2, 'Lara', 'Sandra', 'Teacher'),
(3, 'Jack', 'Jones', 'Student')
create table #Gates (GateId int, GateName varchar(20))
insert into #Gates values
(1, 'frontDoor'),
(2, 'backDoor')
create table #Transitions (Id int, transitionDate datetime, GateId int, StaffId int)
insert into #Transitions values
(1, '2018-01-1 08:00:00', 1, 1),
(2, '2018-01-1 10:00:00', 2, 1),
(3, '2018-01-1 20:00:00', 2, 1),
(4, '2018-01-2 07:00:00', 1, 2),
(5, '2018-01-2 10:00:00', 1, 3),
(6, '2018-01-9 12:00:00', 2, 2)
In order to get desired result, you'll need CTE with all days between range. Limits (dates) of range you specify in variables included in below query.
为了获得理想的结果,您需要在范围之间的所有日期进行CTE。在下面的查询中包含的变量中指定的范围的限制(日期)。
declare @maxTransitionDate datetime, @minTransitionDate datetime
select @maxTransitionDate='2018-01-10', @minTransitionDate='2018-01-01'
;with cte as (
select @minTransitionDate [Days]
union all
select dateadd(day,1,[Days]) from cte where [Days] < @maxTransitionDate
)
,cte2 as (
select [Days], [StaffId], [FirstName], [LastName] from cte cross apply #staff where stafftype ='Student'
)
select C.[Days] [Date], C.[staffid], C.FirstName, C.LastName, A.mintransitiondate, A.[maxtransitiondate], A.firstgatename, A.lastgatename, A.staffType from (
select T.[Date],
T.staffid,
T.mintransitiondate,
case when T.maxtransitiondate <> T.mintransitiondate then T.maxtransitiondate end [maxtransitiondate],
(select gatename from #gates where gateid = T.firstgateid) firstgatename,
(select gatename from #gates where gateid = T.lastgateid) lastgatename,
S.FirstName,
S.LastName,
S.staffType
from (
select [date], staffid,
max(case when rn = 1 then transitiondate end) mintransitiondate,
max(case when rn = cnt then transitiondate end) maxtransitiondate,
max(case when rn = 1 then GateId end) FirstGateId,
max(case when rn = cnt then GateId end) LastGateId
from (
select *, cast(transitiondate as date) [Date],
row_number() over (partition by staffid, cast(transitiondate as date) order by transitiondate) [rn],
count(*) over (partition by staffid, cast(transitiondate as date)) [cnt]
from #Transitions
) a group by staffid, [date]
) [T] join #Staff [S] on T.staffid = S.staffid
where S.staffType = 'Student'
) [A] right join cte2 [C] on A.[Date] = C.[Days] and A.[staffid] = C.[staffid]
#3
0
The other answers seem so complicated.
其他答案看起来很复杂。
First, use cross join
to get all the rows. Then use left join
and aggregation to get the information you want.
首先,使用交叉连接来获取所有行。然后使用左连接和聚合来获取所需的信息。
The basic query looks like this:
基本查询如下所示:
select s.staffId, d.dte,
min(t.transitionDate) as first_change,
max(t.transitionDate) as first_change,
max(case when seqnum_asc = 1 then gateId end) as first_gateid,
max(case when seqnum_desc = 1 then gateId end) as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
(select distinct cast(transitionDate as date) as dte from Transitions) d left join
(select t.*,
row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
from Transitions t
) t
on cast(t.transitiondate as date) = d.dte and
t.staffId = s.staffId and
1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte;
You can enhance the select
to bring back more columns.
您可以增强选择以恢复更多列。
Here is a SQL Fiddle.
这是一个SQL小提琴。