有史以来写过最长的sql

最新推荐文章于 2022-08-23 11:00:54 发布
小馋
最新推荐文章于 2022-08-23 11:00:54 发布
阅读量954
点赞数 1
本文链接：https://blog.csdn.net/qq764136933/article/details/83096533
版权
SELECT
	-- 单据类型
	biz_so_out.biz_type,
	-- 单据日期
	biz_so_out.biz_date,
	-- 单据号
	biz_so_out.bill_no,
	-- 零售单据号
	biz_so.bill_no as biz_so_bill_no,
	-- 品牌
	biz_so_detail_01.brand_uuid,
	-- 商品名称
	biz_so_detail_01.sku_full_name,
	-- 数量
	biz_so_detail_01.qty,
	-- 单价
	biz_so_detail_01.price,
	-- 店员
	biz_so_detail_01.clerk_names,
	-- 会员姓名
	sys_member.`name` as member_name,
	-- 会员手机号
	sys_member.mobile_phone,
	-- 会员证件号
	sys_member.card_no,
	-- 商品分类
	biz_so_detail_01.classify_uuid,
	-- 助记码
	biz_so_detail_01.mnemonic_code,
	-- 商品编码
	biz_so_detail_01.`code`,
	-- 商品型号
	biz_so_detail_01.sku_name,
	-- 公司
	biz_so_out.company_uuid,
	-- 仓库
	biz_so_detail_01.warehouse_uuid,
	biz_so_detail_01.warehouse_name,
	-- 头部备注
	biz_so_out.remark,
	-- 明细备注
	biz_so_detail_01.remark AS detail_remark,
	-- 修改人
	sys_user_01.`name` as update_user_name,
	-- 修改时间
	biz_so_out.update_user_uuid,
	-- 审核人
	sys_user_02.`name`,
	-- 修改时间
	biz_so_out.audit_time,
	-- 序列号
	biz_so_detail_01.serial_info_uuids
FROM
	biz_so_out
INNER JOIN biz_so ON biz_so_out.biz_so_uuid = biz_so.uuid
LEFT JOIN (
	SELECT
		biz_so_detail.*, sys_sku.brand_uuid,
		sys_sku.`name` AS sku_name,
		sys_sku.full_name AS sku_full_name,
		sys_sku.classify_uuid,
		sys_sku.mnemonic_code,
		sys_sku.`code`,
		base_warehouse.`name` AS warehouse_name,
		core_serial_info.serial01 + ',' + core_serial_info.serial02 + ',' + core_serial_info.serial03 + ',' + core_serial_info.serial04 + ',' + core_serial_info.serial05 + ',' + core_serial_info.serial06 + ',' + core_serial_info.serial07 + ',' + core_serial_info.serial08 + ',' + core_serial_info.serial09 + ',' + core_serial_info.serial10 AS serial_info_uuids,
		CASE biz_so_out_clerk.num
	WHEN biz_so_out_clerk.num = 1 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 2 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 3 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 4 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 5 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk05_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 6 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk05_uuid + ',' + biz_so_out_clerk.clerk06_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 7 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk06_uuid + ',' + biz_so_out_clerk.clerk07_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 8 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk06_uuid + ',' + biz_so_out_clerk.clerk07_uuid + ',' + biz_so_out_clerk.clerk08_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 9 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk06_uuid + ',' + biz_so_out_clerk.clerk07_uuid + ',' + biz_so_out_clerk.clerk08_uuid + ',' + biz_so_out_clerk.clerk09_uuid
				)
		)
	WHEN biz_so_out_clerk.num = 10 THEN
		(
			SELECT
				group_concat(sys_user.`name`)
			FROM
				sys_user
			WHERE
				sys_user.uuid IN (
					biz_so_out_clerk.clerk01_uuid + ',' + biz_so_out_clerk.clerk02_uuid + ',' + biz_so_out_clerk.clerk03_uuid + ',' + biz_so_out_clerk.clerk04_uuid + ',' + biz_so_out_clerk.clerk06_uuid + ',' + biz_so_out_clerk.clerk07_uuid + ',' + biz_so_out_clerk.clerk08_uuid + ',' + biz_so_out_clerk.clerk09_uuid + ',' + biz_so_out_clerk.clerk10_uuid
				)
		)
	END AS clerk_names
	FROM
		biz_so_detail
	INNER JOIN sys_sku ON biz_so_detail.sku_uuid = sys_sku.uuid
	LEFT JOIN biz_so_out_clerk ON biz_so_out_clerk.biz_so_out_detail_uuid = biz_so_detail.uuid
	LEFT JOIN base_warehouse ON base_warehouse.uuid = biz_so_detail.warehouse_uuid
	LEFT JOIN core_serial_info ON core_serial_info.uuid = biz_so_detail.serial_info_uuid
) biz_so_detail_01 ON biz_so_detail_01.biz_so_uuid = biz_so.uuid
LEFT JOIN sys_member ON sys_member.uuid = biz_so_out.member_uuid
LEFT JOIN sys_user AS sys_user_01 ON biz_so_out.update_user_uuid = sys_user_01.uuid
LEFT JOIN sys_user AS sys_user_02 ON biz_so_out.audit_user_uuid = sys_user_02.uuid