sql - 带count子查询，内部联接和组的查询

我绝对是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,
)

查询的目的是获取每个用户的报告类型数量，并将其显示在一列中。有三种不同类型的报告。

使用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

最佳答案

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
  )

关于sql - 带count子查询，内部联接和组的查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5860295/

Reports

sql - 带count子查询，内部联接和组的查询