我不知道如何生成这个sql select语句,我使用PostgreSQL
数据库。
这是我的桌子:
我想添加一行按性别显示客户计数:
能做到吗?
最佳答案
您可以使用GROUP BY GROUPING SETS
(Postgresql 9.5+
):
CREATE TABLE customers(idcust INT, name VARCHAR(100), fkGender INT);
INSERT INTO customers(idcust, name, fkGender)
SELECT 1, 'bob', 3 UNION ALL SELECT 2, 'alice', 3 UNION ALL SELECT 3, 'mikel', 4;
CREATE TABLE gender(IdGender INT, gender VARCHAR(100));
INSERT INTO gender(IdGender, gender) VALUES (3, 'male'),(4,'female');
-------------------------------------------------------------------------------------
SELECT CASE WHEN idcust::text IS NULL THEN 'Total' ELSE idcust::text END AS IdCust,
CASE WHEN name IS NOT NULL THEN name ELSE COUNT(*)::text END AS name,
CASE WHEN idcust::text IS NULL THEN '' ELSE gender END AS genderName
FROM customers c
JOIN gender g
ON c.fkGender = g.idGender
GROUP BY GROUPING SETS((idcust, name, gender), (gender))
ORDER BY gender DESC, idcust;
输出:
请记住
idcust
可能是INT
所以要在该列中获得Total
,必须先转换为文本。