我们在操作复杂的查询语句时，经常使用的一些语句

矫情吗；* 2022-05-14 08:54 175阅读 0赞

\#\#左连接 left join \#\#  
left join网上都在说是左连接，***A left join B On condition***，A是什么表，B又是什么表，为什么这样写可以，而\*\*\*A inner Join B on condition\*\*\*就不行，或者\*\*\*A right join B On condition\*\*\*就不是合适呢？首先，我们在操作数据表时，要明白的哪张表时主表，哪张表就是A表；哪张表应该作为副表，哪张表就是B表。on之后的条件是副标的条件。如果主表的字段值在副表中找不到对应的字段值，那么返回的字段值就是null，比如\*\*\*有一个业务，统计每个项目下的每期变更表的金额\*\*\*

1.  这涉及三张表，项目表，项目财务科目表，变更表，为什么涉及三张表？变更表中存有项目财务科目的id，项目财务科目表存有项目的id，变更表是主表，左连接项目财务科目表，再左连接项目表
2.  条件：三张表的is\_delete = 0表示该条记录没被删除。同时变更表中的money\_management要为应收款
3.  流程：首先要在变更表中取出每期款项变更的最新金额，此时用到按照项目财务科目的id分组，使用ORDER BY zmc.create\_datetime按照时间降序排序，第一条记录就是最新的金额，使用GROUP\_CONCAT拼接分组后的字符串，再截取没条记录中的字符串的第一个值，再按照项目进行分组

SELECT
    	tmp.projectId as projectId,
      ROUND(SUM(tmp.newProject),2) as money
    FROM
    	(
    		SELECT
    			zmc.financial_subject_id AS financialSubjectId,
    			zp.id AS projectId,
    			SUBSTRING_INDEX(GROUP_CONCAT(zmc.new_project ORDER BY zmc.create_datetime DESC SEPARATOR '<-->'),'<-->',1) AS newProject
    		FROM
    			zq_money_change zmc
    		LEFT JOIN zq_project_financial_subjects zpfs ON (
    			zpfs.is_deleted = 0
    			AND zpfs.id = zmc.financial_subject_id
    		)
    		LEFT JOIN zq_project zp ON (
    			zpfs.is_deleted = 0
    			AND zp.id = zpfs.project_id
    		)
    		WHERE
    			zmc.money_management = 'MONEY_MANAGEMENT_GATHERING' and zp.id is not NULL and zmc.is_deleted = 0
    		GROUP BY
    			zmc.financial_subject_id
    	) AS tmp
    GROUP BY tmp.projectId

## 内连接 inner join ##

A inner join B On condition 表示两张表的交集，表示两张表有公共的部分集合。这种情况我们要慎用。如果A表中的字段A在B表中找不到对应的值，那么就不会返回A表中的这条记录，但有时，我们需要返回字段A所在A表中的该行记录，这时就违背了我们的初衷，因而，我们在使用\*\*\*内连接\*\*\*需要谨慎，避免得不偿失。我们有一个业务，就是统计出账单下的每个材料的销售金额和销售数量。

分析：每条账单都对应很多个材料，比如我们在超市中购买商品所给的发票，每张发票都对应很多个商品，我们如果有很多张发票，统计所有发票中每个商品的销售金额和销售数量。因而，我们需要在账单中暗账材料的id进行分组统计，获得每个商品的数量和材料品类，这个时候连接材料品类表时就得用inner join了，因为如果没有匹配的值，就不返回这个材料品类的名称和这条记录账单的记录。

SELECT DISTINCT
    	zmc.`name` AS MaterialCategoryName,
    	zms.id AS materialSnapshotId,
    	zms.material_name AS materialName,
        zms.note AS note,
    	zms.brand AS brand,
    	zms.version AS version,
    	zms.chroma AS chroma,
    	zms.specifications AS specifications,
    	zms.unit AS unit,
    	oc.orderCounts AS orderCounts,
    	ROUND(IF(oc.salesCount * zms.retail_price is null,0,oc.salesCount * zms.retail_price),2) AS salesAmount
    FROM
    	zq_material_snapshot zms
    INNER JOIN zq_material_category zmc ON (
    	zmc.is_deleted = 0
    	AND zmc.id = zms.material_category_id
    )
    INNER JOIN (
    	SELECT
    		zms1.id AS materialSnapshotId,
    		COUNT(zmap.material_snapshot_id) AS orderCounts,
    		IFNULL(COUNT(zmap.material_snapshot_id),0) * zmap.number AS salesCount
    	FROM
    		zq_material_account_particulars zmap
    	LEFT JOIN zq_material_snapshot zms1 ON (
    		zms1.is_deleted = 0
    		AND zms1.id = zmap.material_snapshot_id
    	)
    	WHERE
    		zmap.is_deleted = 0
    	GROUP BY
    		zmap.material_snapshot_id
    ) AS oc ON oc.materialSnapshotId = zms.id
    WHERE
    	zms.is_deleted = 0
    ORDER BY COALESCE (salesAmount) DESC

## 其他常见的函数\#\# ##

我们在操作数据时，使用复杂的数据逻辑判断时，需要明白各个函数的使用场景。

#### IF判定的使用场景\#\#\#\# ####

IF(expr1,expr2,expr3) 类似于java当中搞得三目运算符，expr1是条件，条件成立返回expr2，否则返回expr3

SELECT IF(zs.basic_salary is NULL ,0.00,zs.basic_salary ) as basic_salary FROM zq_salary zs;

\#\#\#\#IFNULL判定的使用场景 \#\#\#\#

IFNULL(expr1,expr2) expr1既是判定条件，又是返回结果。如果expr1不为null，返回expr1，否则，返回expr2

SELECT IFNULL(zs.basic_salary,0) from zq_salary zs;

\#\#\#\#FIND\_IN\_SET的使用场景 \#\#\#\#  
FIND\_IN\_SET(str,strlist);查询字段(strlist)中包含(str)的结果，返回结果为null或记录

SELECT IFNULL(zs.basic_salary,0) from zq_salary zs WHERE FIND_IN_SET('2','1,2,3');```

\#\#\#\#GROUP\_CONCAT的使用场景 \#\#\#\#  
GROUP\_CONCAT(expr,\[separator ‘分隔符’\]) 拼接分组（也可不分组）后的字段

SELECT
    	GROUP_CONCAT(
    		zmc.id
    		ORDER BY
    			zmc.create_datetime
    	)
    FROM
    	zq_money_change zmc
    GROUP BY
    	zmc.financial_subject_id;

\#\#\#\#concat的使用场景 \#\#\#\#  
CONCAT(str1,str2,…)将多个字符串连接成一个字符串。

SELECT CONCAT('2018','-','12')

\#\#\#\#SUBSTRING\_INDEX的使用场景\#\#\#\#  
SUBSTRING\_INDEX(str,delim,count)截取字符串

SELECT
    	SUBSTRING_INDEX(
    		GROUP_CONCAT(
    			zmc.id
    			ORDER BY
    				zmc.create_datetime SEPARATOR '<-->'
    		),
    		'<-->',
    		1
    	)
    FROM
    	zq_money_change zmc
    GROUP BY
    	zmc.financial_subject_id;

\#\#\#\#COALESCE 使用场景，可以用作排序使用\#\#\#\#  
COALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL

SELECT
    	zs.id,
    	zs.basic_salary
    FROM
    	zq_salary zs
    ORDER BY
    	COALESCE (zs.basic_salary) DESC;

#### ROUND使用场景，保留几位小数\#\#\#\# ####

round(x,d)，x指要处理的数，d是指保留几位小数  
***注意事项***

1.  如果x是整数，不论d取任何数，其返回值都是整数  
    (1)SELECT ROUND(12,2); 返回值是整数12
2.  如果x是小数，小数位的非零数的个数小于d,其保留值就是非零数  
    (1)SELECT ROUND(12.10001,3);小数位的非零数的个数为1，而d为3，其返回的结果是12.1
3.  如果x是小数，小数位的非零数的个数d大于或等于d,其保留值就是四舍五入的值  
    (1)SELECT ROUND(12.125,2); 返回值是12.13  
    (2)SELECT ROUND(12.124,2); 返回值是12.12
4.  针对1和2，我们想要返回其保留的位数，如果小数位的非零数小于d，我希望返回0。此时，我们可以concat来填充  
    (1) SELECT ROUND(CONCAT(12,’’),2);返回值12.00  
    (2) SELECT ROUND(CONCAT(12.100,’’),2); 返回值12.10