如何写sql语句 [英] How to write the sql statement

问题描述

SQL，

Hi,

the SQL,

SELECT CONVERT(VARCHAR(10), TI_UTCTIME, 121)as [TRAVEL_DATE],
MIN(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [START_TIME],
MAX(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [STOP_TIME],
SD_STUDENTID as [STUDENT_ID],SD_STUDENTNAME as [STUDENT_NAME],
TI_DEVICEID as [DEVICE_ID]

FROM
 
(SELECT TI_UTCTIME,TI_DEVICEID,	TI_DATA
 FROM STS_TRAVELING_INFORMATION) AS T1
 
 JOIN
 
(SELECT SD_STUDENTID,SD_STUDENTNAME
 FROM STS_STUDENT_DETAILS_ WHERE SD_STUDENTNAME='Haseeb'
 AND SD_STUDENTCLASS='12') AS T2
 
 ON T1.TI_DATA LIKE '%'+T2.SD_STUDENTID
 WHERE TI_UTCTIME BETWEEN '2012-07-02' AND '2012-10-01'  
 GROUP BY CONVERT(VARCHAR(10), TI_UTCTIME, 121),TI_DEVICEID,SD_STUDENTID,SD_STUDENTNAME

检索表，如下所示，

retrieve the TABLE as below,

DATE        START_TIME           STOP_TIME        STUDENT_ID STUDENT_NAME DEVICE_ID
----        ----------           ---------        ---------- ---------    -------
2012-07-02 2012-07-02 11:07	2012-07-02 15:52   DT906	Haseeb	ST0001
2012-07-06 2012-07-06 18:45	2012-07-06 18:48   DT906	Haseeb	ST0001
2012-09-04 2012-09-04 12:35	2012-09-04 17:42   DT906	Haseeb	ST0001

SQL，

the SQL,

SELECT DATE,VRS_DEVICEID,LOCATION,TRAVEL_DATE FROM
(
    SELECT Row_number() over (
        PARTITION BY  SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16)
        order by CONVERT(VARCHAR(10), VRS_UTCTIME, 121),VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')'
        ) as rowno,CONVERT(VARCHAR(10), VRS_UTCTIME, 121)as [TRAVEL_DATE],
         VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
    SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
    FROM STS_VEHICLE_RUNNING_STATUS
)AS T1
where rowno = 1

检索表，如下所示，

retrieve the TABLE as below,

 DATE                VRS_DEVICEID   LOCATION     TRAVEL DATE

2012-07-02 11:07       ST001        TVM           2012-07-02

2012-07-02 11:02       ST001        KANIYAPURAM    2012-07-02

2012-07-02 15:52       ST001        KOLLAM         2012-07-02

2012-07-06 18:45       ST001        KOTTAKKAL      2012-07-06

2012-07-06 18:45       ST001        CHANKUVETTI    2012-07-06

2012-07-06 18:48       ST001        MANNARKKAD     2012-07-06

2012-09-04 12:35       ST001        KERALA         2012-09-04


2012-09-04 14:42       ST001        MAVOOR         2012-09-04

2012-09-04 17:42       ST001        KOVALAM        2012-09-04

然后，我要合并下面的两个表，如下所示，

Then ,i want to combine above two table as below,

 DATE        START_TIME        STOP_TIME       STUDENT_ID  STUDENT_NAME DEVICE_ID ENTRY_LOC  EXIT_LOC
------      ------------       ---------       ---------   -----------  --------  ---------  --------
2012-07-02  2012-07-02 11:07  2012-07-02 15:53  DT906	    Haseeb	 ST0001    TVM       KOLLAM
2012-07-06  2012-07-06 18:45  2012-07-06 18:48  DT906	    Haseeb	 ST0001    KOTTAKKAL MANNARKKAD
2012-09-04  2012-09-04 12:35  2012-09-04 17:42  DT906	    Haseeb	 ST0001    KERALA    KOVALAM

也就是说，

比较第一个表中的START_TIME列和第二个表中的DATE列，然后
检索相应的LOCATION值，该值将作为
添加到第一张表中 ENRTY_LOC列.

比较第一个表中的STOP_TIME列与第二个表中的DATE列，然后
检索相应的LOCATION值，该值将作为
添加到第一张表中 EXIT_LOC列

谢谢..

That is,

compare the START_TIME column in first table with DATE column in 2nd table, then
retrieve the corresponding LOCATION value it add to 1st table as
ENRTY_LOC column.

compare the STOP_TIME column in first table with DATE column in 2nd table, then
retrieve the corresponding LOCATION value it add to 1st table as
EXIT_LOC column

Thanks..

推荐答案

easy:

easy:

SELECT DATE,VRS_DEVICEID,LOCATION,TRAVEL_DATE into #tempDevice FROM
(
    SELECT Row_number() over (
        PARTITION BY  SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16)
        order by CONVERT(VARCHAR(10), VRS_UTCTIME, 121),VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')'
        ) as rowno,CONVERT(VARCHAR(10), VRS_UTCTIME, 121)as [TRAVEL_DATE],
         VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
    SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
    FROM STS_VEHICLE_RUNNING_STATUS
)AS T1
where rowno = 1
 
SELECT CONVERT(VARCHAR(10), TI_UTCTIME, 121)as [TRAVEL_DATE],
MIN(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [START_TIME],
MAX(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [STOP_TIME],
SD_STUDENTID as [STUDENT_ID],SD_STUDENTNAME as [STUDENT_NAME],
TI_DEVICEID as [DEVICE_ID]
into #tempStudentDevice
FROM
 
(SELECT TI_UTCTIME,TI_DEVICEID,	TI_DATA
 FROM STS_TRAVELING_INFORMATION) AS T1
 
 JOIN
 
(SELECT SD_STUDENTID,SD_STUDENTNAME
 FROM STS_STUDENT_DETAILS_ WHERE SD_STUDENTNAME='Haseeb'
 AND SD_STUDENTCLASS='12') AS T2
 
 ON T1.TI_DATA LIKE '%<'+T2.SD_STUDENTID
 WHERE TI_UTCTIME BETWEEN '2012-07-02' AND '2012-10-01'  
 GROUP BY CONVERT(VARCHAR(10), TI_UTCTIME, 121),TI_DEVICEID,SD_STUDENTID,SD_STUDENTNAME
 
select  sd.DATE,sd.START_TIME,sd.STOP_TIME,sd.STUDENT_ID,sd.STUDENT_NAME,
        sd.DEVICE_ID, 
		(
			SELECT x.Location From #tempDevice as x
			Where x.vrs_DEVICEID = sd.Device_ID
			AND		sd.[START_TIME] = x.Date
		)as ENTRY_LOC, 
		
		(
			SELECT x.Location From #tempDevice as x
			Where x.vrs_DEVICEID = sd.Device_ID
			AND		sd.[STOP_TIME] = x.Date 
		)
		as EXIT_LOC
from #tempstudentDevice as sd
JOIN #tempDevice as d on d.VRS_DEVICEID = sd.DEVICE_ID
  
 
drop table #tempDevice, #tempstudentDevice

这篇关于如何写sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！