代码之家 › 专栏 › 技术社区 › Asher

展平一对多查询

sql-server-2012 sql-server sql

Asher · 技术社区 · 8 年前

我有一张桌子叫 Event 这个连接到一个或多个 Event Spaces 每个活动空间内都有预订开始和结束时间。

这些多个事件空间线由 event type 指定活动空间预订是签入、签出还是实际活动的列。

表结构如下所示

Event:
Event ID, Booking Start Date, Booking End Date

Event Space:
Event ID, SpaceCode, Event Type, Booking Start Time, Booking End Time

我想在我的结果中把这些预订开始时间和结束时间放在一行。需要注意的是,并非所有事件都有多个事件空间,有些事件只有一个。

所需输出:

Event ID, Bump in Start Time, Bump in End Time, Event Start Time, Event End Time, Bump out start time, bump out end time

当以上列中的一列没有结果时,可以将其保留为空,或者如果有人有更好的解决方案,请告诉我。

到目前为止,我已经编写了一个通用的表表达式来将这些数据汇集在一起,但生成的数据集尚未平坦化。我很难想出这样的设计,如果有任何想法,我将不胜感激。

非常感谢。

查询:

    /*
    Bump in, bump out, event time
    grouped by DAY


*/
WITH Revenue as
(
SELECT  EV200_EVENT_MASTER.EV200_EVT_ID as [Event], 
        Revenue = 
        SUM(Case
        When Orddtl.ER101_PHASE = '1' and ORDDTL.ER101_COMPL_STS = 'N'
        then ORDDTL.ER101_EXT_CHRG 
        When ORDDTL.ER101_PHASE = '5' and ORDDTL.ER101_COMPL_STS = 'N'
        then ORDDTL.ER101_EXT_CHRG 
        Else 0
        End),
        SM.EV800_SPACE_DESC,
        SM.EV800_SPACE_CODE

FROM    ungerboeck.dbo.EV200_EVENT_MASTER WITH (NOLOCK) 
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER PrimCoord WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER FloorMgr WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE --  Floor manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER EventAccount WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking 
        LEFT OUTER JOIN ungerboeck.dbo.EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
        AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE 
        LEFT OUTER JOIN ungerboeck.dbo.EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
        ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE
        LEFT OUTER JOIN ungerboeck.dbo.EV802_SPACE_BKD SPBK 
        ON SPBK.EV802_ORG_CODE = EV200_ORG_CODE AND SPBK.EV802_EVT_ID = EV200_EVT_ID
        LEFT OUTER JOIN ungerboeck.dbo.ER101_ACCT_ORDER_DTL ORDDTL
        ON ORDDTL.ER101_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE 
        AND ORDDTL.ER101_EVT_ID = EV200_EVENT_MASTER.EV200_EVT_ID
        LEFT OUTER JOIN ungerboeck.dbo.EV800_SPACE_MASTER SM
        ON Sm.EV800_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE
        AND SM.EV800_SPACE_CODE = SPBK.EV802_BKD_SPACE

WHERE   EV200_EVENT_MASTER.EV200_ORG_CODE = '10' 
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52 
        AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings

GROUP BY EV200_EVENT_MASTER.EV200_EVT_ID,SM.EV800_SPACE_DESC,EV800_SPACE_CODE

), eventdetails as
(
SELECT  EV200_EVENT_MASTER.EV200_EVT_ID as [Event], 
        EV215_EVT_TYP_DESC as [Event Type],
        EV200_event_master.EV200_EVT_DESC,
        EV200_EVENT_MASTER.EV200_PLN_ATTEND,
        --EventAccount.EV870_NAME AS [Account], 
        PrimCoord.EV870_FIRST_NAME + ' ' + PrimCoord.EV870_LAST_NAME AS [Event Manager]
        --FloorMgr.EV870_FIRST_NAME + ' ' + FloorMgr.EV870_LAST_NAME AS [Floor Manager]

FROM    ungerboeck.dbo.EV200_EVENT_MASTER WITH (NOLOCK) 
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER PrimCoord WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER FloorMgr WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE --  Floor manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER EventAccount WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking 
        LEFT OUTER JOIN ungerboeck.dbo.EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
        AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE 
        LEFT OUTER JOIN ungerboeck.dbo.EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
        ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE

WHERE   EV200_EVENT_MASTER.EV200_ORG_CODE = '10' 
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52 
        AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings


), spacedetails as
(
SELECT distinct 
SPDTL.EV803_BKD_SPACE,
SPDTL.EV803_EVT_ID,
SM.EV800_SPACE_DESC,
CONVERT(DATE,SPDTL.EV803_BKG_DATE) as bkg_date,
spdtl.EV803_START_TIME,
spdtl.EV803_END_TIME,
SPDTL.EV803_BKG_START_TIME,
SPDTL.EV803_BKG_END_TIME,
SPDTL.EV803_USAGE,
EV800_NOTE_1 AS [VENUE]
FROM ungerboeck.dbo.EV803_SPACE_BKD_DTL SPDTL
INNER JOIN ungerboeck.dbo.EV800_SPACE_MASTER SM ON SPDTL.EV803_BKD_SPACE = SM.EV800_SPACE_CODE

)

select eventdetails.Event,
[Event Type],
eventdetails.EV200_EVT_DESC as 'Description',
eventdetails.EV200_PLN_ATTEND as 'PAX',
eventdetails.[Event Manager],
spacedetails.EV800_SPACE_DESC as 'Space',
spacedetails.bkg_date as 'Booking Date',
spacedetails.VENUE,
/*
(CASE 
    WHEN spacedetails.EV803_USAGE = 'IN' THEN 'BUMP IN'
    WHEN spacedetails.EV803_USAGE = 'ET' THEN 'EVENT'
    WHEN spacedetails.EV803_USAGE = 'OUT' THEN 'BUMP OUT'
END) as 'Booking Type',
*/
(SELECT spacedetails.
CAST(spacedetails.EV803_BKG_START_TIME as time(0)) as 'Start Time',
CAST(spacedetails.EV803_BKG_END_TIME as time(0)) as 'End Time',
revenue.revenue


from eventdetails 
inner join spacedetails on eventdetails.Event = spacedetails.EV803_EVT_ID 
inner join Revenue on spacedetails.EV803_EVT_ID = revenue.Event and spacedetails.EV803_BKD_SPACE = Revenue.EV800_SPACE_CODE

1 回复 | 直到 8 年前

Asher 8 年前

我能够回答我自己的问题。

我使用了子查询

 (
    SELECT TOP(1) s.EV803_START_TIME
    FROM spacedetails s
    WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
    AND s.EV803_USAGE = 'IN'
    AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
    AND s.bkg_date = ss.bkg_date
    ) AS 'Bump-in Start Time',
    (
    SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
    FROM spacedetails s
    WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
    AND s.EV803_USAGE = 'IN'
    AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
    AND s.bkg_date = ss.bkg_date
    ) AS 'Bump-in End Time',


(
SELECT TOP(1) s.EV803_START_TIME
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event End Time',

(
SELECT TOP(1) s.EV803_START_TIME
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out End Time',