格式
with recursive 表名字 as(查询语句) 表明b as(查询语句) select * from 表名字,表明b
示例
with recursive a as ( SELECT count(1) as dw FROM xnb_hn_zyjc_main_process WHERE cczg_sfdw=1 and sheng='湖南省' ),
b as ( SELECT count(1) as zgbdw FROM xnb_hn_zyjc_main_process WHERE cczg_sfdw=2 and sheng='湖南省' )
SELECT * FROM a,b
查虚拟表
with statis as
( select total_length, total_time, area_code from patrol_analysis_statistics )
select t_.area_name as xzqName, t_.area_name as name,
(select round(sum(total_length) / 1000, 2) from statis) as mileage,
(select sum(total_time) from statis) as duration
from sys_area t_
不支持with 关键字
SELECT
a.fiwtons + b.total_fiwtons AS total_fiwtons,
a.fontons + b.fontons AS total_fontons,
a.fintons + b.fintons AS total_fintons,
a.fowtons + b.total_fowtons AS total_fowtons,
a.fcname as fcname
FROM (
SELECT
tb_throughput_total.fiwtons,
tb_throughput_total.fontons,
tb_throughput_total.fintons,
tb_throughput_total.fowtons,
tb_throughput_total.fbegintime,
tb_throughput_total.fendtime,
tb_cargo_type.fname AS fcname
FROM
tb_throughput_total
LEFT JOIN
tb_cargo_type
ON
tb_throughput_total.fcargoid = tb_cargo_type.id
WHERE
fdept = '24'
AND MONTH(tb_throughput_total.fbegintime) = 1
AND MONTH(tb_throughput_total.fendtime) = MONTH(CURRENT_DATE())
AND YEAR(tb_throughput_total.fbegintime) = YEAR(CURRENT_DATE()) -1
AND YEAR(tb_throughput_total.fendtime) = YEAR(CURRENT_DATE()) -1
) AS a
JOIN (
SELECT
SUM(tb_throughput.fiwtons) AS total_fiwtons,
SUM(tb_throughput.fontons) AS fontons,
SUM(tb_throughput.fintons) AS fintons,
SUM(tb_throughput.fowtons) AS total_fowtons,
tb_cargo_type.fname AS fcname,
tb_throughput.fmonth
FROM
tb_throughput
LEFT JOIN
tb_cargo_type
ON
tb_throughput.fcargoid = tb_cargo_type.id
WHERE
SUBSTRING(tb_throughput.fmonth, 1, 4) = YEAR(CURRENT_DATE()) - 1
AND SUBSTRING(tb_throughput.fmonth, 6, 2) = MONTH(CURRENT_DATE())
AND tb_throughput.fisvalid = 1
AND (fdept = 17)
GROUP BY
tb_cargo_type.fname, tb_throughput.fmonth
) AS b
ON a.fcname = b.fcname;