我想查出xgroup表中某一个TEAM_id所对应的所有用户以及管理员,
结果以
userid,isManager 2个字段来表示
如:
当TEAM_id=4时
userid,isManager
8 1
9 0
14 0#建表测试代码
DROP TABLE IF EXISTS `xuser`;
CREATE TABLE `xuser` (
`id` int(11) NOT NULL default '0',
`userid` varchar(20) NOT NULL default '',
`team_id` varchar(1024) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=gbk;INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (1,'zsm','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (2,'laoyang','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (3,'sh','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (4,'xxy','1;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (5,'cc','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (6,'ym','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (7,'yugys','1;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (8,'admin','4;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (9,'test1','4;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (10,'test2','2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (11,'43243','3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (12,'1377449','2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (13,'1555555','2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (14,'1375','4;');DROP TABLE IF EXISTS `xgroup`;
CREATE TABLE `xgroup` (
`TEAM_id` int(11) NOT NULL default '0',
`team_manager` varchar(50) character set utf8 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (1,'4;7;1;3;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (2,'1;2;10;12;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (3,'4;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (4,'8;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (5,'8;');
结果以
userid,isManager 2个字段来表示
如:
当TEAM_id=4时
userid,isManager
8 1
9 0
14 0#建表测试代码
DROP TABLE IF EXISTS `xuser`;
CREATE TABLE `xuser` (
`id` int(11) NOT NULL default '0',
`userid` varchar(20) NOT NULL default '',
`team_id` varchar(1024) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=gbk;INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (1,'zsm','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (2,'laoyang','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (3,'sh','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (4,'xxy','1;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (5,'cc','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (6,'ym','1;2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (7,'yugys','1;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (8,'admin','4;2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (9,'test1','4;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (10,'test2','2;3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (11,'43243','3;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (12,'1377449','2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (13,'1555555','2;');
INSERT INTO `xuser` (`id`,`userid`,`team_id`) VALUES (14,'1375','4;');DROP TABLE IF EXISTS `xgroup`;
CREATE TABLE `xgroup` (
`TEAM_id` int(11) NOT NULL default '0',
`team_manager` varchar(50) character set utf8 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (1,'4;7;1;3;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (2,'1;2;10;12;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (3,'4;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (4,'8;');
INSERT INTO `xgroup` (`TEAM_id`,`team_manager`) VALUES (5,'8;');
解决方案 »
- MySql能实现分布透明性么?
- mysql判断是否存在字段
- navicat中gb2312字段写入中文记录无法保存????
- mysql报错Too high level of nesting for select 求解决
- [MySQL问题]解决 MySQLdb 操作数据库时的中文问题
- 请教一个简单的insert 问题! 字符串中间存在0怎么办?
- 我用ADO连接mysql 5.0,起初用ODBC3.51的驱动,连接正常,但是查询结果中文不显示;后用5.0的驱动,中文可以显示,但是连接速度极慢,请
- 请教:如何智能筛选重复数据?
- 求救。linux10下,安装mysql5.0的问题。
- 计算留存率和留存数的SQL要怎么写呢?
- mysql 数据库 时间点恢复?
- 对在文本框输入模糊查询的问题
select u.id,0 as isManager from xuser u where LOCATE('4;',TEAM_id)<>0
#查询群4中管理员
select u.id,1 as isManager FROM xuser u where (select FIND_IN_SET(u.id,REPLACE(t.team_manager,';',','))
from xgroup t where t.team_id='4')
if(userid='admin',1,0) as isManager
from xuser where LOCATE('4;',TEAM_id)>0
from xuser as u,
(select team_manager
from xgroup
where TEAM_id=4) as g
where instr(team_id,'4;')>0)[align=center]==== 思想重于技巧 ====
[/align]
select id,
if(userid='admin',1,0) as isManager
from xuser where INSTR(TEAM_id,'4;')>0
怎么直接写死userid='admin'了呀???这样不行的to liuyann:
非常感谢,结贴给你!