Consult, as shown in figure, want to have BZID and date in the query results, but the price is not the same as data of ID, what should I do


SELECT distinct b.B ZID, Anderson TEMPRICE, "SUBSTR (a.R ECORDDATE, 0, 7) FROM YUHUA_JICENG2_COPY a
LEFT the JOIN YUHUA_KANGJUN2 b on a.C LINICID=biggest LINICID
WHERE a.ORG CODE='320114002 pdy100109'
The ORDER BY b.B ZID ASC crosstab LAST, "SUBSTR (a.R ECORDDATE, 0, 7) ASC crosstab LAST

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
BZID ITEMPRICE RECORDDATE
1336672 71.6000 2015/12
71.6000/2016/1 1336672
1336672 62.3000 2016/10
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
1336672 62.3000/2016/8

CodePudding user response:

As

 with t(SELECT distinct b.B ZID, Anderson TEMPRICE, "SUBSTR (a.R ECORDDATE, 0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a 
LEFT the JOIN YUHUA_KANGJUN2 b on a.C LINICID=biggest LINICID 
WHERE a.ORG CODE='320114002 pdy100109' 
The ORDER BY b.B ZID ASC crosstab LAST, "SUBSTR (a.R ECORDDATE, 0, 7) ASC crosstab LAST) 
Select a. * 
The from t a, t b 
Where a. ZID=b.B ZID 
And a.R ECORDDATE=b.R ECORDDATE 
And Anderson, TEMPRICE<> B.I TEMPRICE 
; 

CodePudding user response:

 
WITH TEMP AS (
SELECT distinct b.B ZID, Anderson TEMPRICE, "SUBSTR (a.R ECORDDATE, 0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a 
LEFT the JOIN YUHUA_KANGJUN2 b on a.C LINICID=biggest LINICID 
WHERE a.ORG CODE='320114002 pdy100109' 
The ORDER BY b.B ZID ASC crosstab LAST, "SUBSTR (a.R ECORDDATE, 0, 7) ASC crosstab LAST) 
SELECT DISTINCT BZID 
The FROM TEMP TEMP1 
WHERE the EXISTS (SELECT 1 
The FROM TEMP TEMP2 
WHERE TEMP1. BZID=TEMP2. BZID 
AND TEMP1. RECORDDATE=TEMP2. RECORDDATE 
AND TEMP1 ITEMPRICE!=TEMP2. ITEMPRICE) 

CodePudding user response:

Query repetitions should not distinct

CodePudding user response:

The select b.b zid, substr (a.r ecorddate, 0, 7)
The from yuhua_jiceng2_copy a
Left the join yuhua_kangjun2 b
On a.c linicid=biggest linicid
Where a.org code='320114002 pdy100109'
Group by b.b zid, substr (a.r ecorddate, 0, 7)
Having Max (Anderson temprice) & lt;> Min (Anderson temprice)

CodePudding user response:

The select t.B ZID NVL (t.m ax_ITEMPRIC, 0), NVL (t.m in_ITEMPRICE, 0), t.R ECORDDATE from (
The SELECT b.B ZID, Max (Anderson TEMPRICE max_ITEMPRICE), min (Anderson TEMPRICE) min_ITEMPRICE, "SUBSTR (a.R ECORDDATE, 0, 7) RECORDDATE FROM YUHUA_JICENG2_COPY a
LEFT the JOIN YUHUA_KANGJUN2 b on a.C LINICID=biggest LINICID
WHERE a.ORG CODE='320114002 pdy100109'
Group by b.B ZID, "SUBSTR (a.R ECORDDATE, 0, 7)
The ORDER BY b.B ZID ASC crosstab LAST, "SUBSTR (a.R ECORDDATE, 0, 7)
ASC crosstab LAST) t where t.m ax_ITEMPRIC & lt;> T.m in_ITEMPRICE