求教,如圖,想把這個查詢結果中的BZID和日期一樣, 但價格不一樣的資料的ID找出來,該如何處理
SELECT distinct b.BZID,a.ITEMPRICE,"SUBSTR"(a.RECORDDATE,0, 7) FROM YUHUA_JICENG2_COPY a
LEFT JOIN YUHUA_KANGJUN2 b on a.CLINICID=b.CLINICID
WHERE a.ORGCODE ='320114002PDY100109'
ORDER BY b.BZID ASC NULLS LAST, "SUBSTR"(a.RECORDDATE,0, 7) ASC NULLS LAST
-----------------------------------------------------------------
BZID ITEMPRICE RECORDDATE
1336672 71.6000 2015/12
1336672 71.6000 2016/1/
1336672 62.3000 2016/10
1336672 71.6000 2016/2/
1336672 62.3000 2016/3/
1336672 71.6000 2016/3/
1336672 62.3000 2016/4/
1336672 62.3000 2016/5/
1336672 62.3000 2016/6/
1336672 62.3000 2016/7/
1336672 62.3000 2016/8/
uj5u.com熱心網友回復：
with t as
(SELECT distinct b.BZID,a.ITEMPRICE,"SUBSTR"(a.RECORDDATE,0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a
LEFT JOIN YUHUA_KANGJUN2 b on a.CLINICID=b.CLINICID
WHERE a.ORGCODE ='320114002PDY100109'
ORDER BY b.BZID ASC NULLS LAST, "SUBSTR"(a.RECORDDATE,0, 7) ASC NULLS LAST)
select a.*
from t a,t b
where a.BZID=b.BZID
and a.RECORDDATE=b.RECORDDATE
and a.ITEMPRICE<>b.ITEMPRICE
;
uj5u.com熱心網友回復：
WITH TEMP AS(
SELECT distinct b.BZID,a.ITEMPRICE,"SUBSTR"(a.RECORDDATE,0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a
LEFT JOIN YUHUA_KANGJUN2 b on a.CLINICID=b.CLINICID
WHERE a.ORGCODE ='320114002PDY100109'
ORDER BY b.BZID ASC NULLS LAST, "SUBSTR"(a.RECORDDATE,0, 7) ASC NULLS LAST)
SELECT DISTINCT BZID
FROM TEMP TEMP1
WHERE EXISTS (SELECT 1
FROM TEMP TEMP2
WHERE TEMP1.BZID = TEMP2.BZID
AND TEMP1.RECORDDATE = TEMP2.RECORDDATE
AND TEMP1.ITEMPRICE != TEMP2.ITEMPRICE)
uj5u.com熱心網友回復：
查詢重復次數 應該不用distinct
uj5u.com熱心網友回復：
select b.bzid, substr(a.recorddate, 0, 7)from yuhua_jiceng2_copy a
left join yuhua_kangjun2 b
on a.clinicid = b.clinicid
where a.orgcode = '320114002PDY100109'
group by b.bzid, substr(a.recorddate, 0, 7)
having max(a.itemprice) <> min(a.itemprice)
uj5u.com熱心網友回復：
select t.BZID,NVL(t.max_ITEMPRIC,0),NVL(t.min_ITEMPRICE,0),t.RECORDDATE from (SELECT b.BZID,max(a.ITEMPRICE) max_ITEMPRICE,min（a.ITEMPRICE）min_ITEMPRICE,"SUBSTR"(a.RECORDDATE,0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a
LEFT JOIN YUHUA_KANGJUN2 b on a.CLINICID=b.CLINICID
WHERE a.ORGCODE ='320114002PDY100109'
group by b.BZID,"SUBSTR"(a.RECORDDATE,0, 7)
ORDER BY b.BZID ASC NULLS LAST, "SUBSTR"(a.RECORDDATE,0, 7)
ASC NULLS LAST) t where t.max_ITEMPRIC<>t.min_ITEMPRICE
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106210.html
標籤：基礎和管理