内部联接SQL Pivot表 [英] Inner join a SQL Pivot table
本文介绍了内部联接SQL Pivot表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
为了获得名字和姓氏,我似乎无法内部连接到教师表.
I cant seem to inner join to a teachers table in order to get the first and surname.
select *
from BookingDays bd
inner join Teachers t
on t.ID = bd.TeacherID
pivot
(
max (bd.BookingDuration)
for bd.DayText in ([MONDAY], [TUESDAY], [WEDNESDAY], [THURSDAY], [FRIDAY])
) as MaxBookingDays
where bd.BookingDate >= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)) and
bd.BookingDate <= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6)) '
我收到的错误消息是-
Msg 8156, Level 16, State 1, Line 3
The column 'ID' was specified multiple times for 'MaxBookingDays'.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "bd.BookingDate" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "bd.BookingDate" could not be bound.
推荐答案
以下错误是因为您未指定列:
The error below is because you are not specifying your columns:
Msg 8156,第16级,状态1,第3行
为"MaxBookingDays"多次指定了"ID"列.
所以我将您的查询稍作更改,如下所示:
So I would change your query slightly to something like this:
select *
from
(
-- don't use select *, call out your fields
-- and use a sub-query with your WHERE inside the subquery
select bd.BookingDuration, bd.Otherfields, t.Fields
from BookingDays bd
inner join Teachers t
on t.ID = bd.TeacherID
where bd.BookingDate >= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)) and
bd.BookingDate <= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6))
) src
pivot
(
max (BookingDuration)
for DayText in ([MONDAY], [TUESDAY], [WEDNESDAY],[THURSDAY], [FRIDAY])
) as MaxBookingDays
这篇关于内部联接SQL Pivot表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文