SQL 不是单组组功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10210320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Not a single-group group function
提问by Triton Man
I have some tables which basically look as follows:
我有一些表格，基本上如下所示：
TBL_USER
user_id - number
user_name - varchar
TBL_STUFF
stuff_id - number
stuff_user_id - number
I want to query for all user information including the number of "stuff" they have. I was trying something like this:
我想查询所有用户信息，包括他们拥有的“东西”的数量。我正在尝试这样的事情：
select user_id, user_name, count(stuff_id)
from tbl_user
left outer join tbl_stuff on stuff_user_id = user_id
where user_id = 5;
but I get an error which says "not a single-group group function"
但我收到一条错误消息，显示“不是单组组功能”
Is there some other way I should be doing this?
还有其他方法我应该这样做吗？
采纳答案by Arion
You could also do it like this:
你也可以这样做：
select
user_id,
user_name,
(
SELECT
COUNT(*)
FROM
tbl_stuff
WHERE
stuff_user_id = tbl_user.user_id
) AS StuffCount,
from
tbl_user
where
user_id = 5;
回答by Daniel Hilgarth
Well, you are missing the group function ;-)
好吧，您缺少组功能；-)
Try this:
尝试这个：
select user_id, user_name, count(stuff_id)
from tbl_user left outer join tbl_stuff on stuff_user_id = user_id
where user_id = 5
group by user_id, user_name;
The last line is the group byclause that tells Oracle to count all rows with the same user_id and user_name combination.
最后一行是group by告诉 Oracle 对具有相同 user_id 和 user_name 组合的所有行进行计数的子句。
回答by MatBailie
One of your comments states that you don't want to include all the field present in a GROUP BYclause.
您的评论之一指出您不想包含GROUP BY子句中存在的所有字段。
@Arion posted a correlated-sub-query re-factor that gives the same values.
@Arion 发布了一个提供相同值的相关子查询重构。
The following query uses a standard (un-correlated) sub-query (inline-view)instead. This is because using this inline-viewstructure canoften perform correlated-sub-query equivilents. But, also, because I find them easier to maintain.
以下查询使用标准（不相关）子查询（内联视图）代替。这是因为使用这种内联视图结构通常可以执行相关子查询等价物。但是，也因为我发现它们更容易维护。
WITH
stuff_count
AS
(
SELECT
stuff_user_id AS user_id,
COUNT(*) AS val
FROM
tbl_stuff
GROUP BY
stuff_user_id
)
SELECT
tbl_user.user_id,
tbl_user.user_name,
stuff_count.val
FROM
tbl_user
LEFT JOIN
stuff_count
ON stuff_count.user_id = tbl_user.user_id
WHERE
tbl_user.user_id = 5;
NOTE:When the plan is generated, it only runs the sub-query for the user_id's necessary, notthe whole table ;)
注意：生成计划时，它只运行 user_id 必需的子查询，而不是整个表；)
