select m.dict_name, a.一月, b.二月, c.三月, d.四月, e.五月, f.六月, g.七月, h.八月, i.九月, j.十月, k.十一月, l.十二月 from (select dict_code, sum(elem_value) 一月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-1', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-1', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) a,
(select dict_code, sum(elem_value) 二月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-2', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-2', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) b,
(select dict_code, sum(elem_value) 三月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-3', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-3', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) c,
(select dict_code, sum(elem_value) 四月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-4', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-4', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) d,
(select dict_code, sum(elem_value) 五月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-5', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-5', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) e,
(select dict_code, sum(elem_value) 六月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-6', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-6', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) f,
(select dict_code, sum(elem_value) 七月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-7', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-7', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) g,
(select dict_code, sum(elem_value) 八月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-8', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-8', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) h,
(select dict_code, sum(elem_value) 九月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-9', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-9', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) i,
(select dict_code, sum(elem_value) 十月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-10', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-10', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) j,
(select dict_code, sum(elem_value) 十一月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-11', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-11', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) k,
(select dict_code, sum(elem_value) 十二月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-12', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-12', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) l, (select dict_code,DICT_NAME from emr_nurseworkload_dict) m where (a.dict_code = b.dict_code) and (b.dict_code = c.dict_code) and (c.dict_code = d.dict_code) and (d.dict_code = e.dict_code) and (e.dict_code = f.dict_code) and (f.dict_code = g.dict_code) and (g.dict_code = h.dict_code) and (h.dict_code = i.dict_code) and (i.dict_code = j.dict_code) and (j.dict_code = k.dict_code) and (k.dict_code = l.dict_code) and (l.dict_code = m.dict_code)
(select dict_code, sum(elem_value) 十二月 from emr_nurseworkload_data where nurse_date between to_date(to_char(trunc(to_date('2010-12', 'YYYY-MM'), 'MM'), 'YYYY-MM-DD'), 'YYYY-MM-DD') and to_date(to_char(last_day(to_date('2010-12', 'YYYY-MM')), 'YYYY-MM-DD'), 'YYYY-MM-DD') group by dict_code) l, 感觉是按月统计,可以用GROUP BY TO_CHAR(nurse_date,'YYYYMM') 统计即可,你试试...
TO gaotianyou如果用 临时表 怎么做?? 小弟我刚接触oracle 对这些不清楚 能否 帮忙做下
TO header230因为就是要写一个 SQL 语句所以 是否还有别的方法可行呢?
select dict_code, TO_CHAR(nurse_date,'YYYYMM'),sum(elem_value) from emr_nurseworkload_data group by dict_code,TO_CHAR(nurse_date,'YYYYMM') 以上SQL 这个能得到结果?
TO java3344520有结果,但不是我要的效果 小弟要的效果是: dict_code 一月 二月 三月 四月 ..... a 1 2 1 1 .... b 1 2 2 2 ....
我知道啊,然后进行行转列 用 select dict_code, max(decode(yyyymm,'201001',elem_value,0)) as '一月', max(decode(yyyymm,'201002',elem_value,0)) as '二月', ........ max(decode(yyyymm,'201012',elem_value,0)) as '十二月' from( select dict_code, TO_CHAR(nurse_date,'YYYYMM') as yyyymm, sum(elem_value) as elem_value from emr_nurseworkload_data group by dict_code,TO_CHAR(nurse_date,'YYYYMM') ) group by dict_code
select dict_code ,sum(case when 1 月条件 then 需要加的值 end), sum(case when 2 月条件 then 需要加的值 end), sum(case when 3 月条件 then 需要加的值 end), ..... sum(case when 12 月条件 then 需要加的值 end) from table group by dict_code
max(decode(yyyymm,'201001',elem_value,0)) as '一月', 红色部分,你都写对了? 一月的201001 5月的201005
select dict_code, TO_CHAR(nurse_date,'YYYYMM') as yyyymm, sum(elem_value) as elem_value from emr_nurseworkload_data group by dict_code,TO_CHAR(nurse_date,'YYYYMM') 你把这个结果贴出来,正确的话说12条记录 ....
select dict_code, max(decode(yyyymm, '201001', elem_value, 0)) as 一月, max(decode(yyyymm, '201002', elem_value, 0)) as 二月, max(decode(yyyymm, '201003', elem_value, 0)) as 三月, max(decode(yyyymm, '201004', elem_value, 0)) as 四月, max(decode(yyyymm, '201005', elem_value, 0)) as 五月, max(decode(yyyymm, '201006', elem_value, 0)) as 六月, max(decode(yyyymm, '201007', elem_value, 0)) as 七月, max(decode(yyyymm, '201008', elem_value, 0)) as 八月, max(decode(yyyymm, '201009', elem_value, 0)) as 九月, max(decode(yyyymm, '201010', elem_value, 0)) as 十月, max(decode(yyyymm, '201011', elem_value, 0)) as 十一月, max(decode(yyyymm, '201012', elem_value, 0)) as 十二月 from (select dict_code, TO_CHAR(to_date('2010-05', 'YYYY-MM'), 'YYYYMM') as yyyymm, sum(elem_value) as elem_value from emr_nurseworkload_data group by dict_code, nurse_date) group by dict_code 出来的效果是 只统计了5月 别的 则为 0 上面的 是我的口误 SORRY
select dict_code, TO_CHAR(to_date('2010-05', 'YYYY-MM'), 'YYYYMM') as yyyymm, sum(elem_value) as elem_value from emr_nurseworkload_data group by dict_code, nurse_date 执行了一下 只有1条 统计数据 就是5月的
不明白你是意思,是只想统计5月份的么 那再最后加WHERE条件就可以啊,整体语句保持不变....select dict_code, max(decode(yyyymm, '201001', elem_value, 0)) as 一月, max(decode(yyyymm, '201002', elem_value, 0)) as 二月, max(decode(yyyymm, '201003', elem_value, 0)) as 三月, max(decode(yyyymm, '201004', elem_value, 0)) as 四月, max(decode(yyyymm, '201005', elem_value, 0)) as 五月, max(decode(yyyymm, '201006', elem_value, 0)) as 六月, max(decode(yyyymm, '201007', elem_value, 0)) as 七月, max(decode(yyyymm, '201008', elem_value, 0)) as 八月, max(decode(yyyymm, '201009', elem_value, 0)) as 九月, max(decode(yyyymm, '201010', elem_value, 0)) as 十月, max(decode(yyyymm, '201011', elem_value, 0)) as 十一月, max(decode(yyyymm, '201012', elem_value, 0)) as 十二月 from (select dict_code, TO_CHAR(nurse_date,'YYYYMM') as yyyymm, sum(elem_value) as elem_value from emr_nurseworkload_data where TO_CHAR(nurse_date,'YYYYMM')='201005' group by dict_code,TO_CHAR(nurse_date,'YYYYMM') ) group by dict_code
不是 单独的统计 5月 我想要的结果已经出来了 TO java3344520 thank you 你是正确的 结贴
-- 应该要有个年份吧?SELECT dict_code, extract(year from nurse_date) as "年份", sum(decode( extract(month from nurse_date),1,elem_value,0)) as "一月", sum(decode( extract(month from nurse_date),2,elem_value,0)) as "二月", sum(decode( extract(month from nurse_date),3,elem_value,0)) as "三月", sum(decode( extract(month from nurse_date),4,elem_value,0)) as "四月", sum(decode( extract(month from nurse_date),5,elem_value,0)) as "五月", sum(decode( extract(month from nurse_date),6,elem_value,0)) as "六月", sum(decode( extract(month from nurse_date),7,elem_value,0)) as "七月", sum(decode( extract(month from nurse_date),8,elem_value,0)) as "八月", sum(decode( extract(month from nurse_date),9,elem_value,0)) as "九月", sum(decode( extract(month from nurse_date),10,elem_value,0)) as "十月", sum(decode( extract(month from nurse_date),11,elem_value,0)) as "十一月", sum(decode( extract(month from nurse_date),12,elem_value,0)) as "十二月" FROM emr_nurseworkload_data GROUP BY dict_code, extract(year from nurse_date) ORDEER BY dict_code, extract(year from nurse_date);
select m.dict_name,
a.一月,
b.二月,
c.三月,
d.四月,
e.五月,
f.六月,
g.七月,
h.八月,
i.九月,
j.十月,
k.十一月,
l.十二月
from (select dict_code, sum(elem_value) 一月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-1', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-1', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) a,
(select dict_code, sum(elem_value) 二月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-2', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-2', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) b,
(select dict_code, sum(elem_value) 三月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-3', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-3', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) c,
(select dict_code, sum(elem_value) 四月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-4', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-4', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) d,
(select dict_code, sum(elem_value) 五月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-5', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-5', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) e,
(select dict_code, sum(elem_value) 六月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-6', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-6', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) f,
(select dict_code, sum(elem_value) 七月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-7', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-7', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) g,
(select dict_code, sum(elem_value) 八月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-8', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-8', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) h,
(select dict_code, sum(elem_value) 九月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-9', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-9', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) i,
(select dict_code, sum(elem_value) 十月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-10', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-10', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) j,
(select dict_code, sum(elem_value) 十一月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-11', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-11', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) k,
(select dict_code, sum(elem_value) 十二月
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-12', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-12', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) l,
(select dict_code,DICT_NAME from emr_nurseworkload_dict) m
where (a.dict_code = b.dict_code)
and (b.dict_code = c.dict_code)
and (c.dict_code = d.dict_code)
and (d.dict_code = e.dict_code)
and (e.dict_code = f.dict_code)
and (f.dict_code = g.dict_code)
and (g.dict_code = h.dict_code)
and (h.dict_code = i.dict_code)
and (i.dict_code = j.dict_code)
and (j.dict_code = k.dict_code)
and (k.dict_code = l.dict_code)
and (l.dict_code = m.dict_code)
from emr_nurseworkload_data
where nurse_date between
to_date(to_char(trunc(to_date('2010-12', 'YYYY-MM'), 'MM'),
'YYYY-MM-DD'),
'YYYY-MM-DD') and
to_date(to_char(last_day(to_date('2010-12', 'YYYY-MM')),
'YYYY-MM-DD'),
'YYYY-MM-DD')
group by dict_code) l,
感觉是按月统计,可以用GROUP BY TO_CHAR(nurse_date,'YYYYMM') 统计即可,你试试...
你可以试着写存储过程,
你发现没有 你的就时间不一样,
你可以 for 循环来做
拼接你的 时间字符串 ‘2010-’||YourMonth 至于是否可行,还有待测试
你可以这样写。思路已经有了,看你怎么写了,
因为是 一年当中 按月 统计
你说的方法 我试了下
是不可行的
小弟我刚接触oracle 对这些不清楚
能否 帮忙做下
from emr_nurseworkload_data
group by dict_code,TO_CHAR(nurse_date,'YYYYMM')
以上SQL 这个能得到结果?
小弟要的效果是:
dict_code 一月 二月 三月 四月 .....
a 1 2 1 1 ....
b 1 2 2 2 ....
用
select dict_code,
max(decode(yyyymm,'201001',elem_value,0)) as '一月',
max(decode(yyyymm,'201002',elem_value,0)) as '二月',
........
max(decode(yyyymm,'201012',elem_value,0)) as '十二月'
from(
select dict_code,
TO_CHAR(nurse_date,'YYYYMM') as yyyymm,
sum(elem_value) as elem_value
from emr_nurseworkload_data
group by dict_code,TO_CHAR(nurse_date,'YYYYMM')
)
group by dict_code
sum(case when 2 月条件 then 需要加的值 end),
sum(case when 3 月条件 then 需要加的值 end),
.....
sum(case when 12 月条件 then 需要加的值 end)
from table group by dict_code
先谢过你 不厌其烦的 帮忙 你最后贴的这段代码 还是不可行 他出来的效果是 这样的 比如 输入的是 2010-05
他的结果是 五月 里有数据 而且 是 2010年的数据的 总和
别的 月 则为 0
红色部分,你都写对了?
一月的201001
5月的201005
TO_CHAR(nurse_date,'YYYYMM') as yyyymm,
sum(elem_value) as elem_value
from emr_nurseworkload_data
group by dict_code,TO_CHAR(nurse_date,'YYYYMM')
你把这个结果贴出来,正确的话说12条记录
....
select dict_code,
max(decode(yyyymm, '201001', elem_value, 0)) as 一月,
max(decode(yyyymm, '201002', elem_value, 0)) as 二月,
max(decode(yyyymm, '201003', elem_value, 0)) as 三月,
max(decode(yyyymm, '201004', elem_value, 0)) as 四月,
max(decode(yyyymm, '201005', elem_value, 0)) as 五月,
max(decode(yyyymm, '201006', elem_value, 0)) as 六月,
max(decode(yyyymm, '201007', elem_value, 0)) as 七月,
max(decode(yyyymm, '201008', elem_value, 0)) as 八月,
max(decode(yyyymm, '201009', elem_value, 0)) as 九月,
max(decode(yyyymm, '201010', elem_value, 0)) as 十月,
max(decode(yyyymm, '201011', elem_value, 0)) as 十一月,
max(decode(yyyymm, '201012', elem_value, 0)) as 十二月
from (select dict_code,
TO_CHAR(to_date('2010-05', 'YYYY-MM'), 'YYYYMM') as yyyymm,
sum(elem_value) as elem_value
from emr_nurseworkload_data
group by dict_code, nurse_date)
group by dict_code
出来的效果是 只统计了5月 别的 则为 0 上面的 是我的口误 SORRY
select dict_code,
TO_CHAR(to_date('2010-05', 'YYYY-MM'), 'YYYYMM') as yyyymm,
sum(elem_value) as elem_value
from emr_nurseworkload_data
group by dict_code, nurse_date
执行了一下 只有1条 统计数据 就是5月的
那再最后加WHERE条件就可以啊,整体语句保持不变....select dict_code,
max(decode(yyyymm, '201001', elem_value, 0)) as 一月,
max(decode(yyyymm, '201002', elem_value, 0)) as 二月,
max(decode(yyyymm, '201003', elem_value, 0)) as 三月,
max(decode(yyyymm, '201004', elem_value, 0)) as 四月,
max(decode(yyyymm, '201005', elem_value, 0)) as 五月,
max(decode(yyyymm, '201006', elem_value, 0)) as 六月,
max(decode(yyyymm, '201007', elem_value, 0)) as 七月,
max(decode(yyyymm, '201008', elem_value, 0)) as 八月,
max(decode(yyyymm, '201009', elem_value, 0)) as 九月,
max(decode(yyyymm, '201010', elem_value, 0)) as 十月,
max(decode(yyyymm, '201011', elem_value, 0)) as 十一月,
max(decode(yyyymm, '201012', elem_value, 0)) as 十二月
from (select dict_code,
TO_CHAR(nurse_date,'YYYYMM') as yyyymm,
sum(elem_value) as elem_value
from emr_nurseworkload_data
where TO_CHAR(nurse_date,'YYYYMM')='201005'
group by dict_code,TO_CHAR(nurse_date,'YYYYMM')
)
group by dict_code
你是正确的 结贴
-- 应该要有个年份吧?SELECT dict_code, extract(year from nurse_date) as "年份",
sum(decode( extract(month from nurse_date),1,elem_value,0)) as "一月",
sum(decode( extract(month from nurse_date),2,elem_value,0)) as "二月",
sum(decode( extract(month from nurse_date),3,elem_value,0)) as "三月",
sum(decode( extract(month from nurse_date),4,elem_value,0)) as "四月",
sum(decode( extract(month from nurse_date),5,elem_value,0)) as "五月",
sum(decode( extract(month from nurse_date),6,elem_value,0)) as "六月",
sum(decode( extract(month from nurse_date),7,elem_value,0)) as "七月",
sum(decode( extract(month from nurse_date),8,elem_value,0)) as "八月",
sum(decode( extract(month from nurse_date),9,elem_value,0)) as "九月",
sum(decode( extract(month from nurse_date),10,elem_value,0)) as "十月",
sum(decode( extract(month from nurse_date),11,elem_value,0)) as "十一月",
sum(decode( extract(month from nurse_date),12,elem_value,0)) as "十二月"
FROM emr_nurseworkload_data
GROUP BY dict_code, extract(year from nurse_date)
ORDEER BY dict_code, extract(year from nurse_date);