MySQL 对另一列 = 值的行进行计数

Question

我有一个 While 循环，显示每行中 p.songid （发布曲目）的 COUNT 。我实际需要的是根据 trackDeleted=0 而不是所有行来获取计数。

 SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle, s.timeSubmitted, s.userid, s.insWanted, s.bounty, COUNT(p.songid)
 FROM  songs s
 LEFT JOIN users u ON u.id = s.userid
 LEFT JOIN posttracks p ON s.songid = p.songid
 WHERE paid=1 AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
 GROUP BY s.timeSubmitted ASC
 LIMIT 25

我可以更新上述查询还是必须在单独的查询中？

Answer 1

我不确定这在 mySql 中是否有效，但在 SQL 中，您可以在连接期间进行过滤，如下所示：

SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle, s.timeSubmitted, s.userid, s.insWanted, s.bounty, COUNT(p.songid)
FROM  songs s
LEFT JOIN users u ON u.id = s.userid
LEFT JOIN posttracks p ON s.songid = p.songid and trackDeleted=0
WHERE paid=1 AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
GROUP BY s.timeSubmitted ASC
LIMIT 25

Answer 2

这取决于 trackDeleted 列在哪个表中。

如果它存在于后轨中，那就很简单

SELECT COUNT(p.songid) FROM posttracks p where p.trackDeleted=0

Answer 3

将 trackDeleted = 0 添加到 WHERE 子句中：

...
WHERE paid=1 AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
    AND trackDeleted=0
 --  ^^^ add this
GROUP BY s.timeSubmitted ASC
...

Answer 4

试试这个...
您必须按 p.songid 分组。

 SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle,
 s.timeSubmitted, s.userid, s.insWanted, s.bounty, COUNT(p.songid) FROM
 songs s LEFT JOIN users u ON u.id = s.userid LEFT JOIN posttracks p ON
 s.songid = p.songid WHERE paid=1 AND s.timeSubmitted >= ( CURDATE() -
 INTERVAL 60 DAY ) GROUP BY s.timeSubmitted ASC,p.songid LIMIT 25

Answer 5

如果您要计算歌曲 ID，则必须将其自行分组，请尝试这个。

 SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle, s.timeSubmitted, s.userid, s.insWanted, s.bounty, COUNT(p.songid)
   FROM  songs s
   LEFT JOIN users u
     ON u.id = s.userid
   LEFT JOIN posttracks p
     ON s.songid = p.songid
  WHERE paid=1 
    AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
    AND trackDeleted=0
  GROUP BY p.songid
  ORDER BY s.timeSubmitted
  LIMIT 25;

Answer 6

将测试放入连接条件中：

LEFT JOIN posttracks p
    ON s.songid = p.songid
    AND trackDeleted = 0

或者将 COUNT(p.songid) 更改为 SUM：

SUM(CASE WHEN (p.songid IS NOT NULL AND trackDeleted = 0) THEN 1 ELSE 0 END)

Answer 7

以防万一有人像我一样偶然发现这一点，正确的答案可能是这样的：

SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle, s.timeSubmitted, s.userid, s.insWanted, s.bounty, COUNT(IF(p.trackDeleted=0, 1, NULL)) as not_deleted_count
     FROM  songs s
     LEFT JOIN users u
     ON u.id = s.userid
     LEFT JOIN posttracks p
     ON s.songid = p.songid
     WHERE paid=1 AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
     GROUP BY s.timeSubmitted ASC
     LIMIT 25

关键是 COUNT(IF(p.trackDeleted=0, 1, NULL)) as not_deleted_count 看起来分组中的所有曲目，并且只计算那些未删除的曲目。

这里的所有其他答案都错误地认为OP只想选择未删除的曲目，而他想要的是计算每个分组内未删除的曲目数量。

Answer 8

试试这个。

DECLARE @trackCount int,
SELECT u.username, u.id, u.score, s.genre, s.songid, s.songTitle, s.timeSubmitted, s.userid, s.insWanted, s.bounty,
 FROM songs s 
 LEFT JOIN users u ON u.id = s.userid 
 LEFT JOIN posttracks p ON s.songid = p.songid 
 WHERE paid=1 
 AND s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY)
 GROUP BY s.timeSubmitted ASC
 LIMIT 25

 SELECT COUNT(p.songid) INTO @TrackCount
 FROM posttracks p
 WHERE p.trackDeleted = 0

尝试将其添加到您的代码中。如果您需要显示有多少曲目有 trackDeleted = 0，只需访问局部变量 @trackcount 即可。