背景： 我们公司想从SQL server 迁移到其他数据库 ，在测试 MySQL8.0 vs tidb SQL语法方面 TiDB 有一些不支持，希望能增强对下列语法的支持。 CTE、横向派生表、CROSS APPLY、OUTER APPLY、WITH NOLOCK、NOWAIT、SKIP LOCKED CTE：
WITH T AS ( SELECT P.a, P.a FROM P WHERE P.a>=‘2018-01-01’ UNION ALL SELECT P.siD, P.opRowStatus FROM P WHERE P.a>=‘2018-01-01’ ) SELECT*FROM
横向派生表：
SELECT P.siD, LT.* FROM P, LATERAL ( SELECT SUM(CASE WHEN PC.a=1 THEN PC.a ELSE 0 END) NeedUserPay, SUM(CASE WHEN PC.a=1 THEN 0 ELSE PC.a END) NotNeedUserPay FROM PC WHERE PC.id=P.siD ) AS LT
CROSS APPLY：
SELECT P.siD, LT.* FROM P INNER JOIN LATERAL ( SELECT PC.p FROM PC PC WHERE PC.id=P.siD LIMIT 1 ) AS LT ON 1=1 /* ON 1=1 is just a trick to join to whatever the LATERAL returns */
OUTER APPLY： SELECT P.siD, LT.* FROM BT_AR_OrderPay P LEFT JOIN LATERAL ( SELECT PC.p FROM PC PC WHERE PC.id=P.siD ) AS LT ON 1=1