create table book(
Student varchar(10),
Bookname varchar(10),
Booknumber integer,
Primary key (Student , bookname)
);
查询出每本书超过5的所有学生(Student)高手们帮忙看看 谢谢
Student varchar(10),
Bookname varchar(10),
Booknumber integer,
Primary key (Student , bookname)
);
查询出每本书超过5的所有学生(Student)高手们帮忙看看 谢谢
select student from book
where booknumber >5
booknumber 上加索引
INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('joson', 'oracle', '7');
INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('joson', 'photoshop', '8');INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('jimmy', 'java', '4');
INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('jimmy', 'photoshop', '3');
INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('jimmy', 'photoshop', '6');INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('jack', 'oracle', '3');
INSERT INTO `book` (`Student` ,`Bookname` ,`Booknumber`)VALUES ('jack', 'photoshop', '5');记录是要 学生有所有的书 ,并且每本书都超过5
结果是
student bookname booknumber
joson java 6
joson oracle 7
joson photoshop 8
select * from book
where booknumber >5
就可以实现了...
select student from book where booknumber>5
group by student having count(bookname)=3
)
+---------+-----------+------------+
| Student | Bookname | Booknumber |
+---------+-----------+------------+
| joson | java | 6 |
| joson | oracle | 7 |
| joson | photoshop | 8 |
| jimmy | java | 4 |
| jimmy | photoshop | 3 |
| jack | oracle | 3 |
| jack | photoshop | 5 |
+---------+-----------+------------+
7 rows in set (0.03 sec)mysql> select *
-> from book a
-> where Booknumber>5
-> and not exists (select 1 from book where Student!=a.Student and Bookname not in (select Bookname from book where Student=a.Student));
+---------+-----------+------------+
| Student | Bookname | Booknumber |
+---------+-----------+------------+
| joson | java | 6 |
| joson | oracle | 7 |
| joson | photoshop | 8 |
+---------+-----------+------------+
3 rows in set (0.05 sec)mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖