Saat ini saya memiliki 2 tabel:

CREATE TABLE table_1 
(
    id integer PRIMARY KEY,
    sync_id integer NOT NULL,
    color VARCHAR (50) NOT NULL
);

CREATE TABLE syncs 
(
    id integer PRIMARY KEY,
    some_column_name VARCHAR (100)
);

Dan 3 pertanyaan serupa:

SELECT
  sc.id,
  COALESCE(t1.sync_id, 0::bigint) as t1_count_red
FROM syncs sc
LEFT JOIN (
  SELECT count(t.id), t.sync_id as sync_id
  FROM table_1 t
  WHERE t.color = 'red'
  GROUP BY sync_id
) t1 ON t1.sync_id = sc.id;


SELECT
  sc.id,
  COALESCE(t1.sync_id, 0::bigint) as t1_count_green
FROM syncs sc
LEFT JOIN (
  SELECT count(t.id), t.sync_id as sync_id
  FROM table_1 t
  WHERE t.color = 'green'
  GROUP BY sync_id
) t1 ON t1.sync_id = sc.id;

SELECT
  sc.id,
  COALESCE(t1.sync_id, 0::bigint) as t1_total_count
FROM syncs sc
LEFT JOIN (
  SELECT count(t.id), t.sync_id as sync_id
  FROM table_1 t
  GROUP BY sync_id
) t1 ON t1.sync_id = sc.id;

Seperti yang kita lihat, kueri ini sangat mirip - perbedaan utamanya adalah dalam kondisi WHERE dan kueri terakhir tanpanya, tetapi sekarang saya memiliki 3 kueri terpisah. Apakah mungkin untuk mendapatkan hasil di bawah ini?

Saya sudah mencoba mengelompokkannya berdasarkan subquery tetapi saya mendapatkan kesalahan "subquery mengembalikan lebih dari 1 baris"

+---------+-----------+-------------+-------------+
| sync_id | count_red | count_green | total_count |
+---------+-----------+-------------+-------------+
|   ...   |    ...    |     ...     |     ...     |
+---------+-----------+-------------+-------------+

SQLFiddle: http://sqlfiddle.com/#!17/b297c/2

0
Damian 13 Agustus 2019, 00:13

1 menjawab

Jawaban Terbaik

Gunakan agregasi bersyarat, yang di Postgres paling baik dilakukan dengan konstruksi FILTER:

SELECT sc.id, t.count_red, t.count_green, t.count_total
FROM syncs sc LEFT JOIN
     (SELECT t.sync_id as sync_id,
             COUNT(*) FILTER (WHERE t.color = 'red') as count_red,
             COUNT(*) FILTER (WHERE t.color = 'green') as count_green,
             COUNT(*) as count_total
      FROM table_1 t  
      GROUP BY sync_id
     ) t
     ON t.sync_id = sc.id;
1
Gordon Linoff 12 Agustus 2019, 21:16