使用子字符串条件连接表 [英] Joining Table with substring condition
本文介绍了使用子字符串条件连接表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两张桌子:
create table t1(
id int unsigned not null auto_increment,
group varchar(3) not null,
number int unsigned zerofill,
used enum('YES','NO') default );
id group number used
1 '110' 00001 'YES'
2 '110' 00002 'YES'
3 '110' 00003 'YES'
4 '210' 00001 'YES'
5 '210' 00002 'YES'
6 '210' 00003 'YES'
7 '310' 00001 'YES'
create table t2(
id int unsigned not null auto_increment,
number varchar(13) default null);
id number
1 '110-00001'
2 '110-00002'
3 '210-00002'
4 '310-00001'
我的第一个目标是找到 t1 中未在 t2 中使用的所有记录:
My First Goal is to find every record from t1 that not used in t2:
查询结果:
id group number used
3 '110' 00003 'YES'
4 '210' 00001 'YES'
6 '210' 00003 'YES'
我的第二个目标是将列设置为否":
And my 2nd Goal is to Set the column used to be 'NO':
id group number used
3 '110' 00003 'NO'
4 '210' 00001 'NO'
6 '210' 00003 'NO'
我尝试使用这样的查询:
I have tried to use the query like this :
select * from t1
left outer join t2
on t1.number = cast(substring(t2.number,8,5) as int)
where t2.id is null;
但 mySQL 说:
您的 SQL 语法有错误;检查手册对应于您的 MySQL 服务器版本以使用正确的语法'int) 附近,其中 tblpeserta.id 为空
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) where tblpeserta.id is null
对我的问题有更好的查询吗?!
is there a better query for my problem?!
推荐答案
尝试
UPDATE t1 LEFT JOIN t2
ON t1.`group` = LEFT(t2.number, 3)
AND t1.number = 0 + RIGHT(t2.number, 5)
SET t1.used = 'NO'
WHERE t2.id IS NULL
和选择
SELECT *
FROM t1 LEFT JOIN t2
ON t1.`group` = LEFT(t2.number, 3)
AND t1.number = 0 + RIGHT(t2.number, 5)
WHERE t2.id IS NULL
输出:
| ID | GROUP | NUMBER | USED |
------------------------------
| 3 | 110 | 3 | NO |
| 4 | 210 | 1 | NO |
| 6 | 210 | 3 | NO |
这篇关于使用子字符串条件连接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文