-- 这个必使用外连接的方式,机器可不知道你要显示条件中那三个数值。 with mt as ( select 1005001 c from dual union all select 1005002 from dual union all select 1005003 from dual ) select mt.c , count(*) from mt left join tb_smp_sms_history_201409 t on business_id = mt.c where latn_id=919 group by mt.c
union all ...........
有没有类似的编码表:business_id 用business_id的编码表和你的查询语句左连接:select a.business_id,nvl(b.b1,0) 合计 from 标码表 a left join (select count(*) b1,business_id from tb_smp_sms_history_201409 t where latn_id=919 and business_id in(1005001,1005002,1005003) group by business_id ) b on a.business_id=b.business_id group by a.business_id ;
设计表的时候 应该是有business_id 这个字段的主表吧,先 select主表 然后 leap join tb_smp_sms_history_201409 group by business_id
何必纠结,复制出来 execl加上1005003 为 0 。
你的(latn_id=919 and business_id=1005003)=false吧 非要显示的话select a.business_id, nvi(b.cnt,0) as cnt from (select business_id from tb_smp_sms_history_201409 t where business_id in (1005001, 1005002, 1005003)) a, (select count(*) as cnt, business_id from tb_smp_sms_history_201409 t where latn_id = 919 and business_id in (1005001, 1005002, 1005003) group by business_id) b where a.business_id = b.business_id不过其实从统计角度看,这个不存在的值根本就没意义
上面说的 union all 的方法不错。 下面这样把 union all 放面,或者我们把默认的 0 与 count() /group 的结果 union all一起再 sum 一次。 select sum(cnt) as cnt, business_id from ( select 0 as cnt, 1005001 as business_id from dual union all select 0 as cnt, 1005002 as business_id from dual union all select 0 as cnt, 1005003 as business_id from dual union all select 1 as cnt,business_id from tb_smp_sms_history_201409 t where latn_id=919 and business_id in(1005001,1005002,1005003) ) a group by business_id
其实这个问题后面是对 SQL 语句的执行次序的理解: 一条普通的 select 语句, 首先是从 from 部分开始执行的, 然后是可选的 join 部分 然后是 where 部分最早执行的肯定是 SQL 中的 "选择运算”, select 后面的是最后执行,它是投影运算。选择运算没有命中的记录,不会被投影运算处理的。 因此在 where 条件没有命中的记录,不会被 group by 处理,自然也就没有 0 这个占位子家伙了。 这有矛盾吧 ,既然是group 和count 只有 有记录的才会进行groupby 所以,count(*) 最少为1 啊,怎么可能会有0的记录楼上说的很对,
SELECT t.business_id, IFNULL(t.num, 0) FROM ( (SELECT DISTINCT business_id FROM tb_smp_sms_history_201409 WHERE business_id IN (1005001, 1005002, 1005003)) a LEFT JOIN (SELECT COUNT(*) AS num, business_id FROM tb_smp_sms_history_201409 t WHERE latn_id = 919 AND business_id IN (1005001, 1005002, 1005003) GROUP BY business_id) b ON a.business_id = b.business_id ) t
通过这个找到需要汇总的记录,之后就和这个条件没有关系了
表中数据 不存在 1005003的记录,group的时候自然不会出现在结果中
with mt as (
select 1005001 c from dual
union all
select 1005002 from dual
union all
select 1005003 from dual
)
select mt.c , count(*)
from mt left join tb_smp_sms_history_201409 t on business_id = mt.c
where latn_id=919
group by mt.c
用business_id的编码表和你的查询语句左连接:select a.business_id,nvl(b.b1,0) 合计
from 标码表 a left join
(select count(*) b1,business_id from tb_smp_sms_history_201409 t where latn_id=919 and business_id in(1005001,1005002,1005003) group by business_id ) b
on a.business_id=b.business_id
group by a.business_id
;
这有矛盾吧 ,既然是group 和count 只有 有记录的才会进行groupby 所以,count(*) 最少为1 啊,怎么可能会有0的记录
这有矛盾吧 ,既然是group 和count 只有 有记录的才会进行groupby 所以,count(*) 最少为1 啊,怎么可能会有0的记录
楼上说的不错,既然要group by那么没有的在分组的时候就不会被统计了。
何必纠结,复制出来 execl加上1005003 为 0 。
非要显示的话select a.business_id, nvi(b.cnt,0) as cnt
from (select business_id
from tb_smp_sms_history_201409 t
where business_id in (1005001, 1005002, 1005003)) a,
(select count(*) as cnt, business_id
from tb_smp_sms_history_201409 t
where latn_id = 919
and business_id in (1005001, 1005002, 1005003)
group by business_id) b
where a.business_id = b.business_id不过其实从统计角度看,这个不存在的值根本就没意义
这有矛盾吧 ,既然是group 和count 只有 有记录的才会进行groupby 所以,count(*) 最少为1 啊,怎么可能会有0的记录楼上说的很对,
下面这样把 union all 放面,或者我们把默认的 0 与 count() /group 的结果 union all一起再 sum 一次。
select sum(cnt) as cnt, business_id
from (
select 0 as cnt, 1005001 as business_id from dual
union all
select 0 as cnt, 1005002 as business_id from dual
union all
select 0 as cnt, 1005003 as business_id from dual
union all
select 1 as cnt,business_id
from tb_smp_sms_history_201409 t
where latn_id=919 and business_id in(1005001,1005002,1005003)
) a
group by business_id
一条普通的 select 语句,
首先是从 from 部分开始执行的,
然后是可选的 join 部分
然后是 where 部分最早执行的肯定是 SQL 中的 "选择运算”,
select 后面的是最后执行,它是投影运算。选择运算没有命中的记录,不会被投影运算处理的。 因此在 where 条件没有命中的记录,不会被 group by 处理,自然也就没有 0 这个占位子家伙了。
这有矛盾吧 ,既然是group 和count 只有 有记录的才会进行groupby 所以,count(*) 最少为1 啊,怎么可能会有0的记录楼上说的很对,
t.business_id,
IFNULL(t.num, 0)
FROM
(
(SELECT DISTINCT
business_id
FROM
tb_smp_sms_history_201409
WHERE business_id IN (1005001, 1005002, 1005003)) a
LEFT JOIN
(SELECT
COUNT(*) AS num,
business_id
FROM
tb_smp_sms_history_201409 t
WHERE latn_id = 919
AND business_id IN (1005001, 1005002, 1005003)
GROUP BY business_id) b
ON a.business_id = b.business_id
) t