数据库复杂sql如何编写入手

最新推荐文章于 2024-07-25 10:28:23 发布

小杨互联网

最新推荐文章于 2024-07-25 10:28:23 发布

阅读量1.4k

点赞数 1

分类专栏：数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_39751120/article/details/128258805

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言：说到数据库我想大家都不陌生，对主流的数据库都会基本使用，但是要写好sql完成复杂的sql编写是需要对数据库原理，sql脚本语法有一定的了解的，但是对于开发人员来说，平常都是在curd写一些业务代码，数据库接触的也不是那么复杂，对于复杂的业务场景，面对sql显然束手无策，对于后端开发人员来说去看一个几百行，几千行的sql实在头大，

出现这种sql的业务场景：

1，数据库多实例跨库查询，数据不同步，无法实现，主从同步实现。

2，数据库业务比较老，产品从多少年迭代过来的，发展成那种能不动就不动的业务代码或者sql.

3,业务本身就复杂，没有选择互联网产品，比如做一些数据分析，数据统计，你不采用大数据，一是成本高，企业成本也是有预算。

4，复杂的业务，比如金融，电商，需要做业务清算的都比较复杂。

5，多端的业务统计汇总，整个系统的业务统计。

以上几种情况都会出现业务负责，涉及到数据库统计计算的时候其实都是很复杂的，我们对于业务的发展没有好的规划和排期就会导致这样的问题发生，业务的复杂度不应该在数据存储上面，显然出现这样的情况只有一种原因，那就是设计不够量化，设计不够到位，没有一个闭环的业务，设计不够好，不能完全实现拓展和移植。

小杨今天就教大家如何写好一个复杂的sql，首先小杨数据库其实不算很精，但是对于一些复杂的sql我也是束手无策，但是呢你学习了程序，是有逻辑思想，所以你要通过排除和分析，对比，逆向思维去思考这个问题，分析这个问题的本质，当然了这个是身经百战才能有的能力，只有经过了大项目的压力，抗住了太多的压力才能不慌不忙去分析和解决问题。

今天就来分享一下小杨写这种复杂的sql如何写：

我们现在有个跨数据库实例的查询，多个库查询，我这边需要统计业务的答题做题数据，比如问卷星的题目和答案，我们通过送权益的方式兑换礼品，当然这是我们售后做满意度调查都会送成长值。当然我们的渠道有app,微信公众号，小程序，消息通知等。我们统计报表客户在哪个渠道答了多少题，分数是多少，答案是多少，我们需要改进的地方和成长值结算，这都是算成本。

显然这俩者完全没有任何关系，你在渠道答题和我问卷题目有啥关系，但是这是业务链路，比较长的链路了，我们可以答完题抽奖送积分。

我们通过任务触发去关联我们答卷的消息，把题目展示出来就行了。

LEFT JOIN (
	SELECT
		MAX( result ) result,
		CARE_TARGET_ID 
	FROM
		(
		SELECT
		CASE
				
			WHEN
				tc.SEND_RESULT LIKE CONCAT( '%', '"code":"200"', '%' ) THEN
					1 ELSE 0 
				END result,
	tt.CARE_TARGET_ID 
FROM
	database.tt_job tt
	LEFT JOIN database.tt_job_channel tc ON tc.`CARE_TARGET_ID` = tt.`CARE_TARGET_ID` 
	) 
GROUP BY
	CARE_TARGET_ID 
	) c1 ON c1.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r1.number,
		r1.`ANSWER_TEXT`,
		r1.`ID`,
		r1.`TITLE`,
		r1.`CARE_TARGET_ID`,
		r1.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type ='51291006'
		ORDER BY
			td.q_index ASC 
		) r1 
	WHERE
		r1.number = 1 
	) t1 ON t1.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r2.number,
		r2.`ANSWER_TEXT`,
		r2.`ID`,
		r2.`TITLE`,
		r2.`CARE_TARGET_ID`,
		r2.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null 
			AND tt.bill_type ='51291006' 
		ORDER BY
			td.q_index ASC 
		) r2 
	WHERE
		r2.number = 2 
	) t2 ON t2.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r3.number,
		r3.`ANSWER_TEXT`,
		r3.`ID`,
		r3.`TITLE`,
		r3.`CARE_TARGET_ID`,
		r3.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type= '51291006'
		ORDER BY
			td.q_index ASC 
		) r3 
	WHERE
		r3.number = 3 
	) t3 ON t3.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r4.number,
		r4.`ANSWER_TEXT`,
		r4.`ID`,
		r4.`TITLE`,
		r4.`CARE_TARGET_ID`,
		r4.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type = '51291006' 
		ORDER BY
			td.q_index ASC 
		) r4 
	WHERE
		r4.number = 4 
	) t4 ON t4.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r5.number,
		r5.`ANSWER_TEXT`,
		r5.`ID`,
		r5.`TITLE`,
		r5.`CARE_TARGET_ID`,
		r5.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type = '51291006'  
		ORDER BY
			td.q_index ASC 
		) r5 
	WHERE
		r5.number = 5 
	) t5 ON t5.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r6.number,
		r6.`ANSWER_TEXT`,
		r6.`ID`,
		r6.`TITLE`,
		r6.`CARE_TARGET_ID`,
		r6.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type  ='51291006' 
		ORDER BY
			td.q_index ASC 
		) r6 
	WHERE
		r6.number = 6 
	) t6 ON t6.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r7.number,
		r7.`ANSWER_TEXT`,
		r7.`ID`,
		r7.`TITLE`,
		r7.`CARE_TARGET_ID`,
		r7.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type ='51291006'
		ORDER BY
			td.q_index ASC 
		) r7 
	WHERE
		r7.number = 7 
	) t7 ON t7.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r8.number,
		r8.`ANSWER_TEXT`,
		r8.`ID`,
		r8.`TITLE`,
		r8.`CARE_TARGET_ID`,
		r8.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type ='51291006' 
		ORDER BY
			td.q_index ASC 
		) r8 
	WHERE
		r8.number = 8 
	) t8 ON t8.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r9.number,
		r9.`ANSWER_TEXT`,
		r9.`ID`,
		r9.`TITLE`,
		r9.`CARE_TARGET_ID`,
		r9.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null
			AND tt.bill_type = '51291006' 
		ORDER BY
			td.q_index ASC 
		) r9 
	WHERE
		r9.number = 9 
	) t9 ON t9.CARE_TARGET_ID = t.`CARE_TARGET_ID`
	LEFT JOIN (
	SELECT
		r10.number,
		r10.`ANSWER_TEXT`,
		r10.`ID`,
		r10.`TITLE`,
		r10.`CARE_TARGET_ID`,
		r10.`persion_id` 
	FROM
		(
		SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text !=null
			AND tt.bill_type = '51291006'
		ORDER BY
			td.q_index ASC 
		) r10 
	WHERE
		r10.number = 10 
	) t10 ON t10.CARE_TARGET_ID = t.`CARE_TARGET_ID` 
	AND t.bill_type = '51291006' 
WHERE
	1 = 1 
	AND p.care_type_code = '70851005'

扎眼一看这个sql特别长是不是，这只是一部分我不全部展示了，通过分析10个子查询，对应的题目和答案通过函数编号去标记题目信息。

分层查询，嵌套：

第一层：

	SELECT
			td.`TITLE`,
			ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,
			td.`ANSWER_TEXT`,
			td.`ID`,
			tt.`CARE_TARGET_ID`,
			tp.`persion_id` 
		FROM
			database.tt_job tt
			LEFT JOIN database.tc_answer tp ON tp.`persion_id` = tt.`ANSWER_ID`
			LEFT JOIN database.tc_answer_detail td ON td.person_id = tp.id 
		WHERE
			td.title != '题目' 
			AND td.answer_text != null 
			AND tt.bill_type = '51291006'
		ORDER BY
			td.q_index ASC

ROW_NUMBER ( ) over ( PARTITION BY tp.`PERSION_ID` ORDER BY td.q_index ) AS number,

函数根据id分组且下标排序，然后获取到 id排序取指定数据的编号把数据返回

第二层：

SELECT
		r1.number,
		r1.`ANSWER_TEXT`,
		r1.`ID`,
		r1.`TITLE`,
		r1.`CARE_TARGET_ID`,
		r1.`persion_id` 
	FROM
		(

) r1 WHERE
		r1.number = 1

第二层数据就简单了根据第一层的数据返回的赋值到外层了，显然我们获取指定r1,number的那条数据对不对

第三层：

LEFT JOIN (
) t2 ON t2.CARE_TARGET_ID = t.`CARE_TARGET_ID`

第三层无非就是把第二层的统计数据返回到外层然后查询主键关联到外面的表，从而完成了一次嵌套子查询。显然这个sql也没有那么难啊。

当然还有4层5层，78层也很正常。

编写复杂sql的内功心法：

1，拆解复杂sql的逻辑业务，从内到外，这点和程序不一样，程序是从上往下，而sql是从内到外。

2，每一层查询分析，直到外层，把数据反出去。

3，合理运用mysql内置函数，帮助我们分析统计。

4，以功能实现为主，优化性能我们可以后期优化。

————没有与生俱来的天赋，都是后天的努力拼搏（我是小杨，谢谢你的关注和支持）

小杨互联网

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录