SQL 使用计数子查询、内连接和组查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5860295/
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
query with count subquery, inner join and group
提问by Roberto Betancourt
I'm definitely a noob with SQL, I've been busting my head to write a complex query with the following table structure in Postgresql:
我绝对是 SQL 的菜鸟，我一直在拼命用 Postgresql 中的以下表结构编写一个复杂的查询：
CREATE TABLE reports
(
reportid character varying(20) NOT NULL,
userid integer NOT NULL,
reporttype character varying(40) NOT NULL,
)
CREATE TABLE users
(
userid serial NOT NULL,
username character varying(20) NOT NULL,
)
The objective of the query is to fetch the amount of report types per user and display it in one column. There are three different types of reports.
查询的目标是获取每个用户的报告类型数量并将其显示在一列中。共有三种不同类型的报告。
A simple query with group-by will solve the problem but display it in different rows:
使用 group-by 的简单查询将解决问题，但将其显示在不同的行中：
select count(*) as Amount,
u.username,
r.reporttype
from reports r,
users u
where r.userid=u.userid
group by u.username,r.reporttype
order by u.username
回答by Magnus
SELECT
username,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid && reports.reporttype = 'Type1'
) As Type1,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid && reports.reporttype = 'Type2'
) As Type2,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid && reports.reporttype = 'Type3'
) As Type3
FROM
users
WHERE
EXISTS(
SELECT
NULL
FROM
reports
WHERE
users.userid = reports.userid
)
回答by Andriy M
SELECT
u.username,
COUNT(CASE r.reporttype WHEN 1 THEN 1 END) AS type1Qty,
COUNT(CASE r.reporttype WHEN 2 THEN 1 END) AS type2Qty,
COUNT(CASE r.reporttype WHEN 3 THEN 1 END) AS type3Qty
FROM reports r
INNER JOIN users u ON r.userid = u.userid
GROUP BY u.username
If your server's SQL dialect requires the ELSE branch to be present in CASE expressions, add ELSE NULLbefore every END.
如果您的服务器的 SQL 方言要求 CASE 表达式中存在 ELSE 分支，请ELSE NULL在每个END.
回答by ajmcp
If you're looking for the "amountof report types per user", you'll be expecting to see a number, either 1, 2 or 3 (given that there are three different types of reports) against each user. You won't be expecting the reporttype (it'll just be counted not displayed), so you don't need reporttype in either the SELECT or the GROUP BY part of the query.
如果您正在寻找“每个用户的报告类型数量”，您将希望看到针对每个用户的数字，1、2 或 3（假设存在三种不同类型的报告）。您不会期望报告类型（它只会被计算而不显示），因此您不需要在查询的 SELECT 或 GROUP BY 部分中使用 reporttype。
Instead, use COUNT(DISTINCT r.reporttype) to count the number of different reporttypes that are used by each user.
相反，使用 COUNT(DISTINCT r.reporttype) 来计算每个用户使用的不同报告类型的数量。
SELECT
COUNT(DISTINCT r.reporttype) as Amount
,u.username
FROM users u
INNER JOIN reports r
ON r.userid=u.userid
GROUP BY
u.username
ORDER BY u.username
