mssql错误Error8120:分组必须只能查询分组字段和其他字段的聚合

in mssql •  7 years ago 

平时都是使用myssql的我,最近不得不开始学习mssql了。

在mysql下:

select author, Datepart(hour, created) as hh from comments 
where created>='2017-08-01 0:00:00' 
and title like "穿越诗歌"
and permlink not like 're-%'
group by Datepart(hour, created)

这是一点问题也没有的。可是在mssql下,系统提示:

Error 8120: Column 'author' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

虽然我的英语不咋的,但是计算英语还是能看懂大概的。这句的意思是:author这个字段在查询中无效,因为它既不用于聚合函数,也不属于组子句的字段。

我蒙了呀,难道我如果查询10个字段,都要按照分个组吗?

后来我冷静的思考了一下。分组以后,每组的author值有很多,那么这句话应该查出来一个哪一条记录了。所以要用count max min等聚合函数聚合一下才行。

在mysql中,分组以后如果没有指定,就会自动把第一条记录的author值查询出来。

事实上呢,这个含有穿越诗歌的文章都是同一个作者写的,选哪一条记录都一样。

可以看出,mssql很严谨,在语法上就不通过了,这样的句子是不能执行的。 mssql的确实是比mysql要严谨呀。

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Congratulations @dailystudy! You have completed the following achievement on Steemit and have been rewarded with new badge(s) :

Award for the number of upvotes

Click on the badge to view your Board of Honor.
If you no longer want to receive notifications, reply to this comment with the word STOP

Do not miss the last post from @steemitboard:
SteemitBoard World Cup Contest - Final results coming soon

Do you like SteemitBoard's project? Then Vote for its witness and get one more award!

Congratulations @dailystudy! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 1 year!

Click here to view your Board

Support SteemitBoard's project! Vote for its witness and get one more award!

Congratulations @dailystudy! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!