Phoenix Tips (7) 子查询

1、IN 和 Not In 的子查询

 

SELECT ItemName
FROM Items 
WHERE ItemID IN 
    (SELECT ItemID
     FROM Orders
     WHERE Date >= to_date('2013/09/02'));

2、Exists 和Not Exists的子查询

SELECT ItemName
FROM Items i
WHERE EXISTS 
    (SELECT *
     FROM Orders
     WHERE Date >= to_date('2013/09/02')
     AND ItemID = i.ItemID);

 

 

 

3、半连接、反连接

见Join

4、比较运算

 

SELECT ID, Name
FROM Contest
WHERE Score >
    (SELECT avg(Score)
     FROM Contest)
ORDER BY Score DESC;

 

 

5、ANY/SOME/ALL 运算

 

SELECT OrderID
FROM Orders
WHERE quantity >= ANY
    (SELECT max(quantity)
     FROM Orders
     GROUP BY ItemID);

6、相关子查询

 

 

SELECT PatentID, Title
FROM Patents p
WHERE FileDate <= ALL
    (SELECT FileDate
     FROM Patents
     WHERE Region = p.Region);

 

7、多重嵌套

 

SELECT ItemID, ItemName
FROM Items i
WHERE NOT EXISTS
    (SELECT *
     FROM Orders
     WHERE CustomerID IN
         (SELECT CustomerID
          FROM Customers
          WHERE Country = ‘Belgium’)
     AND Quantity < 1000
     AND ItemID = i.ItemID)
OR ItemID != ALL
    (SELECT ItemID
     FROM Orders
     WHERE CustomerID IN
         (SELECT CustomerID
          FROM Customers
          WHERE Country = ‘Germany’)
     AND Quantity < 2000);

 

8、衍生表

 

 

SELECT m, count(*) 
FROM 
    (SELECT max(x) m 
     FROM a1 
     GROUP BY name) AS t 
GROUP BY m
ORDER BY count(*) DESC;