用于分段计数的SQL

Question

我正试图使用StackExchange数据浏览器创建一个在网站上提问的人的声誉直方图。

以下给出错误消息：

Each GROUP BY expression must contain at least one column that 
is not an outer reference.
Invalid column name 'lt_100'. ...

建议赞赏

select
  case when Reputation < 100    then "lt_100"
       when Reputation >= 100 and Reputation < 200   then "100_199"
       when Reputation >= 200 and Reputation < 300   then "200_299"
       when Reputation >= 300 and Reputation < 400   then "300_399"
       when Reputation >= 400 and Reputation < 500   then "400_499"
       when Reputation >= 500 and Reputation < 600   then "500_599"
       when Reputation >= 600 and Reputation < 700   then "600_699"
       when Reputation >= 700 and Reputation < 800   then "700_799"
       when Reputation >= 800 and Reputation < 900   then "800_899"
       when Reputation >= 900 and Reputation < 1000  then "900_999"
       else "over 1000"
  end  ReputationRange,
  count(*) as TotalWithinRange
FROM Users
JOIN Posts ON Users.Id = Posts.OwnerUserId 
JOIN PostTags ON PostTags.PostId = Posts.Id
JOIN Tags on Tags.Id = PostTags.TagId
WHERE PostTypeId = 1 and Posts.CreationDate > '9/1/2010'
Group by 
1

Answer 1

对于要分类的范围，应该使用single-quotes。如果使用" "，它将被视为列名。此外，还应将计算包含在group by子句中。

演示

select
  case when Reputation < 100    then 'lt_100'
       when Reputation >= 100 and Reputation < 200   then '100_199'
       when Reputation >= 200 and Reputation < 300   then '200_299'
       when Reputation >= 300 and Reputation < 400   then '300_399'
       when Reputation >= 400 and Reputation < 500   then '400_499'
       when Reputation >= 500 and Reputation < 600   then '500_599'
       when Reputation >= 600 and Reputation < 700   then '600_699'
       when Reputation >= 700 and Reputation < 800   then '700_799'
       when Reputation >= 800 and Reputation < 900   then '800_899'
       when Reputation >= 900 and Reputation < 1000  then '900_999'
       else 'over 1000'
  end ReputationRange,
  count(*) as TotalWithinRange
FROM Users
JOIN Posts ON Users.Id = Posts.OwnerUserId 
JOIN PostTags ON PostTags.PostId = Posts.Id
JOIN Tags on Tags.Id = PostTags.TagId
WHERE PostTypeId = 1 and Posts.CreationDate > '9/1/2010'
Group by 
case when Reputation < 100    then 'lt_100'
       when Reputation >= 100 and Reputation < 200   then '100_199'
       when Reputation >= 200 and Reputation < 300   then '200_299'
       when Reputation >= 300 and Reputation < 400   then '300_399'
       when Reputation >= 400 and Reputation < 500   then '400_499'
       when Reputation >= 500 and Reputation < 600   then '500_599'
       when Reputation >= 600 and Reputation < 700   then '600_699'
       when Reputation >= 700 and Reputation < 800   then '700_799'
       when Reputation >= 800 and Reputation < 900   then '800_899'
       when Reputation >= 900 and Reputation < 1000  then '900_999'
       else 'over 1000'
end

Answer 2

不幸的是，您不能像在订单中那样使用"1"来别名组。-为了避免在您的组中重复case语句，您可以利用SQL中的"with"子句：

with data as (
select
  case when Reputation < 100    then 'lt_100'
       when Reputation >= 100 and Reputation < 200   then '100_199'
       when Reputation >= 200 and Reputation < 300   then '200_299'
       when Reputation >= 300 and Reputation < 400   then '300_399'
       when Reputation >= 400 and Reputation < 500   then '400_499'
       when Reputation >= 500 and Reputation < 600   then '500_599'
       when Reputation >= 600 and Reputation < 700   then '600_699'
       when Reputation >= 700 and Reputation < 800   then '700_799'
       when Reputation >= 800 and Reputation < 900   then '800_899'
       when Reputation >= 900 and Reputation < 1000  then '900_999'
       else 'over 1000'
       end as ReputationRange FROM Users
JOIN Posts ON Users.Id = Posts.OwnerUserId 
JOIN PostTags ON PostTags.PostId = Posts.Id
JOIN Tags on Tags.Id = PostTags.TagId
WHERE PostTypeId = 1 and Posts.CreationDate > '9/1/2010')
select  ReputationRange, count(*) as TotalWithinRange
from data
Group by ReputationRange

工作演示/示例