SQL 小结

列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,SC,(
    SELECT SNO FROM SC WHERE SCGRADE<60
    GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME
或
select id,name,AVGS.avgs from student, SC, (
select sid, avg(score) as avgs from SC group by sid
) as AVGS where student.id = SC.sid and student.id = AVGS.sid and SC.score < 60
group by student.id having count(*) >= 2

列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
//以课程为条件进行自连接，然后固定课程，再固定一方学生，按双方比较查出另一方
select s2.sid from SC s1, SC s2 where s1.cid = s2.cid and s1.cid = 1 and s1.sid = 2 and s1.score < s2.score

列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
//以学生为条件进行自连接，固定和比较课程
SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,SC SC2 WHERE SC1.CNO='1' AND SC2.CNO='2' AND SC1.SNO=SC2.SNO AND SC1.SCGRADE>SC2.SCGRADE

查询现有图书中价格最高的图书，输出书名及作者
//函数返回值如此用
SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS)

查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出
//having中可直接用函数
SELECT a.CNO FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME IN(N'计算方法',N'组合数学')
GROUP BY a.CNO HAVING COUNT(*)=2 ORDER BY a.CNO DESC

显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩
select T1.* from test T1,( select avg(smoney) s ,sprovince from test group by sprovince)T2 where T1.smoney > T2.s and T1.sprovince = T2.sprovince;

火箭   胜
火箭   胜
火箭   负
火箭   负
湖人   胜
湖人   负
结果：
       胜  负
火箭   2  2
湖人   1  1
select x user,sum(case y when '胜' then 1 else 0 end) 胜,sum(case y when '负' then 1 else 0 end) 负 from user group by x

field1  field2
A         1
A         2
A         3
用一条sql语句查询成：
field1   all
A        1,2,3,
select field1,group_concat(field2) as all from table group by field1
oracle 10g以上可用wm_concat 等价于mysql 的 group_concat

检索至少定购商品号为‘0001’和‘0002’的顾客号。(用交的方法)  
select c_number from orderiterm where a_number="0001"
intersect
select c_number from orderiterm where a_number="0002"

检索至少定购商品号为‘0001’和‘0002’的顾客号。(用自表连接方法)
                                         
select X. 顾客号 from OrderItem X,OrderItem Y
where X.顾客号=Y.顾客号 and X.商品号='0001' and Y. 商品号='0002';

检索一次定购商品号‘0001’商品数量最多的顾客号和顾客名。
//通过排序取一来求最大最小
select c_number, c_name from customer where c_number in (select c_number from orderiterm where o_number="0001" order by quntity desc limit 1)

检索这样的顾客号,顾客名,他们定购了所有的商品 （除法）      
select Custommer.顾客号,顾客名 from Custommer where not exists
(select * from Article where not exists
(select * from OrderItem
where OrderItem.顾客号=Custommer.顾客号 and OrderItem.商品号=Article.商品号))  
最外层，SELECT CID,CNAME FROM CUSTOMER WHERE，现在，我拿了CUSTOMER里一条记录，要对它来测试，我手上有它的CID信息了，放在一边；
里面，SELECT * FROM ARTICLE WHERE，是拿ARTICLE一条记录，要对它测试，我手上有它的AID，那么，
SELECT * FROM ARTICLE WHERE NOT EXISTS
（SELECT * FROM ORDER WHERE ORDER.CID = CUSTOMER.CID AND ORDER.AID = ARTICLE.AID）
这是什么意思呢，是说对这个AID，已知的CID（外层信息在内层就当常量），没有ORDER记录，
选出来的结果，就是对已知的CID，没有买的东西
现在再套上最外层的NOT EXISTS，就是说，测试这个CUSTOMER记录，只要他没有没买的东西，就选出来
所以最后结果集，就是没有没买的东西的人，也就是买了所有东西的人
简言之，没有没买的东西，这两个“没”，就是那两个“NOT EXISTS”
SELECT XID FROM X WHERE NOT EXIST(
SELECT * FROM Y WHERE NOT EXIST
(SELECT * FROM R WHERE R.XID = X.XID AND R.YID = Y.YID)
)
这是模板


检索这样的顾客号，他们至少订购了顾客号为“0002”所订购的所有商品 （除法）
select id from customer where not exists
(
    select pid from order as o where order.cid = 200 and not exists
    (
        select * from order where order.cid = customer.id and order.pid = o.pid
    )
)
或
select * from customer where not exists
(
    select * from product where not exists
    (
        select * from order where order.cid = customer.id and order.pid = product.pid
    ) and exists
    (
        select * from order where order.cid = 200 and order.pid = product.pid
    )
)