mysql - SQL 中的嵌套查询

Question

我正在尝试使用嵌套查询组合 2 个查询。第一个是这样的:

SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as start_time
FROM table1 AS de
JOIN table1table2 AS md
ON de.subcode= md.subcode
WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' AND de.end IS NULL AND de.Subcat = 'STOP'
ORDER BY de.time

生成结果

Mach  ShortenCode  Tme  
Mach1   451         2017-12-25 08:25
Mach2   854         2017-12-25 08:25

所以 451 在 Mach1 上。棘手的部分是，对于 ShortenCode，我想显示它所在的前一个 Mach。它会是这样的:

SELECT distinct de.MCH_CODE FROM table1 AS de
join table2 as md
ON de.subcode = md.subcode 
WHERE de.ShortenCode = 'the ones displayed in the first query'

如何通过子查询获取:

Mach  ShortenCode  Tme                 Mach(Previous)    
Mach1   451         2017-12-25 08:25   Mach4
Mach2   854         2017-12-25 08:25   Mach5

基本上 Mach 列有 Mach1，Mach2，还有 Mach4 和 Mach5。我试过这个但没有成功:

SELECT t1.Mach, t1.ShortenCode, t1.start_time, t2.PreviousMach
    FROM( SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as start_time, mch_
            FROM table1 AS de
            JOIN table1table2 AS md
            ON de.subcode= md.subcode
            WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' AND de.end IS NULL AND de.Subcat = 'STOP'
            ORDER BY de.time
    ) t1
    join
        ( SELECT distinct de.MCH_CODE FROM table1 AS de
                join table2 as md
                ON de.subcode = md.subcode 
                WHERE de.ShortenCode = t1.ShortenCode
        ) t2

如果您有任何建议，我将不胜感激

Answer 1

我想你可以尝试像这样在 SELECT 部分获取子查询

SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as 
start_time,
(SELECT dee.MCH_CODE FROM table1 AS dee JOIN table1table2 AS mdd
ON dee.subcode= mdd.subcodewhere md.MAT_CODE = mdd.MAT_CODE and 
dee.MCH_CODE < de.MCH_CODE order by dee.MCH_CODE DESC LIMIT 1) as prevMach
FROM table1 AS de
JOIN table1table2 AS md
ON de.subcode= md.subcode
WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' 
AND de.end IS NULL AND de.Subcat = 'STOP'
ORDER BY de.time

尽管我猜测根据索引和数据量计算可能需要很长时间。