当MySQL中无法使用with关键词时该怎么办？

当MySQL中无法使用with关键词时该怎么办？

with
    c1 as (SELECT count(*) FROM database.table1),
    c2 as (SELECT count(*) FROM database.table2),
    c3 as (SELECT count(*) FROM database.table3),
    c4 as (SELECT count(*) FROM database.table4),
    c5 as (SELECT count(*) FROM database.table5)
select c1.*,c2.*,c3.*,c4.*,c5.*
    from c1,c2,c3,c4,c5

在MySQL中，由于WITH子句可能不被所有版本支持，或者在某些配置下不可用，你可以通过使用子查询和直接JOIN的方式来实现类似的效果。以下是将上述WITH语句转换为MySQL兼容版本的示例：

SELECT 
    (SELECT COUNT(*) FROM database.table1) AS c1_count,
    (SELECT COUNT(*) FROM database.table2) AS c2_count,
    (SELECT COUNT(*) FROM database.table3) AS c3_count,
    (SELECT COUNT(*) FROM database.table4) AS c4_count,
    (SELECT COUNT(*) FROM database.table5) AS c5_count
FROM 
    (SELECT 1) AS dummy;

这里，我们使用了一个包含单行的虚拟表(SELECT 1) AS dummy来作为连接的基础，因为MySQL不支持无FROM子句的多子查询SELECT。每个子查询计算一个表的记录数，并通过AS子句给每个计数赋予一个别名。这样，你可以在结果集中得到五个表的记录数，就像原WITH语句意图做的那样。