【mysql】返回count(*)总数为0的值
在做mysql题的时候遇到了比较棘手的问题,花费了挺长时间才解决(本人小白),
难点应该就是这个:返回count(*)是0的值(原题地址:https://leetcode-cn.com/problems/trips-and-users/submissions/)。
太多知识点不会了,做的时候很掉头发!
在网上查了资料,一步一步解决,最终Sql如下(用Navicat美化一哈):
SELECT DISTINCT
b.request_at "Day",
ifnull(round(cnt / total, 2), 0) "Cancellation Rate"
FROM
temp_trips b
LEFT JOIN (
SELECT
count(*) cnt,
request_at
FROM
trips
WHERE
client_id NOT IN (
SELECT
users_id
FROM
users
WHERE
banned = 'Yes'
)
AND STATUS != "completed"
GROUP BY
request_at
) a ON a.request_at = b.request_at
LEFT JOIN (
SELECT
request_at,
count(*) total
FROM
trips
WHERE
client_id NOT IN (
SELECT
users_id
FROM
users
WHERE
banned = 'Yes'
)
GROUP BY
request_at
) c ON c.request_at = a.request_at
WHERE
b.request_at BETWEEN "2013-10-01"
AND "2013-10-03"
根据题目要求还要加上时间判断才是正确答案,我很纠结这个between的位置,要是有哪个明白人老哥能指点指点请别客气哈!
因为MySQL里没有nvl这个函数,所以这个题写解法跟Oracle的可能就不太一样,我也差了Oracle中显示总数为0的方法。
看到一个老哥的文章里面有如下代码:
select
tba1.id,
nvl(tmptb.cnt,0)
from tba as tba1
left join(select nd.id
count(1) cnt
from tba nd
group by nd.id) tmptb
on tmptb.id = tba1.id
转载至:https://blog.csdn.net/Tuanzidarendelaopo/article/details/64922368(老哥如果不愿意被转载的话告诉我一下!)
试了一下这个方法:
想了半天,问了同事,最后突然想到distinct(怪自己太傻)
select
distinct nvl(b.cnt,0),
a.jd
from
table_1 a
left join (
select
jd,count(*) cnt
from
table_1
where
publish in(0)
group by
jd
) b on a.jd = b.jd
出来的结果:
有一条返回0的结果了!!!