There are two table
A car form (one million records)
A car ID device number, lane number, image path, car time
Field (GCID, SBBH CDBH, imgurl GCSJ)
Lane information table CDXX (one thousand records)
(ID SBBH CDBH) under the equipment number is the only one with multiple lane number
Note: the two tables did not establish foreign keys

Now need a period of time all lanes each lane to take a picture, there is no record image blank

Please everyone a great god show

 
SELECT DISTINCT SBBH, CDBH 
The FROM GCJL 
WHERE GCSJ & gt; 08:00:00=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
AND GCSJ & lt; 09:00:00=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS'); 

Now think only of to heavy equipment number and lane number but to other columns do not display for you after the great spirit show

Left lane information table join a car form only through SBBH and CDBH lane will be greater than the record of the information table

To everybody a great god directions for every great god give directions to everybody a great god directions for every great god show

CodePudding user response:

 
SELECT distinct CDXX SBBH, CDXX. CDBH 
The FROM CDXX CDXX left join GXJL GCJL 
On CDXX. SBBH=GCJL. SBBH AND CDXX. CDBH=GCJL. CDBH 
WHERE JGSJ & gt; 08:00:00=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
AND JGSJ & lt; 08:00:01=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 

You can't get my want image path to solve solution

CodePudding user response:

 SELECT CDXX SBBH, CDXX. CDBH, Max (imgurl) 
The FROM CDXX CDXX left join GXJL GCJL 
On CDXX. SBBH=GCJL. SBBH AND CDXX. CDBH=GCJL. CDBH 
WHERE JGSJ & gt; 08:00:00=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
AND JGSJ & lt; 08:00:01=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
Group by CDXX SBBH, CDXX. CDBH; 

CodePudding user response:

Logic is a bit messy, look for 2 times just understand
You just to show SBBH CDBH, and then add any a picture record imgurl?

CodePudding user response:

refer to the second floor js14982 response:

 SELECT CDXX. SBBH, CDXX. CDBH, Max (imgurl) 
The FROM CDXX CDXX left join GXJL GCJL 
On CDXX. SBBH=GCJL. SBBH AND CDXX. CDBH=GCJL. CDBH 
WHERE JGSJ & gt; 08:00:00=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
AND JGSJ & lt; 08:00:01=TO_DATE (' 2014-01-15 ', '- DD YYYY - MM HH24: MI: SS') 
Group by CDXX SBBH, CDXX. CDBH; 

eldest brother here with Max () what is the function of
And if I want to no record imgurl null how

reference js14982 reply: 3/f

see logic is a bit messy, looked at 2 times just understand
You just to show SBBH CDBH, and then add any a picture record imgurl?

The best there is no matching images show the other columns null

CodePudding user response:

The

reference 4 floor qq_36072945 reply:

The other columns? What column do you want?

CodePudding user response:

This statement is SBBH CDBH recorded, image path without record shows empty

CodePudding user response:

'now need a period of time all lanes each lane to take a picture, there is no record image blank'
According to the description of the lz, I put the device as a camera or photo machine, lz want all lane and take a picture every lane, lane that a photograph may have multiple devices, as long as one can,
If more than one lane equipment, some pictures captured some didn't get, that is about to use row_number sorted by lane partition image path livestock in article 1, there is the picture which records, specific statement is as follows:
The SELECT A.C DBH, DECODE (B.I MGURL, NULL, NULL, B.I MGURL) AS IMGURL
The FROM CDXX A LEFT JOIN (
The SELECT CDBH, IMGURL row_number () over (partition by CDBH order by IMGURL) rn
The FROM GCJL
WHERE GCSJ>=DATE1 AND GCSJB)
WHERE A.S BBH=B.S BBH AND B.R N=1
GROUP BY A.C DBH
;
Do not know to understand right?