34,575
社区成员
发帖
与我相关
我的任务
分享
select * from ren
where exists (
select 1
from tiyu
where tiyu.`name` like '%球%'
and instr(concat(',', ren.NameIdAdd,','), concat(',',tiyu.Id,','))>0
);
drop table if exists `ren`;
drop table if exists `tiyu`;
CREATE TABLE `ren` (
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`NameIdAdd` VARCHAR(45) NULL,
PRIMARY KEY (`Id`),
UNIQUE INDEX `idRen_UNIQUE` (`Id` ASC));
--
CREATE TABLE `tiyu` (
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(45) NULL,
PRIMARY KEY (`Id`),
UNIQUE INDEX `Id_UNIQUE` (`Id` ASC));
--
INSERT INTO `ren` (`NameIdAdd`) VALUES ('1,3,7,9');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('1,5,8');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('1,10');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('2,5,8');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('1,2,3,9');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('2,4,7,9');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('1,6,8,10');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('3,5,9');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('3,5,7,10');
INSERT INTO `ren` (`NameIdAdd`) VALUES ('4,8,10');
--
INSERT INTO `tiyu` (`Name`) VALUES ('足球');
INSERT INTO `tiyu` (`Name`) VALUES ('篮球');
INSERT INTO `tiyu` (`Name`) VALUES ('引体向上');
INSERT INTO `tiyu` (`Name`) VALUES ('羽毛球');
INSERT INTO `tiyu` (`Name`) VALUES ('1000米');
INSERT INTO `tiyu` (`Name`) VALUES ('100米');
INSERT INTO `tiyu` (`Name`) VALUES ('仰卧起坐');
INSERT INTO `tiyu` (`Name`) VALUES ('乒乓球');
INSERT INTO `tiyu` (`Name`) VALUES ('体前屈');
INSERT INTO `tiyu` (`Name`) VALUES ('排球');
-- 以上为测试表和测试数据
select * from ren
where exists (
select 1
from tiyu
where tiyu.`name` like '%球%'
and instr(concat('%,', ren.NameIdAdd,',%'), concat(',',tiyu.Id,','))>0
);
SELECT DISTINCT
ren.*
FROM
ren
JOIN
renreltiyu
ON ren.id = renreltiyu.renid
JOIN
tiyu
ON tiyuid = tiyu.id
WHERE
tiyu.name LIKE '%球%';
select * from ren
where Id in (select Id from tiyu where name like '%球%')