如何在单个查询中编写具有多个where条件的多个select语句 - How to write multiple select statements with multiple where condition in single query?

How to write multiple select statements with multiple where condition in single query?

我正在用sql编写php，我在数据库中有一个teachers_rating表，我想获得评级，比如有多少成员给出5评级，像4，像3，像这样…我用过一次sql查询检查。

    SELECT COUNT( teachers_rating.rating ) AS fivestar
    FROM teachers_rating
    WHERE rating = '5' and id='23'
    UNION
    SELECT COUNT( teachers_rating.rating ) AS fourstar
    FROM teachers_rating
    WHERE rating = '4'and id='23'
    UNION
    SELECT COUNT( teachers_rating.rating ) AS threestar
    FROM teachers_rating
    WHERE rating = '3'and id='23'
like that its running but out output is
fivestar    
18
14
20
but iwant this type of structure
fivestar    fourstar threestar
18           14          20

如果有人知道，请帮助我。

这叫做条件聚合:

SELECT COUNT(CASE WHEN t.rating =5 THEN 1 END) as five,
       COUNT(CASE WHEN t.rating =4 THEN 1 END) as four,
       COUNT(CASE WHEN t.rating =3 THEN 1 END) as three
FROM teachers_rating t
WHERE t.id = '23'

如果你希望所有IDs都是动态的:

SELECT t.id,
       COUNT(CASE WHEN t.rating =5 THEN 1 END) as five,
       COUNT(CASE WHEN t.rating =4 THEN 1 END) as four,
       COUNT(CASE WHEN t.rating =3 THEN 1 END) as three
FROM teachers_rating t
GROUP BY t.id

试试这个

Select SUM(if(rating = '5',1,0) as fivestar,SUM(if(rating = '4',1,0) as fourstar,SUM(if(rating = '3',1,0) as threestar FROM teachers_rating where id='23'

最简单的形式是:

SELECT (SELECT…)作为五分之一，(SELECT…)作为四星，(SELECT…)作为threestar