sql - 从连接表中选择或计数

Question

我想统计gallery_id = 1 media，存在于media_action行，
在我使用下面的查询之前，我选择了 gallery_media 中的所有行然后循环，想知道更好吗？

// select all gallery_media rows
var dbQuery = `SELECT *  
  FROM "gallery_media" WHERE gallery_id = $1`;
var dbParams = [galleryId];
var dbQueryR = yield Promise.resolve( queryPromise(dbClient, dbQuery, dbParams) );

// loop
var total;
for (var i = 0; i < dbQueryR.rows.length; i++) {
  var mediaId = dbQueryR.rows[i].media_id;
  // select count media_action where media_id = mediaId 
  // and total++; 
  var dbQuery = `SELECT count(*)
    AS total_row_count 
    FROM "media_action" WHERE media_id = $1`;
}

// below example data final result get total = 3
// if add where type = 0 get total = 2

数据

gallery_media    
gallery_id | media_id  
1 | 1  
1 | 2  
1 | 3  

media_action     
media_id | type  
1 | 0  
3 | 0  
3 | 1 

表格

CREATE TABLE gallery_media
(
  id serial NOT NULL,
  gallery_id integer NOT NULL,
  media_id integer NOT NULL,
...

CREATE TABLE media_action
(
  id serial NOT NULL,
  media_id integer NOT NULL,
  type integer NOT NULL
...

Answer 1

看起来像一个普通的连接——有一个可选的添加 WHERE 条件:

SELECT count(*) AS total 
FROM   gallery_media g
JOIN   media_action  m USING (media_id)
WHERE  g.gallery_id = $1
-- AND m.type = 0  -- optional

Details in the manual.