mysql - 计算从 2 个单独的内联选择语句中获取的值

Question

我有一个已简化并写在下面的查询。如何显示作为 2 个单独的内联选择语句计算的 2 个字段的时间差？

SELECT x.name ,
   ( SELECT data.ts 
     FROM data 
     WHERE prim_key = x.prim_key and roll_no ='1'  
 ) **starttime** ,
  ( SELECT data.ts 
    FROM woman_data 
    WHERE prim_key = x.prim_key and roll_no ='10'

) **endtime**
, **TIME_TO_SEC( TIMEDIFF( endtime , starttime)** as timediff // This line does not work.It cannot recognize endtime ans starttime
FROM data x 
     INNER JOIN  y 
ON x.prim_key = y.prim_key
ORDER BY x.prim_key

Answer 1

试试这个:

starttime 和 endtime 是派生列，不能在同一个 select 语句中使用。您必须创建一个派生表，然后在外部查询中使用这些列

select name,starttime,endtime,TIME_TO_SEC( TIMEDIFF( endtime , starttime) as timediff
from(
SELECT x.name ,
   ( SELECT data.ts 
     FROM data 
     WHERE prim_key = x.prim_key and roll_no ='1'  
 ) starttime ,
  ( SELECT data.ts 
    FROM woman_data 
    WHERE prim_key = x.prim_key and roll_no ='10'

) endtime

FROM data x 
     INNER JOIN  y 
ON x.prim_key = y.prim_key)a
ORDER BY x.prim_key