select medicode,mediname,unit,unitprice,rq,count(*) as sl
from (
select d.medicode,d.mediname,c.unit,c.unitprice,to_char(a.creadate,'yyyy-mm') as rq
from mzsf_clininvoinfo a, mzsf_clinreciinfo_jzdj b, mzsf_clinreciinfo_jzmx c, dictmedi d
where a.sfid = b.sfid and a.forgid = b.forgid and b.autonumb = c.autonumb and b.forgid = c.forgid and c.mediid = d.mediid and c.forgid = d.forgid and a.forgid = 266272 and a.creadate >=date'2017-08-01' and a.creadate <date'2018-05-01' and a.status = 1 union all select t3.medicode,t3.mediname,t2.ambunit as unit,t2.unitprice,to_char(t2.date3,'yyyy-mm') as rq from patinfo_cy t1, hosp_detail_price_cy t2, dictmedi t3 where t1.hospid = t2.hospid and t1.forgid = t2.forgid and t2.mediid = t3.mediid and t2.forgid = t3.forgid and t2.isprice = 1 and t2.isconfirm =1 and t3.item = 1 and t1.forgid =266272 and t2.date3 >=date'2017-08-01' and t2.date3 < date'2018-05-01') x group by medicode,mediname,unit,unitprice,rq order by rq 这个语句查询结果是单个收费项目在时间段内出现的次数,怎么改成统计时间段内收费项目的收费次数。就比如一个项目出现了,数量是2,上面语句就查出来1求解!
from (
select d.medicode,d.mediname,c.unit,c.unitprice,to_char(a.creadate,'yyyy-mm') as rq
from mzsf_clininvoinfo a, mzsf_clinreciinfo_jzdj b, mzsf_clinreciinfo_jzmx c, dictmedi d
where a.sfid = b.sfid and a.forgid = b.forgid and b.autonumb = c.autonumb and b.forgid = c.forgid and c.mediid = d.mediid and c.forgid = d.forgid and a.forgid = 266272 and a.creadate >=date'2017-08-01' and a.creadate <date'2018-05-01' and a.status = 1 union all select t3.medicode,t3.mediname,t2.ambunit as unit,t2.unitprice,to_char(t2.date3,'yyyy-mm') as rq from patinfo_cy t1, hosp_detail_price_cy t2, dictmedi t3 where t1.hospid = t2.hospid and t1.forgid = t2.forgid and t2.mediid = t3.mediid and t2.forgid = t3.forgid and t2.isprice = 1 and t2.isconfirm =1 and t3.item = 1 and t1.forgid =266272 and t2.date3 >=date'2017-08-01' and t2.date3 < date'2018-05-01') x group by medicode,mediname,unit,unitprice,rq order by rq 这个语句查询结果是单个收费项目在时间段内出现的次数,怎么改成统计时间段内收费项目的收费次数。就比如一个项目出现了,数量是2,上面语句就查出来1求解!
解决方案 »
- oracle 11g myeclipse 10
- 关于联机日志存放路径的疑问
- c 语言调用Oracle存储过程(需要获取到存储过程中的out 变量),跪求大神帮助,oci得欧
- 请教高手解决sqlldr问题
- 在插入一条记录前检查记录的值,不符合条件,则不让插入该记录。
- 要查询数据库中某表的某列,要查询的表和列都是从前台选择,请问PL/SQL怎么写啊.
- 为啥我用了create 命令却在数据库中看不到表
- 请问:有这个一个定义 long long GetLongLong(short index),为何前面有两个LONG ,什么意思。
- 数据库连接问题。windows+sqlplus连接unix+oralce
- 新手学习中,是不是配置文件错了
- 如何进行数据库优化
- oracle9i以上就支持自动检测死锁,但是我在plsql中可以用语句查询到死锁,alert日志中没有ORA-00060的日志,详情见正文
,mediname
,unit
,unitprice
,rq
,COUNT(*) AS sl
,forgid
FROM (SELECT d.medicode
,d.mediname
,c.unit
,c.unitprice
,to_char(a.creadate, 'yyyy-mm') AS rq
,a.forgid
FROM mzsf_clininvoinfo a
,mzsf_clinreciinfo_jzdj b
,mzsf_clinreciinfo_jzmx c
,dictmedi d
WHERE a.sfid = b.sfid
AND a.forgid = b.forgid
AND b.autonumb = c.autonumb
AND b.forgid = c.forgid
AND c.mediid = d.mediid
AND c.forgid = d.forgid
-- AND a.forgid = 266272
AND a.creadate >= DATE '2017-08-01'
AND a.creadate < DATE '2018-05-01'
AND a.status = 1
UNION ALL
SELECT t3.medicode
,t3.mediname
,t2.ambunit AS unit
,t2.unitprice
,to_char(t2.date3, 'yyyy-mm') AS rq
,t1.forgid
FROM patinfo_cy t1
,hosp_detail_price_cy t2
,dictmedi t3
WHERE t1.hospid = t2.hospid
AND t1.forgid = t2.forgid
AND t2.mediid = t3.mediid
AND t2.forgid = t3.forgid
AND t2.isprice = 1
AND t2.isconfirm = 1
AND t3.item = 1
-- AND t1.forgid = 266272
AND t2.date3 >= DATE '2017-08-01'
AND t2.date3 < DATE '2018-05-01') x
GROUP BY medicode
,mediname
,unit
,unitprice
,rq
,forgid
ORDER BY rq