56,677
社区成员
发帖
与我相关
我的任务
分享
DYN_VECHILE表
FLIGHT_NO,START_TIME,END_TIME,VECHILE_ID,VECHILE_NUM
1 10:20 10:23 10 2
1 10:25 10:30 20 3
1 11:20 11:23 30 0
1 13:27 13:33 40 5
1 15:30 15:43 50 8
2 19:27 19:33 10 6
2 23:27 23:33 20 2
2 18:27 18:33 30 1
SELECT FLIGHT_NO,
(SELECT START_TIME FROM DYN_VECHILE v1 WHERE VECHILE_ID=10 AND v1.FLIGHT_NO = v.FLIGHT_NO) AS '10Begin',
(SELECT END_TIME FROM DYN_VECHILE v2 WHERE VECHILE_ID=10 AND v2.FLIGHT_NO = v.FLIGHT_NO) AS '10END',
(SELECT START_TIME FROM DYN_VECHILE v3 WHERE VECHILE_ID=20 AND v3.FLIGHT_NO = v.FLIGHT_NO) AS '20Begin',
(SELECT END_TIME FROM DYN_VECHILE v4 WHERE VECHILE_ID=20 AND v4.FLIGHT_NO = v.FLIGHT_NO) AS '20END',
SUM(CASE VECHILE_ID WHEN 30 THEN VECHILE_NUM ELSE 0 END) AS '30Times',
SUM(CASE VECHILE_ID WHEN 40 THEN VECHILE_NUM ELSE 0 END) AS '40Times'
FROM
DYN_VECHILE v
GROUP BY
FLIGHT_NO
select FLIGHT_NO,
Max(IF(VECHILE_ID=10,START_TIME,null)) as `10Begin`,
max(if(VECHILE_ID=10,END_TIME,null)) as `10END`,
max(if(VECHILE_ID=20,START_TIME,null)) as `20Begin`,
max(if(VECHILE_ID=20,END_TIME,null)) AS `20END`,
Sum(IF(VECHILE_ID=30,VECHILE_NUM,null)) as `30Times`,
sum(if(VECHILE_ID=40,VECHILE_NUM,null)) as `40Times`
from DYN_VECHILE
group By FLIGHT_NO