一个复杂的sql 查询

最新推荐文章于 2022-09-17 14:45:52 发布

GoodStudyAndDayDayUp

最新推荐文章于 2022-09-17 14:45:52 发布

阅读量100

点赞数

分类专栏： mysql 文章标签：四级联，sql 查询

本文链接：https://blog.csdn.net/leinminna/article/details/116043344

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

该博客内容涉及将复杂的四级联SQL查询拆分成三个两级联查询，分别针对productGroup到product，product到region，region到customer的关系进行处理。通过WITH子句创建中间表，利用LEFT JOIN和GROUP BY等操作连接和聚合数据，实现数据的层级联检索。

摘要由CSDN通过智能技术生成

四级联，sql 拆分为三个两级级联

1.

四级联，sql 查询
productGroup->product->region->customer

WITH prod_group AS (
	SELECT
		dict_code AS product_group 
	FROM
		sys_dict 
	WHERE
		parent_id = ( SELECT ID FROM sys_dict WHERE dict_code = '40f44f426e874c989fce836ebfaea451' ) 
		AND dict_code != 'c8ea8e97547841278af5f229a05d30da' 
		AND is_delete = 0 
	),
	prod AS ( 
	SELECT npi_product_name AS product FROM dim_product_mapping GROUP BY npi_product_name 
	),
	prodgroup_prod AS ( 
	SELECT * FROM prod_group, prod UNION ALL SELECT 'c8ea8e97547841278af5f229a05d30da' AS product_group, 'All' AS product 
	)
	,
	half_region AS (
	SELECT
		prod.npi_product_name AS product,
		region.region 
	FROM
		dim_product_mapping AS prod
		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
	GROUP BY
		prod.npi_product_name,
		region.region 
	ORDER BY
		prod.npi_product_name,
		region.region 
	)
	, product_s as(
		select product FROM half_region  GROUP BY product ORDER BY product)
	-- region of product in All
	, region_s as(
	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
	, prod_reg as (
	select * from product_s,region_s
	)
	,product_region AS ( 
	SELECT * FROM half_region 
	UNION SELECT * FROM prod_reg 
	UNION ALL 
	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
	)
	,r_tem AS (
	SELECT
		reg.region,
		cus.npi_customer AS customer 
	FROM
		dim_mapping_region_country AS reg
		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
	WHERE
		reg.region IS NOT NULL 
	GROUP BY
		reg.region,
		cus.npi_customer UNION ALL
	SELECT
		npi_region AS region,
		pss_npi_customer AS customer 
	FROM
		datahub_customer_mapping_business_case 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		pss_npi_customer UNION ALL
	SELECT
		npi_region AS region,
		npi_customer AS customer 
	FROM
		datahub_customer_mapping_pdp 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		npi_customer 
	),
	region_customer AS ( SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer )
	,
	tem AS (
	SELECT
		pgp.product_group,
		pgp.product,
		pr.region
		,
		rc.customer 
	FROM
		prodgroup_prod AS pgp
		LEFT JOIN product_region AS pr ON pr.product = pgp.product
		LEFT JOIN region_customer AS rc ON rc.region = pr.region 
	WHERE
		pgp.product_group IS NOT NULL 
	GROUP BY
		pgp.product_group,
		pgp.product,
		pr.region
		,rc.customer 
	ORDER BY
		pgp.product_group,
		pgp.product,
		pr.region
		,rc.customer 
	) SELECT
	concat_ws ( '---', dict_name, product_group) AS "dict_name---dict_code",
	product,
	region
	,
	customer 
FROM
	tem
	LEFT JOIN sys_dict AS sys ON sys.dict_code = tem.product_group

2.productGroup->product

-- productGroup/product (200)
WITH prod_group AS (
	SELECT
		dict_code AS product_group 
	FROM
		sys_dict 
	WHERE
		parent_id = ( SELECT ID FROM sys_dict WHERE dict_code = '40f44f426e874c989fce836ebfaea451' ) 
		AND dict_code != 'c8ea8e97547841278af5f229a05d30da' 
		AND is_delete = 0 
	),
	prod AS ( 
	SELECT npi_product_name AS product FROM dim_product_mapping GROUP BY npi_product_name 
	)
	,
	prodgroup_prod AS ( 
	SELECT * FROM prod_group, prod UNION ALL SELECT 'c8ea8e97547841278af5f229a05d30da' AS product_group, 'All' AS product 
	)
	SELECT
	concat_ws ( '---', dict_name, product_group) AS "dict_name---dict_code",
	product
FROM
	prodgroup_prod as tem
	LEFT JOIN sys_dict AS sys ON sys.dict_code = tem.product_group

3.product->region

-- product/region(934)

	with half_region AS (
	SELECT
		prod.npi_product_name AS product,
		region.region 
	FROM
		dim_product_mapping AS prod
		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
	GROUP BY
		prod.npi_product_name,
		region.region 
	ORDER BY
		prod.npi_product_name,
		region.region 
	)
	, product_s as(
		select product FROM half_region  GROUP BY product ORDER BY product)
	-- region of product in All
	, region_s as(
	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
	, prod_reg as (
	select * from product_s,region_s
	)
	,product_region AS ( 
	SELECT * FROM half_region 
	UNION SELECT * FROM prod_reg 
	UNION ALL 
	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
	)
	select * from product_region group by product, region ORDER BY product, region

4.region->customer

	-- region/customer(34)
	with r_tem AS (
	SELECT
		reg.region,
		cus.npi_customer AS customer 
	FROM
		dim_mapping_region_country AS reg
		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
	WHERE
		reg.region IS NOT NULL 
	GROUP BY
		reg.region,
		cus.npi_customer UNION ALL
	SELECT
		npi_region AS region,
		pss_npi_customer AS customer 
	FROM
		datahub_customer_mapping_business_case 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		pss_npi_customer UNION ALL
	SELECT
		npi_region AS region,
		npi_customer AS customer 
	FROM
		datahub_customer_mapping_pdp 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		npi_customer 
	)
	SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer

5.product/region/customer

还可以拆分为一个三级级联

	with half_region AS (
	SELECT
		prod.npi_product_name AS product,
		region.region 
	FROM
		dim_product_mapping AS prod
		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
	GROUP BY
		prod.npi_product_name,
		region.region 
	ORDER BY
		prod.npi_product_name,
		region.region 
	)
	, product_s as(
		select product FROM half_region  GROUP BY product ORDER BY product)
	-- region of product in All
	, region_s as(
	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
	, prod_reg as (
	select * from product_s,region_s
	)
	,product_region AS ( 
	SELECT * FROM half_region 
	UNION SELECT * FROM prod_reg 
	UNION ALL 
	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
	)
	,r_tem AS (
	SELECT
		reg.region,
		cus.npi_customer AS customer 
	FROM
		dim_mapping_region_country AS reg
		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
	WHERE
		reg.region IS NOT NULL 
	GROUP BY
		reg.region,
		cus.npi_customer UNION ALL
	SELECT
		npi_region AS region,
		pss_npi_customer AS customer 
	FROM
		datahub_customer_mapping_business_case 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		pss_npi_customer UNION ALL
	SELECT
		npi_region AS region,
		npi_customer AS customer 
	FROM
		datahub_customer_mapping_pdp 
	WHERE
		npi_region IS NOT NULL 
	GROUP BY
		npi_region,
		npi_customer 
	),
	region_customer AS ( SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer )
-- 	,
-- 	tem AS (
	SELECT
-- 		pgp.product_group,
		pr.product,
		pr.region
		,
		rc.customer 
	FROM
		product_region AS pr 
		LEFT JOIN region_customer AS rc ON rc.region = pr.region 
	WHERE
		pr.product IS NOT NULL 
	GROUP BY
-- 		pgp.product_group,
		pr.product,
		pr.region
		,rc.customer 
	ORDER BY
-- 		pgp.product_group,
		pr.product,
		pr.region
		,rc.customer

GoodStudyAndDayDayUp

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
一个复杂的sql 查询

四级联，sql 查询-- productGroupwith prod_group as(select dict_name as product_group from sys_dict where parent_id=(select id from sys_dict where dict_code='40f44f426e874c989fce836ebfaea451') and dict_code !='c8ea8e97547841278af5f229a05d30da' and is_delete=0
复制链接

扫一扫

专栏目录