如图我的语句是这个select section,count(*)
from (
select case
when blfy like '%aaa%'
then 'aaa'
when blfy like '%bbb%'
then 'bbb'
when blfy like '%ccc%'
then 'ccc'
end section
from inpatient_info )
group by section
结果 就显示出a:5 然后就没有了
from (
select case
when blfy like '%aaa%'
then 'aaa'
when blfy like '%bbb%'
then 'bbb'
when blfy like '%ccc%'
then 'ccc'
end section
from inpatient_info )
group by section
结果 就显示出a:5 然后就没有了
from (
select case
when blfy like 'aaa'
then 'aaa'
when blfy like 'bbb'
then 'bbb'
when blfy like 'ccc'
then 'ccc'
end section
from inpatient_info )
group by section
这样也不行……
union
select 'bbb', count(1) from inpatient_info where blfy like 'bbb'
union
select 'ccc', count(1) from inpatient_info where blfy like 'ccc'
SELECT SUM(count_aaa) aaa, SUM(count_bbb) bbb, SUM(count_ccc) ccc
FROM (SELECT DECODE(INSTR(BLFY, 'aaa'), 0, 0, 1) count_aaa,
DECODE(INSTR(BLFY, 'bbb'), 0, 0, 1) count_bbb,
DECODE(INSTR(BLFY, 'ccc'), 0, 0, 1) count_ccc
FROM INPATIENT_INFO)
from (
(select case when blfy like '%aaa%' then 1 else 0 end data, 'a' as section from inpatient_info)
union all
(select case when blfy like '%bbb%' then 1 else 0 end data, 'b' as section from inpatient_info)
union all
(select case when blfy like '%ccc%' then 1 else 0 end data, 'c' as section from inpatient_info)
) A
group by section
就是这样子了!
union
显示的结果是这个实际上 都应该有数值
CREATE TABLE inpatient_info(BLFY VARCHAR2(100), BLFY_SUM NUMBER)INSERT INTO inpatient_info VALUES ('aaa;bbb;ccc', 3);
INSERT INTO inpatient_info VALUES ('aaa;bbb;ccc', 3);
INSERT INTO inpatient_info VALUES ('bbb;ccc', 2);
INSERT INTO inpatient_info VALUES ('aaa', 1);
INSERT INTO inpatient_info VALUES ('aaa;bbb', 2);
INSERT INTO inpatient_info VALUES ('aaa;ccc', 2);select * from inpatient_infoselect 'aaa', count(1) from inpatient_info where blfy like '%aaa%'
union
select 'bbb', count(1) from inpatient_info where blfy like '%bbb%'
union
select 'ccc', count(1) from inpatient_info where blfy like '%ccc%'
结果:
SELECT SUM(count_aaa) aaa, SUM(count_bbb) bbb, SUM(count_ccc) ccc
FROM (SELECT DECODE(INSTR(BLFY, 'aaa'), 0, 0, 1) count_aaa,
DECODE(INSTR(BLFY, 'bbb'), 0, 0, 1) count_bbb,
DECODE(INSTR(BLFY, 'ccc'), 0, 0, 1) count_ccc
FROM INPATIENT_INFO)上面SQL的结果: