mysql - 统计mysql上的每一行

Question

我有这个查询:

SELECT 
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 4) AS desejo,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 2) AS europa,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 6) AS futebol,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 8) AS jazz,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 3) AS praia,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 1) AS sp,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 7) AS tamta,
    (SELECT COUNT(*) FROM tb_pinturas WHERE c.catId= 5) AS velocidade,
    c.catNome AS nome, 
    c.catSlug AS slug, 
    c.catId AS id  
FROM aux_categoria c 
LEFT JOIN tb_pinturas p ON(c.catId = p.catId)
GROUP BY c.catid    ORDER BY c.catNome;

我只想为每个计数制作一列。我该怎么办？

Answer 1

这样的东西应该适合你:

SELECT 
    c.catNome AS nome, 
    c.catId AS id,
    count(p.catId) as the_count  
FROM aux_categoria c 
LEFT JOIN tb_pinturas p ON(c.catId = p.catId)
GROUP BY c.catNome,c.catid    
ORDER BY c.catNome