The from (
The select d.m edicode, d.m ediname c.u nit, c.u nitprice, to_char (a.c readate, 'yyyy - mm) as rq
The from mzsf_clininvoinfo a, mzsf_clinreciinfo_jzdj b, mzsf_clinreciinfo_jzmx c, dictmedi d
Where a.s fid=b.s fid and a.f orgid=b. orgid and b.a utonumb=c.a. utonumb and b. orgid=c.f orgid and c. ediid=d.m ediid and c.f orgid=d.f orgid and a.f orgid=266272 and a.c readate & gt;=the date '2017-08-01' and a.c readate & lt; The date '2018-05-01' and a.s tatus=1 union all select t3. Medicode, t3. Mediname, t2. Ambunit as unit, t2. The 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 & gt; .=the date '2017-08-01' and t2 date3 & lt; The date '2018-05-01') x group by medicode, mediname, unit, unitprice, rq order by rq this statement query result is a single charging projects within the time period, the number of occurrences of statistical period how to charge items of The Times, they have appeared such as a project, the number is 2, the above statement is found out 1 to solve!
CodePudding user response:
Don't understand, use the count or sum?CodePudding user response:
The SELECT medicode, mediname
, the unit
Unitprice,
Rq,
, and COUNT (*) AS sl
, forgid
The FROM (SELECT d.m edicode
, d.m ediname
C.u nit,
, c.u nitprice
, to_char (a.c readate, 'yyyy - mm) AS rq
, a.f orgid
The FROM mzsf_clininvoinfo a
, mzsf_clinreciinfo_jzdj b
, mzsf_clinreciinfo_jzmx c
Dictmedi d
WHERE a.s fid=b.s fid
AND a.f orgid=b. orgid
AND b.a utonumb=c.a. utonumb
AND b. orgid=c.f orgid
AND c. ediid=d.m ediid
AND c.f orgid=d.f orgid
- AND a.f orgid=266272
AND a.c readate & gt;=the DATE '2017-08-01'
AND a.c readate & lt; The DATE '2018-05-01'
AND a.s tatus=1
UNION ALL
The SELECT t3. Medicode
, t3. Mediname
, t2. Ambunit AS unit
, t2. The unitprice
, to_char (t2) date3, 'yyyy - mm) AS rq
, t1 forgid
The FROM patinfo_cy t1
, hosp_detail_price_cy t2
, dictmedi t3
WHERE a 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 & gt;=the DATE '2017-08-01'
AND t2. Date3 & lt; The DATE '2018-05-01') x
GROUP BY medicode
, mediname
, the unit
Unitprice,
Rq,
, forgid
The ORDER BY rq