我尝试使用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.
2条答案
按热度按时间epggiuax1#
使用时
UNION
,从第一个SELECT
为整个结果集返回。因此,即使您指定“totalbookingswithoutrecurrance”、“totalrecurrance”和“totalbookingswithrecurrance”,您的结果集(给出当前示例)列标题也将是“totalrecurrance”。我相信你的问题在于你最终的选择:
totalbookingswithrecurrance没有被分组,您也没有执行某种聚合操作——因此出现了错误。
保持原样:
但是,这将添加一个额外的分组级别,我怀疑您不希望这样做。
而且,您的列似乎没有在unioned语句之间对齐,这是必须的——至少是最佳实践(对我来说)。
我无法对此进行测试,但请尝试执行以下操作:
zbwhf8kr2#
你所犯的错误与
UNION
. 第三个查询出错。你得先把它修好再把它和另外两个连接起来。如果按单个字段分组,则该字段将是唯一不带聚合函数的可选字段。这里有一篇文章更深入地解释了这一点。
查看您的查询,您正在筛选特定的roomid,不确定您的确切目标是什么,但在这种情况下,您不需要按roomid分组。