SQL - 从 SQL 查询查询 - 如何从未知查询读取

Question

我有一个SQL查询，用于多次访问森林n字符：

select name, count(forest_entry) from characters
group by name

这导致：

name      |  count(forest_entry)
==========|==============
Gandalf   |  3
Samwise   |  2
Hobbit    |  3
Ork       |  2
Frodo     |  2
Galadriel |  1

现在我想反转字符计数次数。例如，3个人去了森林2次。1人去森林1次(加拉德列尔(。2人去了森林3次。 问题是我没有可以从第一个查询中读取的列名。

我怎样才能达到这个结果：

number of times |  number of
went to forest  |  characters
================|==============
3             |  2
1             |  1
2             |  3

Answer 1

为计算列指定别名以在外部查询中使用它

select times, count(*) people
from
(
select name, count(forest_entry) times
from characters
group by name
) tmp
group by times

Answer 2

您可以使用子查询首先返回每个字符的条目计数，然后按条目计数分组并对此执行另一个计数：

DECLARE @forestentries TABLE (name varchar(50), entered bit)
INSERT INTO @forestentries VALUES ('Gandalf',1)
INSERT INTO @forestentries VALUES ('Gandalf',1)
INSERT INTO @forestentries VALUES ('Gandalf',1)
INSERT INTO @forestentries VALUES ('Samwise',1)
INSERT INTO @forestentries VALUES ('Samwise',1)
SELECT EntryCount, COUNT(*) [number of characters] FROM (
SELECT Name, COUNT(entered) as EntryCount from @forestentries group by Name ) a
GROUP BY EntryCount