表结构如下,
CREATE DATABASE if not EXISTS `task_node_case`; use `task_node_case`;DROP TABLE if EXISTS `test_task`;
create table test_task(
`tid` bigint AUTO_INCREMENT,
`tname` varchar(20) not null,
PRIMARY KEY (`tid`)
)charset=utf8;DROP TABLE if EXISTS `node_info`;
create table node_info(
`nid` bigint AUTO_INCREMENT,
`nname` varchar(20) not null,
PRIMARY KEY (`nid`)
)charset=utf8;DROP TABLE if EXISTS `case_info`;
create table case_info(
`cid` bigint AUTO_INCREMENT,
`cname` varchar(20) not null,
PRIMARY KEY (`cid`)
)charset=utf8;DROP TABLE if EXISTS `test_task_node_info`;
create table test_task_node_info(
`id` bigint AUTO_INCREMENT,
`tid` bigint not null,
`nid` bigint not null,
PRIMARY KEY (`id`),
KEY `tid` (`tid`),
CONSTRAINT `test_task_1` FOREIGN KEY (`tid`) REFERENCES `test_task` (`tid`),
KEY `nid` (`nid`),
CONSTRAINT `node_info_1` FOREIGN KEY (`nid`) REFERENCES `node_info` (`nid`)
)charset=utf8;DROP TABLE if EXISTS `node_info_case_info`;
create table node_info_case_info(
`id` bigint AUTO_INCREMENT,
`nid` bigint not null,
`cid` bigint not null,
PRIMARY KEY (`id`),
KEY `nid` (`nid`),
CONSTRAINT `node_info_2` FOREIGN KEY (`nid`) REFERENCES `node_info` (`nid`),
KEY `cid` (`cid`),
CONSTRAINT `case_info_2` FOREIGN KEY (`cid`) REFERENCES `case_info` (`cid`)
)charset=utf8;insert into test_task(`tname`) values ('老师1');
insert into test_task(`tname`) values ('老师2');
select * from test_task;
insert into node_info(`nname`) values ('班级1');
insert into node_info(`nname`) values ('班级2');
select * from node_info;
insert into case_info(`cname`) values ('学生1');
insert into case_info(`cname`) values ('学生2');
select * from case_info;insert into test_task_node_info(`tid`,`nid`) values (1,1);老师1,班级1.
insert into test_task_node_info(`tid`,`nid`) values (2,1);老师2,班级1.select * from test_task_node_info;
insert into node_info_case_info(`nid`,`cid`) values (1,1);班级1,学生1.
insert into node_info_case_info(`nid`,`cid`) values (1,2);班级1,学生2.
select * from node_info_case_info;现在我想根据老师1,班级1,查询出老师1,班级1下的学生ID,请问如何查询,我的数据库设计有没有问题,谢谢。
我的数据是,
老师1,班级1,学生1.
老师2,班级1,学生2.我想要的结果是根据老师ID和班级ID,查询出对应的学生。
CREATE DATABASE if not EXISTS `task_node_case`; use `task_node_case`;DROP TABLE if EXISTS `test_task`;
create table test_task(
`tid` bigint AUTO_INCREMENT,
`tname` varchar(20) not null,
PRIMARY KEY (`tid`)
)charset=utf8;DROP TABLE if EXISTS `node_info`;
create table node_info(
`nid` bigint AUTO_INCREMENT,
`nname` varchar(20) not null,
PRIMARY KEY (`nid`)
)charset=utf8;DROP TABLE if EXISTS `case_info`;
create table case_info(
`cid` bigint AUTO_INCREMENT,
`cname` varchar(20) not null,
PRIMARY KEY (`cid`)
)charset=utf8;DROP TABLE if EXISTS `test_task_node_info`;
create table test_task_node_info(
`id` bigint AUTO_INCREMENT,
`tid` bigint not null,
`nid` bigint not null,
PRIMARY KEY (`id`),
KEY `tid` (`tid`),
CONSTRAINT `test_task_1` FOREIGN KEY (`tid`) REFERENCES `test_task` (`tid`),
KEY `nid` (`nid`),
CONSTRAINT `node_info_1` FOREIGN KEY (`nid`) REFERENCES `node_info` (`nid`)
)charset=utf8;DROP TABLE if EXISTS `node_info_case_info`;
create table node_info_case_info(
`id` bigint AUTO_INCREMENT,
`nid` bigint not null,
`cid` bigint not null,
PRIMARY KEY (`id`),
KEY `nid` (`nid`),
CONSTRAINT `node_info_2` FOREIGN KEY (`nid`) REFERENCES `node_info` (`nid`),
KEY `cid` (`cid`),
CONSTRAINT `case_info_2` FOREIGN KEY (`cid`) REFERENCES `case_info` (`cid`)
)charset=utf8;insert into test_task(`tname`) values ('老师1');
insert into test_task(`tname`) values ('老师2');
select * from test_task;
insert into node_info(`nname`) values ('班级1');
insert into node_info(`nname`) values ('班级2');
select * from node_info;
insert into case_info(`cname`) values ('学生1');
insert into case_info(`cname`) values ('学生2');
select * from case_info;insert into test_task_node_info(`tid`,`nid`) values (1,1);老师1,班级1.
insert into test_task_node_info(`tid`,`nid`) values (2,1);老师2,班级1.select * from test_task_node_info;
insert into node_info_case_info(`nid`,`cid`) values (1,1);班级1,学生1.
insert into node_info_case_info(`nid`,`cid`) values (1,2);班级1,学生2.
select * from node_info_case_info;现在我想根据老师1,班级1,查询出老师1,班级1下的学生ID,请问如何查询,我的数据库设计有没有问题,谢谢。
我的数据是,
老师1,班级1,学生1.
老师2,班级1,学生2.我想要的结果是根据老师ID和班级ID,查询出对应的学生。
帮忙抛一下SQL语句啊,小弟才疏学浅,不会写,
-> from test_task a inner join test_task_node_info b using (tid)
-> inner join node_info_case_info c using(nid)
-> inner join node_info d using(nid)
-> inner join case_info e using(cid);
+-------+-------+-------+
| tname | nname | cname |
+-------+-------+-------+
| 老师1 | 班级1 | 学生1 |
| 老师2 | 班级1 | 学生1 |
| 老师1 | 班级1 | 学生2 |
| 老师2 | 班级1 | 学生2 |
+-------+-------+-------+
4 rows in set (0.00 sec)mysql>
select distinct tname,nname,cname
from test_task a inner join test_task_node_info b on a.tid=b.tid
inner join node_info_case_info c on b.nid=c.nid
inner join node_info d on c.nid=d.nid
inner join case_info e on b.cid=e.cid;