复杂sql语句集锦
本文主要讲一下笔者在工作中遇到的一些逻辑比较复杂的sql语句,下面是具体写法:
SELECT IF ( LOCATE('familyKey', link, 1) = 0, NULL, SUBSTRING( link, LOCATE('familyKey', link, 1) + LENGTH('familyKey') + 1, IF ( LOCATE( '&', link, LOCATE('familyKey', link, 1) ) = 0, LENGTH(link), LOCATE( '&', link, LOCATE('familyKey', link, 1) ) - ( LOCATE('familyKey', link, 1) + LENGTH('familyKey') + 1 ) ) ) ) familyKey FROM illustrations;
这条语句是将illustrations表中的link字段,如:
...&episType=239&familyKey=90897&illustrationId=6837275...
这样的字条串中familyKey=后面的90897这样的编号取出来,说白了就是将字符串按关键字截取,得到的数据可组成新表进行下一步数据分析工作。
我们也可以将其分拆一下,写成下面的语句:
SELECT IF(strbegin=0,NULL,SUBSTRING(link,strbegin+strlen+1,IF(strend=0,LENGTH(link),strend-(strbegin+strlen+1)))) familyKey,mainId FROM ( SELECT mainId,link,strbegin,LOCATE('&',link,strbegin) strend, LENGTH('familyKey') strlen FROM ( SELECT mainId,link,LOCATE('familyKey',link,1) strbegin FROM illustrations ) tmp) tmp;
这样看起来就有条理多了,两条语句中都涉及了IF条件判断这些所谓的动态查询的知识点,写完还是小有成就感。
下面介绍一下mysql5.7中的一大亮点改进——对Json数据类型的支持
CREATE TABLE maingroup_id_parentId AS SELECT n1.id AS parentId,n2.id AS id FROM nav n1,nav n2 WHERE n1.meta->'$.model' = n2.parent_meta->'$.model' AND n1.meta->'$.spec' = n2.parent_meta->'$.spec' AND n1.meta->'$.lang' = n2.parent_meta->'$.lang' AND n1.meta->'$.startup' = n2.parent_meta->'$.startup' AND n1.meta->'$.localMarketOnly' = n2.parent_meta->'$.localMarketOnly' AND n2.nav_table = 'nav-mainGroup-table' AND n1.nav_table <> 'nav-mainGroup-table';
这条语句是将nav表中建立在以model,spec,lang,startup,localMarketOnly这几个关键字为联系的基础上的上下层级关系找出来,建成子表maingroup_id_parentId,然后做进一步数据整理。语句中用到了meta这个Json类型的字段,其使用方式简便、表义清晰,十分适合对类似带数据参数的请求地址的存储。在处理大数据量的表间关系时,上面的用法可能就不实用了,因为效率太差,一条语句可能很久都跑不出来,盲目地等待是没有任何意义的,我是不会做这种事情,所以我们必须找到更高效的关联方案。这里可将这些关键字对应的信息从Json字段中提出来单独作为一个字段,并建立索引,然后将它们进行匹配:
-- pnc字段关键字提取 ALTER TABLE pnc ADD pnc_number VARCHAR(20) generated always AS (json_extract(meta,'$.pnc')) virtual; ALTER TABLE pnc ADD drawingVar VARCHAR(8) generated always AS (json_extract(meta,'$.drawingVar')) virtual; ALTER TABLE pnc ADD drawing VARCHAR(8) generated always AS (meta->"$.drawing") virtual; ALTER TABLE pnc ADD subGroup VARCHAR(8) generated always AS (json_extract(meta,'$.subGroup')) virtual; ALTER TABLE pnc ADD mainGroup VARCHAR(8) generated always AS (json_extract(meta,'$.mainGroup')) virtual; ALTER TABLE pnc ADD spec VARCHAR(150) generated always AS (json_extract(meta,'$.spec')) virtual; ALTER TABLE pnc ADD model VARCHAR(50) generated always AS (json_extract(meta,'$.model')) virtual; -- pnc建索引 ALTER TABLE pnc ADD INDEX link(pnc_number,drawingVar,drawing,subGroup,mainGroup,spec,model); -- 修改bom表 ALTER TABLE bom ADD pnc_number VARCHAR(20) generated always AS (json_extract(parent_meta,'$.pnc')) virtual; ALTER TABLE bom ADD drawingVar VARCHAR(8) generated always AS (json_extract(parent_meta,'$.drawingVar')) virtual; ALTER TABLE bom ADD drawing VARCHAR(8) generated always AS (parent_meta->"$.drawing") virtual; ALTER TABLE bom ADD subGroup VARCHAR(8) generated always AS (json_extract(parent_meta,'$.subGroup')) virtual; ALTER TABLE bom ADD mainGroup VARCHAR(8) generated always AS (json_extract(parent_meta,'$.mainGroup')) virtual; ALTER TABLE bom ADD spec VARCHAR(150) generated always AS (json_extract(parent_meta,'$.spec')) virtual; ALTER TABLE bom ADD model VARCHAR(50) generated always AS (json_extract(parent_meta,'$.model')) virtual; -- bom建索引 ALTER TABLE bom ADD INDEX link(pnc_number,drawingVar,drawing,subGroup,mainGroup,spec,model); -- 造表间关系 DROP TABLE IF EXISTS pnc_bom_id; CREATE TABLE pnc_bom_id SELECT bom.id AS bomId,pnc.id AS pncId FROM nissan_bom bom,nissan_pnc pnc WHERE bom.pnc_number=pnc.pnc_number AND bom.drawingVar=pnc.drawingVar AND bom.drawing=pnc.drawing AND bom.mainGroup=pnc.mainGroup AND bom.subGroup=pnc.subGroup AND bom.spec=pnc.spec AND bom.model=pnc.model;