表a
------------
aid af
----------------
1 a
2 1a2
3 1a3
3 1a3++++++++++++++++++++++++++
表b
------------------------
bid bf
-----------------
2 2b2
3 2b3
3 2b3
5 dsdw
7 dsd
select tab1.aid,tab1.af,tab2.bid,tab2.bf from tab1 join tab2 on tab1.aid=tab2.bidaid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3得到了让人抓狂的结果??为什么呢?我认为上面的SQL语句应该得到如下结果,为什么却得到了上面的结果呢?aid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
------------
aid af
----------------
1 a
2 1a2
3 1a3
3 1a3++++++++++++++++++++++++++
表b
------------------------
bid bf
-----------------
2 2b2
3 2b3
3 2b3
5 dsdw
7 dsd
select tab1.aid,tab1.af,tab2.bid,tab2.bf from tab1 join tab2 on tab1.aid=tab2.bidaid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3得到了让人抓狂的结果??为什么呢?我认为上面的SQL语句应该得到如下结果,为什么却得到了上面的结果呢?aid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3两两组合就是4条记录,有什么问题吗?
???aid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3
当A表aid = 2时,跟B表一一条对应,
出来
2 1a2 2 2b2
当A表第一条AID = 3 AF = LA3时,跟B表一一条对应,
扫描出来:
3 1a3 3 2b3
3 1a3 3 2b3当A表第二条AID = 3 AF = LA3时,跟B表一一条对应,
扫描出来:
3 1a3 3 2b3
3 1a3 3 2b3这样扫描结果是:
aid af bid bf
-------------------------------
2 1a2 2 2b2
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3
3 1a3 3 2b3 你觉得有错吗?
CREATE TABLE TBTEST1(aid INT, af CHAR(5))
INSERT TBTEST1
SELECT 1 ,'a ' UNION
SELECT 2, '1a2' UNION
SELECT 3, '1a3'
INSERT TBTEST1 VALUES( 3, '1a3')
CREATE TABLE TBTEST2(Bid INT, Bf CHAR(5))
INSERT TBTEST2
SELECT 2 ,'2b2' UNION
SELECT 3, '2b3' UNION
SELECT 5, 'dsdw'
INSERT TBTEST2 VALUES(3,'2b3')
SELECT TB1.*,TB2.BF FROM (SELECT DISTINCT * FROM TBTEST1 WHERE AID IN (
SELECT DISTINCT AID FROM TBTEST1 JOIN TBTEST2 ON AID=BID))AS TB1JOIN(SELECT * FROM TBTEST2 WHERE BID IN (
SELECT DISTINCT AID FROM TBTEST1 JOIN TBTEST2 ON AID=BID))AS TB2ON TB1.AID=TB2.BID
2 1a2 2b2
3 1a3 2b3
3 1a3 2b3
书名 《SQL语言艺术》作者: [美] Stéphane Faroult, Peter Robson 著 温 昱 靳向阳 译
ISBN: 9787121058349
定价: ¥58
出版社: 电子工业出版社
出版时间:2008年3月
1、查询“”课程比“”课程成绩高的所有学生的学号;
SELECT a.S# FROM (SELECT s#,score FROM SC WHERE C#='001') a,(SELECT s#,score
FROM SC WHERE C#='002') b
WHERE a.score>b.score AND a.s#=b.s#;
2、查询平均成绩大于分的同学的学号和平均成绩;
SELECT S#,avg(score)
FROM sc
GROUP BY S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
SELECT Student.S#,Student.Sname,count(SC.C#),sum(score)
FROM Student left Outer JOIN SC on Student.S#=SC.S#
GROUP BY Student.S#,Sname
4、查询姓“李”的老师的个数;
SELECT count(distinct(Tname))
FROM Teacher
WHERE Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
SELECT Student.S#,Student.Sname
FROM Student
WHERE S# not in (SELECT distinct( SC.S#) FROM SC,Course,Teacher WHERE SC.C#=Course.C# AND Teacher.T#=Course.T# AND Teacher.Tname='叶平');
6、查询学过“”并且也学过编号“”课程的同学的学号、姓名;
SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT S#,Sname
FROM Student
WHERE S# in (SELECT S# FROM SC ,Course ,Teacher WHERE SC.C#=Course.C# AND Teacher.T#=Course.T# AND Teacher.Tname='叶平' GROUP BY S# having count(SC.C#)=(SELECT count(C#) FROM Course,Teacher WHERE Teacher.T#=Course.T# AND Tname='叶平'));
8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;
SELECT S#,Sname FROM (SELECT Student.S#,Student.Sname,score ,(SELECT score FROM SC SC_2 WHERE SC_2.S#=Student.S# AND SC_2.C#='002') score2
FROM Student,SC WHERE Student.S#=SC.S# AND C#='001') S_2 WHERE score2 <score;
9、查询所有课程成绩小于分的同学的学号、姓名;
SELECT S#,Sname
FROM Student
WHERE S# not in (SELECT Student.S# FROM Student,SC WHERE S.S#=SC.S# AND score>60);
10、查询没有学全所有课的同学的学号、姓名;
SELECT Student.S#,Student.Sname
FROM Student,SC
WHERE Student.S#=SC.S# GROUP BY Student.S#,Student.Sname having count(C#) <(SELECT count(C#) FROM Course);
11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名;
SELECT S#,Sname FROM Student,SC WHERE Student.S#=SC.S# AND C# in SELECT C# FROM SC WHERE S#='1001';
12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名;
SELECT distinct SC.S#,Sname
FROM Student,SC
WHERE Student.S#=SC.S# AND C# in (SELECT C# FROM SC WHERE S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update SC set score=(SELECT avg(SC_2.score)
FROM SC SC_2
WHERE SC_2.C#=SC.C# ) FROM Course,Teacher WHERE Course.C#=SC.C# AND Course.T#=Teacher.T# AND Teacher.Tname='叶平');
14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT S# FROM SC WHERE C# in (SELECT C# FROM SC WHERE S#='1002')
GROUP BY S# having count(*)=(SELECT count(*) FROM SC WHERE S#='1002');
建议看下
小梁的这篇 Blog
http://blog.csdn.net/liangCK/archive/2008/10/08/3031558.aspx
上面贴出了一部分