mysql-奇技淫巧之附属表过滤查询数量

mysql-奇技淫巧

最近参加公司新项目，其中mysql中的一些奇技淫巧吸引了我，利用inner join 表内连接筛选出规定的数据量，话不多说看代码！

创建表 test_1

CREATE TABLE test_1 (
id int(11) NOT NULL,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
salary_data varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

插入一些数据

INSERT INTO test_1 (nick_name, salary_data)
VALUES(
‘小明’,‘1000;2000;3000;4000;5000’
);
INSERT INTO test_1 (nick_name, salary_data)
VALUES(
‘小红’,‘1001;2001;3001’
);
INSERT INTO test_1 (nick_name, salary_data)
VALUES(
‘小张’,‘0011’
);

注意不要向下边这样，差距在最后一位多一个‘ ; ’ 最好复制上边的SQL

创建一个筛量表 new_table

CREATE TABLE new_table (
id int(11) NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

给new_table表插入3个id

like this
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

接下来就可以开始我们的语句了。

SELECT t.id as test_id,t.nick_name as nick_name,SUBSTRING_INDEX( SUBSTRING_INDEX( t.salary_data, ';', new_table.id + 1 ), ";",- 1 ) AS salary_data,new_table.id AS table_id FROM test_1 AS t INNER JOIN new_table ON new_table.id < ( LENGTH( t.salary_data ) - LENGTH( REPLACE ( t.salary_data, ";", "" ) ) + 1 );

这样我们就成功的过滤掉超过2条的薪资数据（小明的薪资4000；5000），用inner join on筛选掉了那两条数据，后来我想如果换成3不是不用这么费劲了吗？结果实践果然是检验真理的唯一标准；

new_table.id 替换成3后，不能匹配其他人的数据。具体原理仍在探索中，现猜测可能是mysql在select时，如果配到数字类型则比较一回就结束了查询吧。当然朋友们如果知道具体原因，麻烦留言告知答案，感激不尽。

这是一个小小的奇技淫巧，一则总结，二则分享，希望看到这里的人没有浪费你的时间。