使用union连接sql select语句

kyvafyod  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(407)

我尝试使用union all连接三个select查询,但显示错误。当我加入前两个select语句时,它工作得很好。当我添加第三条语句时,我无法执行它。
SQL查询

WITH CTE_BOOKINGS (TotalRecurrance,TotalBookingsWithoutRecurrance,TotalBookingsWithRecurrance,RoomID) AS
(
SELECT 0,count(BK.pkBookingID) as TotalBookingsWithoutRecurrance,0, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND NOT EXISTS (select * from tblBookingRecurrance where fkBookingID=BK.pkBookingID AND ACTIVE=1)
AND RM.roomID=16867
group by RM.roomID

UNION ALL

SELECT count(distinct BR.fkRecurranceID) as TotalRecurrance,0,0, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
INNER JOIN tblBookingRecurrance BR WITH (NOLOCK) ON BK.pkBookingID=BR.fkBookingID
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND RM.roomID=16867
AND BR.active=1
group by RM.roomID

UNION ALL

SELECT 0,0,count(BK.pkBookingID) as TotalBookingsWithRecurrance, RM.roomID as RoomID
FROM dbo.tblBooking BK WITH (NOLOCK)
INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
WHERE
BK.deleteBooking=0
AND BDT.UTC_bookingEnd > GETUTCDATE()
AND BI.primaryRoom=1
AND BI.Active=1
AND RM.roomID=16867
group by RM.roomID
)

SELECT  SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID FROM CTE_BOOKINGS Group BY RoomID order by 1 desc

运行查询时显示以下错误:
错误

Column 'CTE_BOOKINGS.TotalBookingsWithRecurrance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
epggiuax

epggiuax1#

使用时 UNION ,从第一个 SELECT 为整个结果集返回。因此,即使您指定“totalbookingswithoutrecurrance”、“totalrecurrance”和“totalbookingswithrecurrance”,您的结果集(给出当前示例)列标题也将是“totalrecurrance”。
我相信你的问题在于你最终的选择:

SELECT  SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID FROM CTE_BOOKINGS Group BY RoomID order by 1 desc

totalbookingswithrecurrance没有被分组,您也没有执行某种聚合操作——因此出现了错误。
保持原样:

SELECT SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID 
FROM CTE_BOOKINGS 
GROUP BY RoomID, TotalBookingsWithRecurrance
ORDER BY 1 DESC

但是,这将添加一个额外的分组级别,我怀疑您不希望这样做。
而且,您的列似乎没有在unioned语句之间对齐,这是必须的——至少是最佳实践(对我来说)。
我无法对此进行测试,但请尝试执行以下操作:

WITH CTE_BOOKINGS ( RoomID, RecurranceType, Recurrance, UnknownCol1, UnknownCol2  ) 
AS (    
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithoutRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND NOT EXISTS (select * from tblBookingRecurrance where fkBookingID=BK.pkBookingID AND ACTIVE=1)
        AND RM.roomID=16867
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalRecurrance' AS RecurranceType,
        COUNT ( DISTINCT BR.fkRecurranceID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2 
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    INNER JOIN tblBookingRecurrance BR WITH (NOLOCK) ON BK.pkBookingID=BR.fkBookingID
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=16867
        AND BR.active=1
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance, 
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=18841
    GROUP BY RM.roomID
)
SELECT
    SUM ( 
        CASE WHEN RecurranceType IN ( 'TotalRecurrance', 'TotalBookingsWithoutRecurrance' ) THEN Recurrance ELSE 0 END 
    ) AS TotalBookings, 
    SUM ( 
        CASE WHEN RecurranceType = 'TotalBookingsWithRecurrance' THEN Recurrance ELSE 0 END 
    ) AS TotalBookingsWithRecurrance, 
    RoomID 
FROM CTE_BOOKINGS
GROUP BY RoomID
ORDER BY 1 DESC;
zbwhf8kr

zbwhf8kr2#

你所犯的错误与 UNION . 第三个查询出错。你得先把它修好再把它和另外两个连接起来。
如果按单个字段分组,则该字段将是唯一不带聚合函数的可选字段。这里有一篇文章更深入地解释了这一点。
查看您的查询,您正在筛选特定的roomid,不确定您的确切目标是什么,但在这种情况下,您不需要按roomid分组。

相关问题