Sql的截取关联查询

最近在写sql时，碰到了如下情况，



需求是通过一个账户的id去查询关联的该账户下的所有简历，其中地址字段要通过另一个表进行关联查询。地址字段存的是地址表中对应的编号。

例如210000，210100，210106 代表辽宁省沈阳市铁西区。

通过截取地址字段，分出3个字符串然后进行分别关联查询

语句如下：

插的图片，插代码块的格式我也有点迷


当数据库中该用户下只有一份简历的时候，这条语句是成立的，但如果有多条就会报错

[Err] 1242 – Subquery returns more than 1 row

子查询超过一行以上

原因在于查询条件，当有多份简历时，查询的简历id不唯一

WHERE prt.user_info_id=(
            SELECT pit._id
            FROM person_info_t pit
            WHERE pit.user_id=@pti_user_id)
) 

临时表数据中转 调用过程循环查询

创建一张表作为储存简历的id

drop table if exists tmp_table;  
create table tmp_table (                             -- 创建一张表作为本次储存数据的中转站  也可以用临时表来代替
id INT UNSIGNED NOT NULL auto_increment,             -- id自增
prt_id varchar(64),                                  -- 储存简历id
PRIMARY KEY (id)
); 

• UNSIGNED 表示无符号
• MYSQL中整型范围

从临时表中查找并将简历的id插入到表中,count结果用作循环次数限制

INSERT INTO tmp_table(prt_id) 
SELECT prt. _id FROM person_resume_t prt ,person_info_t pit 
WHERE prt.user_info_id = pit._id 
AND pit.user_id = "98444836924a4c67845da048a77e29d6";
SET @j=0;
SELECT COUNT(*) INTO @j FROM tmp_table;

创建查询过程，在这说下，sql循环是个坑，卡了我好长时间


– mysql 循环在存储过程中才受支持，存储过程外不能用

– mysql 循环在存储过程中才受支持，存储过程外不能用

– mysql 循环在存储过程中才受支持，存储过程外不能用

CREATE PROCEDURE selectPrtInfoByPrtId(IN j int)
BEGIN
DECLARE i INT;
                SET i=1;
                WHILE i<j+1  do
SELECT
prt._id,prt.production_pic_url,prt.video_url,prt.user_info_id,
prt.skill_tag,prt.sex,prt.phone_number,prt.head_pic_url,
prt.introduction,prt.`name`,prt.modify_time,prt.email,
prt.create_time,prt.birth,prt.address,prt.resume_name,
(select re.REGION_NAME 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(prt.address,',',1) 
WHERE prt._id=(   -- 此处拿到简历id
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
)  
as province,
(select re.REGION_CODE 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(prt.address,',',1) 
WHERE prt._id=(
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
) 
as province_id,
(select re.REGION_NAME 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(prt.address,',',2),',',-1) 
WHERE prt._id=(
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
) 
as city,
(select re.REGION_CODE 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(prt.address,',',2),',',-1) 
WHERE prt._id=(
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
) 
as city_id,
(select re.REGION_NAME 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(prt.address,',',-1) 
WHERE prt._id=(
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
) 
as district,
(select re.REGION_CODE 
from region re 
inner join person_resume_t prt 
on re.REGION_CODE = SUBSTRING_INDEX(prt.address,',',-1) 
WHERE prt._id=(
            SELECT tt.prt_id 
            FROM tmp_table tt 
            WHERE tt.id = i)
) 
as district_id
FROM person_resume_t prt
WHERE prt._id=(SELECT tt.prt_id FROM tmp_table tt WHERE tt.id = i)
ORDER BY prt.create_time;

        SET i = i + 1;
        END WHILE;

 END

声明变量i，用作索引来查找临时表中的id，j为传入的限制循环次数，每次查询后返回查询结果，也可以在弄一个表来接收

执行一下

drop table if exists tmp_table;  
create table tmp_table (                            
id INT UNSIGNED NOT NULL auto_increment, 
prt_id varchar(64),
PRIMARY KEY (id)
); 
INSERT INTO tmp_table(prt_id) 
SELECT prt. _id FROM person_resume_t prt ,person_info_t pit 
WHERE prt.user_info_id = pit._id 
AND pit.user_id = "98444836924a4c67845da048a77e29d6";
SELECT COUNT(*) INTO @j FROM tmp_table;
SELECT @j;
CALL selectPrtInfoByPrtId(@j);

返回结果如下




返回三条结果，总结一下：

– sql的循环离不开储存过程的支持，单独拿出来写并不好用

– 变量的作用域问题，尝试时全局变量的值很容易出现问题

– mysql中也没有控制台的概念，整个就是控制台，可以通过select的方式来查看值