dongyuchen9276 2016-08-25 06:48
浏览 621
已采纳

如何在单个查询中编写具有多个where条件的多个select语句?

I am working on php with sql .I have one teachers_rating table in database I want to get ratings like how many members are give 5 rating like that only 4, like 3, like so...I used this sql query check once.

    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

If any one knows please help me.

  • 写回答

3条回答 默认 最新

  • duandiao3961 2016-08-25 06:55
    关注

    This is called conditional aggregation :

    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'
    

    If you want it dynamic for all 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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?