php - SQL 计数大小和性别

Question

我想计算衬衫尺码，每个尺码必须显示不同的性别

这是我想看到的输出

Size Qty Male Female 

S    20   15     5
M    30   20    10

这是我的一些 SQL 语言和 PHP 代码，但这只是查询每种大小的数量。

$stmt = $con->prepare("SELECT ssize,COUNT(*) FROM students GROUP BY ssize");

$stmt->execute();

<?php while($rs = $stmt->fetch()) { ?>

    <?=$rs['ssize']." ".number_format($rs["COUNT(*)"])?> 

<?php } ?>

Answer 1

您可以使用条件聚合来做到这一点:

SELECT s.ssize, COUNT(*),
       SUM(s.gender = 'M') as Male, SUM(s.gender = 'F') as Female
FROM students s
GROUP BY s.ssize;