mysql - sql语句中的多个where子句？

Question

我有一个简单的 sessions看起来像这样的表:

   sid  watchtime
    1   01:07:00
    2   01:07:00
    3   01:34:00
    4   01:36:00
    5   01:36:00
    6   01:59:00
    7   01:59:00
    8   00:00:00
    9   00:00:00
    10  03:42:00
    11  00:16:00
    12  00:49:00
    13  00:55:00
    14  00:00:00
    15  00:05:00
    16  00:05:00

现在，我想显示观看时间的计数 >=5和观看时间 <5 .我尝试了以下解决方案:

SELECT count(watchtime) as lessthan_five FROM sessions 
WHERE watchtime <=5 
AND count(watchtime) as morethan_five 
         from sessions WHERE watchtime >5";

我收到以下错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as morethanfive from sessions where WHERE watchtime >=5"' at line 1

我的代码哪里做错了？

Answer 1

您可以使用条件聚合来获得您想要的结果，利用 MySQL 在数字上下文中将 bool 值视为 1 或 0:

SELECT SUM(watchtime <= '00:05:00') AS lessthan_five,
       SUM(watchtime >  '00:05:00') AS morethan_five
FROM sessions

输出:

lessthan_five   morethan_five
5               11

Demo on dbfiddle