SELECT语句中的MySQL分组

Question

我有一张桌子“动物”

​

​

我想弄清楚我在这两类动物中有多少种:爬行动物:螯虾、鳄鱼、蜥蜴、蛇和啮齿动物:仓鼠、豪猪、睡鼠、水豚。

当我为一个类别写作时：

SELECT Reptiles
FROM    (Select distinct COUNT(an_id) as Reptiles
         from animals
         Where an_type in ('chelonian', 'crocodilian', 'lizard', 'snake'))rep;

我得到了正确的答案：

爬行动物=9

然后我想添加第二栏的啮齿动物，所以我写道：

SELECT Reptiles, Rodents
FROM    (Select distinct COUNT(an_id) as Reptiles
        from animals
        Where an_type in ('chelonian', 'crocodilian', 'lizard', 'snake'))rep
            (Select distinct count(an_id) as Rodents
            from animals
            where an_type in ('hamster', 'porcupine', 'dormouse', 'capybara'))rod
;

当然，我会收到一个语法错误。

我只想有两栏爬行动物和啮齿类动物的数量(an_id)。有人能告诉我写这个查询的正确方法吗？

提前谢谢你，

滴滴

Answer 1

您在分隔表时忽略了逗号(，)

an_type in (“螯合”，“鳄鱼”，“蜥蜴”，“蛇”)代表， (选择不同计数(An_id)作为啮齿动物)

试着像这样

SELECT Reptiles, Rodents
FROM 
    (
     Select distinct COUNT(an_id) as Reptiles
     From animals
     Where an_type in ('chelonian', 'crocodilian', 'lizard', 'snake')
    ) rep,
    (
     Select distinct count(an_id) as Rodents
     from animals
     where an_type in ('hamster', 'porcupine', 'dormouse', 'capybara')
    )rod

Answer 2

这一行上面缺少逗号(，)吗？

(Select distinct count(an_id) as Rodents