MYSQL 计数 - 如果为空，则返回 0

Question

这是我的查询：

select COUNT(*) as total, COUNT(distinct user) as unique 
FROM table 
WHERE uristem in ('/example/', '/example/', '/example/', '/example/') 
      and time > DATE_SUB(NOW(), INTERVAL 24 HOUR)' 
group by uristem;

应该看起来像这样

100 50
25  50
0   0
100 35

但是如果没有计数，它看起来像这样（丢失）

100 50
40  50
100 35

如何填写 0？

Answer 1

试试这个（未经测试）：

select COUNT(t.uristem) as total
, COUNT(distinct t.user) as unique 
FROM 
    (
        select uristem, user
        from table
        where time > DATE_SUB(NOW(), INTERVAL 24 HOUR) 
    ) t
right outer join 
(
    select '/example/' x
    union select '/example/'
    union select '/example/'
    union select '/example/'
) y
on t.uristem = y.x
group by y.x;

Answer 2

如果你没有一个带有 uri 词干的表，那么像

Select
  u.uristem,
  count(t.uristem),
  count(distinct t.user) as unique
From (
    Select '/example1/' As uristem
    Union All Select '/example2/'
    Union All Select '/example3/'
    Union All Select '/example4/'
  ) u
    Left Outer Join
  table t
    On u.uristem = t.uristem And
    t.time > Date_Sub(Now(), Interval 24 Hour)
 Group By
   u.uristem

应该这样做