虚拟表的使用和创建

咸菜～

已于 2023-09-07 08:54:59 修改

阅读量342

点赞数

分类专栏： sql 文章标签： sql java

于 2022-12-19 15:24:56 首次发布

本文链接：https://blog.csdn.net/nkdas/article/details/128373191

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

格式

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;