每个GROUP BY表达式必须至少包含一个不是外部引用的列 [英] Each GROUP BY expression must contain at least one column that is not an outer reference

问题描述

如果有人可以帮助我解决我想在下面构建的查询，那会很棒。理想情况下，我希望最终得到一个包含7列的表：EstabName，Country，Province，EstablishmentID，bookingsCY，bookingsLY和Searches。我知道我很接近;但是因为它是
我不断收到错误消息：'每个GROUP BY表达式必须包含至少一个不是外部引用的列'等等。非常感激，如果有人可以采取以下脚本并为我调整它以使其正常工作。

Would be great if somebody could help me with the query I am trying to build below. Ideally I want to end up with a table with 7 columns: EstabName, Country, Province, EstablishmentID, bookingsCY, bookingsLY and Searches. I know im close; however as it stands I keep getting the error message: 'Each GROUP BY expression must contain at least one column that is not an outer reference' amongst others. Would appreciate it so much if someone could take the below script and tweak it for me to get it working.

非常感谢

SELECT

      bt.title as EstabName

，p.countryId as country

     ，p.provinceId as Province

     ，bt.MasterEstabId as EstablishmentId

     ，count（distinct（bt.bookingid））as bookingsCY

，count（distinct（bt.bookingid））as bookingsPY

     ，pr.ActiveSearch as searches





FROM [ dbo]。[Booking_tbl] as bt 



左加入[dbo]。[Booking_tbl] b bt.BookingId = b.BookingId

左连接[dbo]。[acomEstab_tbl] ae on bt.MasterEstabId = ae.estabid

left join [dbo]。[BookingMeta] p on bt.BookingId = p.BookingId

左边加入[dbo]。[acomProvince_tbl] pr on p.ProvinceId = pr.ProvinceId and p.CountryId = pr.CountryId 



其中bt.BookedDate在'2019-04-01'和'2019-05-22'之间为
和b.BookedDate在'2018-04-01'和'2018-05-22'之间为¥b $ b和bt.DomainBookedOn ='travelrepublic.co.uk'
$
和ae.title不为空白
和'bookingsCY'= 0

group by'bt.EstabName'

order by 1 asc，2 asc，6 desc

SELECT
      bt.title as EstabName
,p.countryId as country
     ,p.provinceId as Province
     ,bt.MasterEstabId as EstablishmentId
     ,count(distinct(bt.bookingid)) as bookingsCY
,count(distinct(bt.bookingid)) as bookingsPY
     ,pr.ActiveSearch as Searches


FROM [dbo].[Booking_tbl] as bt 

left join [dbo].[Booking_tbl] b on bt.BookingId= b.BookingId
left join [dbo].[acomEstab_tbl] ae on bt.MasterEstabId = ae.estabid
left join [dbo].[BookingMeta] p on bt.BookingId= p.BookingId
left join [dbo].[acomProvince_tbl] pr on p.ProvinceId= pr.ProvinceId and p.CountryId= pr.CountryId 

where bt.BookedDate between '2019-04-01' and '2019-05-22'
and b.BookedDate between '2018-04-01' and '2018-05-22'
and bt.DomainBookedOn ='travelrepublic.co.uk'
and ae.title is not null
and 'bookingsCY' = 0
group by 'bt.EstabName'
order by 1 asc, 2 asc, 6 desc

推荐答案

和'bookingsCY'= 0

group by 'bt .EstabName'

and 'bookingsCY' = 0
group by 'bt.EstabName'

您在 bt.EstabName 周围使用字符串文字，我猜它在哪里  ;是一个表别名+列名;删除了撇号。之前的一行对我来说是错误的。

You use string literal around bt.EstabName where I guess it is a table alias + column Name; removed the Apostrophe. Also the line before Looks wrong for me.

这篇关于每个GROUP BY表达式必须至少包含一个不是外部引用的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！