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

##左连接 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