直接发语句了,具体情况如下:怎么让记录行数为0的 count(*)显示0.而不是不显示?SELECT CHARGE_NAME,count(*) FROM DETAIL_CHARGE where charge_name in('红光治疗','陪客费','支气管造影') group by charge_nameCHARGE_NAME NVL(COUNT(
-------------------------------------------------------------------------------- ----------
红光治疗 4804
陪客费 9690
已选择2行。
SELECT CHARGE_NAME,nvl(count(*),0) FROM DETAIL_CHARGE where charge_name in('红光治疗','陪客费','支气管造影') group by charge_nameCHARGE_NAME COUNT(*)
-------------------------------------------------------------------------------- ----------
红光治疗 4804
陪客费 9690
已选择2行。
-------------------------------------------------------------------------------- ----------
红光治疗 4804
陪客费 9690
已选择2行。
SELECT CHARGE_NAME,nvl(count(*),0) FROM DETAIL_CHARGE where charge_name in('红光治疗','陪客费','支气管造影') group by charge_nameCHARGE_NAME COUNT(*)
-------------------------------------------------------------------------------- ----------
红光治疗 4804
陪客费 9690
已选择2行。
解决方案 »
- 造成ORA-01843 无效的月份
- Convert Mysql to Oracle使用的问题
- 求SQL
- 用一个表去更新另一个表
- SELECT id,nick FROM user WHERE username='test' AND password='1' or '1'='1'; 执行顺序
- 求oracle10下禁止所有触发器的SQL
- oracle建立大量jdbc连接,并且未自动释放
- VC如何链接到Oracale数据库啊?(送分。。)(只要执行简单的insert, delete update操作)
- 如何取得一个表中的最后一个记录??
- ORACLE存储过程怪问题,100分解决再加100分,加100分
- 动态SQL的一个问题请教下
- plsql到底怎么导入dmp表呢,折磨一天了 ,求高手解答
SELECT CHARGE_NAME,count(a.CHARGE_NAME) FROM DETAIL_CHARGE a right join (
select '红光治疗' charge_name from dual union all
select '陪客费' charge_name from dual union all
select '支气管造影' charge_name from dual)b
on a.CHARGE_NAME=b.CHARGE_NAME
group by b.charge_name
select '红光治疗' charge_name from dual union all
select '陪客费' charge_name from dual union all
select '支气管造影' charge_name from dual)b
on a.CHARGE_NAME=b.CHARGE_NAME
group by b.charge_name
又没有和另一个表进行外连接
单纯的你一个表DETAIL_CHARGE 中的name 如果没有‘支气管造影’,那么你group by 就肯定不会有该数据啊!
你又要非加上支气管造影这样的统计数据
除非:SELECT CHARGE_NAME,count(*) FROM DETAIL_CHARGE where charge_name in('红光治疗','陪客费','支气管造影') group by charge_name
UNION
SELECT '支气管造影',0 FROM dual
like this:
直接用left/right join,以后统计也方便
union
SELECT CHARGE_NAME,count(*) FROM DETAIL_CHARGE where charge_name='陪客费'
union
SELECT CHARGE_NAME,count(*) FROM DETAIL_CHARGE where charge_name='支气管造影'这样吧。
我做的是动态查询,事前不知道有多少种charge_name,更不知道哪个charge_name没有记录。用NVL函数没有效果。
不知道多少种...那应该有一个所有种类的记录表吧,直接用这个表 与 业务数据关联 count group by
SELECT b.CHARGE_NAME,count(a.CHARGE_NAME) FROM DETAIL_CHARGE a right join (
select charge_name from charge_define where charge_name in ('红光治疗','陪客费','支气管造影'))b
on a.CHARGE_NAME=b.CHARGE_NAME
group by b.charge_name
-- 那么你的需求无法用SQL语句实现!只能用存储过程!
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 1,'zy' from dual union all
select 2,'zy' from dual union all
select 2,'zy' from dual union all
select 2,'zy' from dual union all
select 2,'zy' from dual union all
select 2,'zy' from dual union all
select 2,'zy' from dual;
select id,count(id) from test_tb where id=3 group by id
union all
select 0,0 from dual
1、这些数据是保存在一张表中的,就是说这张表里面有CHARGE_NAME这个字段,并且这个字段存储的值就是('红光治疗','陪客费','支气管造影')这样的中文字符,那么如果'支气管造影'这组值的count(*)为0,表示这个值的数据不存在。那么group by怎么会有值?既然没有值,就是说不需要查这个数据。
2、对于1,如果非要查'支气管造影'这个数据的count(*)值,就只能造一个count(*)=0的值;但是又有一个问题,“我做的是动态查询,事前不知道有多少种charge_name,更不知道哪个charge_name没有记录。”这句话就很矛盾了,都不知道有多少种CHARGE_NAME,就是说在这一张表中所有的CHARGE_NAME都是已经存在的,那么那些不存在的怎么还要查呢?这样的解释只有一种,还存在另一张CHARGE_NAME表。如果没有CHARGE_NAME这张表的话,那么CHARGE_NAME的值是肯定会知道有几种的,如果还不知道CHARGE_NAME的种类有几种的话,这种查询是不可能实现的(这种查询是没有必要的)
然后套用子查询就可以得到你的结果:
code如下:
select a.chargeid,a.charge_name,
nvl((select count(*) from DETAIL_CHARGE b where a.chargeid=b.chargeid),0) counts
from chargeinfo a
where a.charge_name in('红光治疗','陪客费','支气管造影')
FROM Detail_Charge a, TABLE(Str2list('红光治疗,陪客费,支气管造影', ',')) b
WHERE a.Charge_Name(+) = b.Column_Value
GROUP BY b.Column_Value
str2list是自己写的一个表函数.
FUNCTION Str2list(Str_In IN VARCHAR2,
Split_In VARCHAR2 DEFAULT ',',
Id_In VARCHAR2 DEFAULT NULL,
n_In NUMBER DEFAULT 0) RETURN t_Strlist
PIPELINED AS
v_Str VARCHAR2(4000) DEFAULT Str_In || Split_In;
v_Index NUMBER;
v_No VARCHAR2(50);
v_Id VARCHAR2(4000);
v_n NUMBER DEFAULT 1;
BEGIN
LOOP
v_Index := Instr(v_Str, Split_In);
EXIT WHEN(Nvl(v_Index, 0) = 0);
IF Id_In IS NULL THEN
v_Id := '';
ELSE
v_Id := '|' || Id_In;
END IF;
IF n_In = 0 THEN
v_No := '';
ELSE
v_No := '|' || v_n;
END IF;
PIPE ROW(TRIM(Substr(v_Str, 1, v_Index - 1)) || v_Id || v_No);
v_Str := Substr(v_Str, v_Index + 1);
v_n := v_n + 1;
END LOOP;
RETURN;
END Str2list;