遇到一个小问题大概是这样的,有没有老司机帮忙解答一下,就要一个sql语句就行了:
student_A: id name age sex
student_B: id name age sex
student_C: id name age sex
group: gid code(不同的code对应不同的student表)想要通过一个mysql的sql语句就可以实现以下结果:
#IF CODE = A
SELECT * FROM STUDENT_A WHERE ID = GID; (举例:如果已知或者可以取到GID=3,此时结果为张三)
#ELSEIF CODE = B
SELECT * FROM STUDENT_B WHERE ID = GID;(举例:如果已知或者可以取到GID=3,此时结果为李四)
#ELSE CODE = C
SELECT * FROM STUDENT_C WHERE ID = GID;(举例:如果已知或者可以取到GID=3,此时结果为王五)
注:GID为3时,在不同的STUDENT表中的NAME不相同。
student_A: id name age sex
student_B: id name age sex
student_C: id name age sex
group: gid code(不同的code对应不同的student表)想要通过一个mysql的sql语句就可以实现以下结果:
#IF CODE = A
SELECT * FROM STUDENT_A WHERE ID = GID; (举例:如果已知或者可以取到GID=3,此时结果为张三)
#ELSEIF CODE = B
SELECT * FROM STUDENT_B WHERE ID = GID;(举例:如果已知或者可以取到GID=3,此时结果为李四)
#ELSE CODE = C
SELECT * FROM STUDENT_C WHERE ID = GID;(举例:如果已知或者可以取到GID=3,此时结果为王五)
注:GID为3时,在不同的STUDENT表中的NAME不相同。
union all
SELECT * FROM STUDENT_B WHERE ID = GID
union all
SELECT * FROM STUDENT_C WHERE ID = GID
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
CASE WHEN CODE = 'A' THEN (SELECT * FROM STUDENT_A WHERE ID = GID)
WHEN CODE = 'B' THEN (SELECT * FROM STUDENT_B WHERE ID = GID)
WHEN CODE = 'C' THEN (SELECT * FROM STUDENT_C WHERE ID = GID)
ELSE '' END ;
create table studenta
(
id varchar(40),
name varchar(30),
age int,
sex varchar(20)
);
create table studentb
(
id varchar(40),
name varchar(30),
age int,
sex varchar(20)
);
create table studentc
(
id varchar(40),
name varchar(30),
age int,
sex varchar(20)
);
create table group0
(
gid varchar(40),
code varchar(30)
);
INSERT INTO studenta VALUES('1','a1','18','male');
INSERT INTO studenta VALUES('2','a2','19','male');
INSERT INTO studenta VALUES('3','a3','21','male');
INSERT INTO studenta VALUES('4','a4','22','male');
INSERT INTO studenta VALUES('5','a5','23','male');
INSERT INTO studentb VALUES('1','b1','18','male');
INSERT INTO studentb VALUES('2','b2','19','male');
INSERT INTO studentb VALUES('3','b3','21','male');
INSERT INTO studentb VALUES('4','b4','22','male');
INSERT INTO studentb VALUES('5','b5','23','male');
INSERT INTO studentc VALUES('1','c1','18','male');
INSERT INTO studentc VALUES('2','c2','19','male');
INSERT INTO studentc VALUES('3','c3','21','male');
INSERT INTO studentc VALUES('4','c4','22','male');
INSERT INTO studentc VALUES('5','c5','23','male');
INSERT INTO group0 VALUES('1','g1');
INSERT INTO group0 VALUES('2','g2');
INSERT INTO group0 VALUES('3','g3');
INSERT INTO group0 VALUES('4','g3');
INSERT INTO group0 VALUES('5','g2');
INSERT INTO group0 VALUES('6','g1');SELECT * FROM studenta,studentb,studentc,group0;SELECT * FROM studenta,group0 WHERE ID = GID
union all
SELECT * FROM studentb,group0 WHERE ID = GID
union all
SELECT * FROM studentc,group0 WHERE ID = GID想要通过一个mysql的sql语句就可以实现以下结果:
如果code=g1,可以知道g1对应的gid(可以多个,但gid是唯一的),那么只去studenta中查询此时id=gid的所有字段;
如果code=g2,可以知道g2对应的gid(可以多个,但gid是唯一的),那么只去studentb中查询此时id=gid的所有字段;
如果code=g3,可以知道g3对应的gid(可以多个,但gid是唯一的),那么只去studentc中查询此时id=gid的所有字段;
我用的mybatis连接mysql,需要写一个getStudentByIdAndCode()方法,举个例子:
code='g3',gid='4',那么想要的结果就是:
id name age sex
4 c4 22 male
CASE WHEN g.`code` = 'g1' THEN (SELECT * FROM studenta,group0 WHERE id = gid)
WHEN g.`code` = 'g2' THEN (SELECT * FROM studentb,group0 WHERE id = gid)
WHEN g.`code` = 'g3' THEN (SELECT * FROM studentc,group0 WHERE id = gid)
END
FROM group0 g,studenta,studentb,studentc;出错了:::
SQL]SELECT
CASE WHEN g.`code` = 'g1' THEN (SELECT * FROM studenta,group0 WHERE id = gid)
WHEN g.`code` = 'g2' THEN (SELECT * FROM studentb,group0 WHERE id = gid)
WHEN g.`code` = 'g3' THEN (SELECT * FROM studentc,group0 WHERE id = gid)
END
FROM group0 g,studenta,studentb,studentc;
[Err] 1241 - Operand should contain 1 column(s)