如下列语句。是按月分组查询的。
意思即是1-6月的人数与员工名称的分组。--挂号人次-退号人次
select sum(mzl),ygxm from(
select count(*) as mzl,gy_ygdm.ygxm as ygxm from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01' and ms_ghmx.jlsj <= date'2010-06-01' group by gy_ygdm.ygxm
union all
select -count(*) as mzl,gy_ygdm.ygxm as ygxm from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm and ms_ghmx.sbxh = ms_thmx.sbxh and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01' and ms_thmx.thrq <= date'2010-06-01' group by gy_ygdm.ygxm)
group by ygxm;问题:如何更详细点。按每日分组显示?
如果实在难度太大,先将一个月的数据,按日分组显示也行。
现在在座各位了。
意思即是1-6月的人数与员工名称的分组。--挂号人次-退号人次
select sum(mzl),ygxm from(
select count(*) as mzl,gy_ygdm.ygxm as ygxm from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01' and ms_ghmx.jlsj <= date'2010-06-01' group by gy_ygdm.ygxm
union all
select -count(*) as mzl,gy_ygdm.ygxm as ygxm from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm and ms_ghmx.sbxh = ms_thmx.sbxh and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01' and ms_thmx.thrq <= date'2010-06-01' group by gy_ygdm.ygxm)
group by ygxm;问题:如何更详细点。按每日分组显示?
如果实在难度太大,先将一个月的数据,按日分组显示也行。
现在在座各位了。
-- 为了方便查看,先整理了一下楼主发的代码
select
sum(mzl)
,ygxm
from(
select count(*) as mzl
,gy_ygdm.ygxm as ygxm
from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01'
and ms_ghmx.jlsj <= date'2010-06-01'
group by gy_ygdm.ygxm
union all
select -count(*) as mzl
,gy_ygdm.ygxm as ygxm
from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm
and ms_ghmx.sbxh = ms_thmx.sbxh
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01'
and ms_thmx.thrq <= date'2010-06-01'
group by gy_ygdm.ygxm
)
group by ygxm;-- 至于按天分组,只要稍作修改即可,不只能能否满足楼主你的要求?
select
sum(mzl)
,ygxm
,deal_date -- 增加
from(
select count(*) as mzl
,gy_ygdm.ygxm as ygxm
,ms_ghmx.jlsj as deal_date -- 增加
from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01'
and ms_ghmx.jlsj <= date'2010-06-01'
group by gy_ygdm.ygxm
union all
select -count(*) as mzl
,gy_ygdm.ygxm as ygxm
,ms_thmx.thrq as deal_date -- 增加
from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm
and ms_ghmx.sbxh = ms_thmx.sbxh
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01'
and ms_thmx.thrq <= date'2010-06-01'
group by gy_ygdm.ygxm
)
group by ygxm
,deal_date -- 增加
;
-- 上面的代码有漏,重发一遍
select
sum(mzl)
,ygxm
,deal_date -- 增加
from(
select count(*) as mzl
,gy_ygdm.ygxm as ygxm
,ms_ghmx.jlsj as deal_date -- 增加
from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01'
and ms_ghmx.jlsj <= date'2010-06-01'
group by gy_ygdm.ygxm
,ms_ghmx.jlsj -- 增加
union all
select -count(*) as mzl
,gy_ygdm.ygxm as ygxm
,ms_thmx.thrq as deal_date -- 增加
from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm
and ms_ghmx.sbxh = ms_thmx.sbxh
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01'
and ms_thmx.thrq <= date'2010-06-01'
group by gy_ygdm.ygxm
,ms_thmx.thrq -- 增加
)
group by ygxm
,deal_date -- 增加
;
sum(mzl) YGXM DEAL_DATE
1 刘志文 2010-03-07 10:45:54
1 刘涛 2010-05-04 09:20:07
1 刘涛 2010-04-18 08:44:39
1 刘涛 2010-04-14 08:19:45
1 唐代彬 2010-05-18 11:57:36
1 唐代彬 2010-05-18 09:33:46
1 唐代彬 2010-05-13 13:13:20
1 唐代彬 2010-05-13 12:10:06DEAL_DATE的结果为年-月-天 小时:分:秒。我想表达的目的是。这样。可能要处理下或者什么请帮帮忙。
sum(mzl) YGXM DEAL_DATE
1 刘志文 2010-03-07
1 刘涛 2010-05-04
1 刘涛 2010-04-18
1 刘涛 2010-04-14
2 唐代彬 2010-05-18
2 唐代彬 2010-05-13以天为单位。统计每个人,每天的mzl
先谢谢dyw31415926了。
select
sum(mzl)
,ygxm
,deal_date -- 增加
from(
select count(*) as mzl
,gy_ygdm.ygxm as ygxm
,to_date(to_char(ms_ghmx.jlsj,'yyyymmdd'),'yyyymmdd') as deal_date -- 增加(改)
from ms_ghmx,gy_ygdm,ms_ghks
where ms_ghmx.ksdm = ms_ghks.ksdm
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_ghmx.jlsj >= date'2010-01-01'
and ms_ghmx.jlsj <= date'2010-06-01'
group by gy_ygdm.ygxm
,to_date(to_char(ms_ghmx.jlsj,'yyyymmdd'),'yyyymmdd') -- -- 增加(改)
union all
select -count(*) as mzl
,gy_ygdm.ygxm as ygxm
,to_date(to_char(ms_thmx.thrq,'yyyymmdd'),'yyyymmdd') as deal_date -- -- 增加(改)
from ms_thmx,gy_ygdm,ms_ghks,ms_ghmx
where ms_ghmx.ksdm = ms_ghks.ksdm
and ms_ghmx.sbxh = ms_thmx.sbxh
and gy_ygdm.ygdm = ms_ghmx.jzys
and ms_ghks.ksdm = (select ksdm from ms_ghks where ksmc = '急诊医学科')
and ms_thmx.thrq >= date'2010-01-01'
and ms_thmx.thrq <= date'2010-06-01'
group by gy_ygdm.ygxm
,to_date(to_char(ms_thmx.thrq,'yyyymmdd'),'yyyymmdd') -- -- 增加(改)
)
group by ygxm
,deal_date -- 增加
;
deal_date 如果要指定格式的话,
可以使用to_char函数
例子: to_char(deal_date,'yyyymmdd') -- like 20100709如果要排序列,在最后面加上
order by 要排序的字段即可
例子: order by ygxm,deal_date