使用计数子查询、内部联接和组进行查询 [英] query with count subquery, inner join and group

问题描述

我绝对是 SQL 的菜鸟，我一直在拼命在 Postgresql 中编写一个具有以下表结构的复杂查询:

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:

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.

使用 group-by 的简单查询将解决问题，但将其显示在不同的行中:

A simple query with group-by will solve the problem but display it in different rows:

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
  )

这篇关于使用计数子查询、内部联接和组进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！