1.多表联表查询
select a.category_id ,a.product_id,b.product_class_id,b.name,c.name,c.value,d.sku
from t_sku as d
left join t_product_category as a on a.product_id = d.product_id
left join t_product as b on a.product_id = b.id
left join t_product_attribute_value as c on a.product_id = c.product_id
where c.value = "2-4" and a.category_id in (12,17,18,19) and b.name = "c2m4" and d.sku in ("0","1","2");
结果：
image.png
2.多行转多列
使用场景：商品表字段不满足，需要商品属性扩展表配合,但是商品属性扩展表字段是属于键值对属性类型（比如：product_id,product_addtional_key,product_addtional_value）,addtional_key为icon，addtional_value为icon的url，addtional_key为picture，addtional_value为picture的url，需要同时获取icon，picture属性。
image.png
select product_id,max(case type when 1 then url else "" end ) icon,
max(case type when 2 then url else "" end) picture from t_product_media group by product_id;
结果：
image.png
3.多表联查及多行属性转多字段
select a.category_id ,a.product_id,b.product_class_id,b.name,c.name,c.value,d.sku,e.icon,e.picture
from t_sku as d
left join t_product_category as a on a.product_id = d.product_id
left join t_product as b on a.product_id = b.id
left join t_product_attribute_value as c on a.product_id = c.product_id
left join (
select product_id,
max(case type when 1 then url else "" end ) icon,
max(case type when 2 then url else "" end) picture
from t_product_media group by product_id ) as e on e.product_id = a.product_id
where c.value = "2-4" and a.category_id in (12,17,18,19) and b.name = "c2m4" and d.sku in ("0","1","2");
结果：
image.png
4.SHA256数据完整性校验字段check_code
update t_enterprise_info as a
left join t_relation_user_enterprise as b on a.enterprise_name = b.enterprise_name
set a.check_code = sha2(concat_ws("",b.user_name,a.enterprise_name,a.credit_code,a.legalperson_name,a.legalperson_idcard_num,b.user_name),256) where a.id > 0;
结果：
image.png
concat_ws函数说明：
CONCAT_WS(separator,str1,str2,…)
说明
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
5.mysql关于递归树的实现方法：
定义mysql函数，具体方法如下：
DELIMITER
use linjiashop;
drop function if exists `getUnitChildList`;
CREATE DEFINER=`linjiashop`@`%` FUNCTION `getUnitChildList`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sChildList VARCHAR(1000);
DECLARE sChildTemp VARCHAR(1000);
SET sChildTemp = rootId;
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
SET sChildList = CONCAT(sChildList,',',sChildTemp);
ELSE
SET sChildList = CONCAT(sChildTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildTemp FROM t_shop_user WHERE FIND_IN_SET(parent_id,sChildTemp)>0;
END WHILE;
RETURN sChildList;
END
DELIMITER
6.mysql参数拼接的模糊匹配：
SELECT * FROM order_goods as og left join t_order as orders on og.order_id = orders.id
where orders.order_from = #{payType,jdbcType=VARCHAR}
and (og.good_id like "${goodsId}|%" or og.good_id like "%|${goodsId}" or og.good_id = #{goodsId});
7.主表是一对多的left join重复数据问题：
1.DISTINCT进行去重，效果确实能够达到且立竿见影，
2.在语句末尾添加group by b_id来实现同样的效果。
3.on中最好能保证一对一
原因：后来发现，是自己join的c表没有关联主键或者说是value唯一性的字段。
通过对该表以该字段查询后发现确实存在两行数据，换言之，如果在c表中查询该字段对应的数据后
即SELECT * FROM c WHERE c.network_id = '123456789';
结果显示数据有n行，那么根据笛卡尔积，left join 将会产生m * n条数据，会重复n倍。
8.关于批量操作的实现方式：
1.通过mybatis的foreach标签来实现批量插入。
可能遇到的问题：mysql自带的批量处理，不能一次处理太多数据
错误主要关注点：
Packet for query is too large (89,277,988 > 67,108,864)
setting the ‘max_allowed_packet’ variable.
原因：mysql对sql语句默认有闲置大小，具体可以通过下面的语句在mysql中新建查询进行查询。
show variables like 'max_allowed_packet'
2.SqlSession批量处理
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
WorkerMapper workerMapper1 = sqlSession.getMapper(WorkerMapper.class);
int i = 0;
for (Worker worker : workers) {
workerMapper1.updateByPrimaryKeySelective(worker);
if (i % 1000 == 999) {
sqlSession.commit();
sqlSession.clearCache();
}
i++;
}
//未满足的提交一次
sqlSession.commit();
sqlSession.clearCache();
Error Code: 1093. You can't specify target table 'nog' for update in FROM clause
出现错误的原因是因为修改的表和查询的表是同一个表，MySQL是不允许这样做的，我们可以通过中间再查询一次来解决：
update car set tag = 1 where id in (select id from (select id from car where brand_id=182 and tag=0) As temp);
同一张表根据某个字段分组统一其他的某一个字段
update cmp_order.order_goods as nog
left join cmp_order.t_order as ord on nog.order_id = ord.id
left join (select * from (select json_extract(extend_message,'$.resourceId') as resourceId,good_id from cmp_order.order_goods where id in ( select id from (select min(id) as id from cmp_order.order_goods where extend_message->'$.resourceId' is not null group by extend_message->'$.resourceId') as tr )) as t ) as nt on nt.resourceId = nog.extend_message->'$.resourceId'
set nog.good_id = nt.good_id
where ord.order_type = 1 and ord.id = 1625;
explain
联表查询时，where字段名可以不加表前缀
注意：这种情况需要保证字段名在联表时无重复。
select b.id, b.cert_number, b.user_name, b.worker_name, b.is_passed, b.failed_reason, b.auth_type, b.enterprise_name, b.credit_code, b.registered_address, b.businesslicense, b.legalperson_name, b.legalperson_idcard_num, b.legalperson_idcard_front, b.legalperson_idcard_back, b.contact_name, b.contact_idcard_num, b.contact_idcard_front, b.contact_idcard_back, b.created_time, b.reviewed_time,a.real_name AS worker_real_name
from t_auth_info_record as b
left join t_worker as a on b.worker_name = a.name
WHERE (user_name = "test0716a" ) order by id
