mysql语句记录

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重复数据问题（待续）：