iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 22
2
Modern Web

淺談資料庫&ASP.net&C# 入門系列 第 22

[iT鐵人賽Day22]可以做好多事情的SQL子查詢

SQL子查詢

進行多資料表查詢,除了使用join外,也可以使用SQL 子查詢(Subquery),子查詢就是在一個Select指令內再放入一個Select查詢指令進行查詢,通常是位在Select的Where子句,可以透過子查詢取得查詢條件。

子查詢須知

每一個子查詢都是一個Select指令,必須用小括弧包起來,能夠針對不同資料表進行查詢。
如果SQL查詢指令內有子查詢,首先處理的是子查詢條件,然後再依子查詢取得的條件值來處理主查詢,然後就去得最後的查詢結果。

子查詢基本語法:

SELECT 欄位名稱1,欄位名稱2,...,欄位名稱n 
FROM 資料表名稱1
WHERE 欄位名稱 = 
(SELECT 欄位名稱 FROM 資料表名稱2 WHERE 條件)

1.子查詢要在括號()中。
2.通常子查詢SELECT只會取得單一欄位的值,以便主查詢的欄位進行比較運算。
3.如需排序,子查詢不能使用ORDER BY,只能使用GROUP BY 子句。
4.如果子查詢可以取得多筆資料,在主查詢需使用IN邏輯運算子。

在WHERE使用子查詢主要目的是取得其他資料表紀錄的欄位值來建立WHERE句的條件運算式。

範例練習

範例將以下面四個資料表為例。

Customers 客戶資料表

https://ithelp.ithome.com.tw/upload/images/20190923/20119925nCYBxBi9UW.png

Products 產品資料表
https://ithelp.ithome.com.tw/upload/images/20190923/20119925AgVCdm6KNQ.png

orders 訂單資料表

https://ithelp.ithome.com.tw/upload/images/20190923/20119925lSFe9CRxZ8.png

Orderdetial 訂單資訊資料表

https://ithelp.ithome.com.tw/upload/images/20190923/20119925QyVnTkBSDr.png

SQL範例1:

在orders資料表查出CustomerID是apple的訂單資訊有幾筆。

select count( * ) as 訂單數 
from orders
where customerID = (select id from Customers where name = 'apple')

上述使用兩個select,分別查詢orders與Customers資料表,在客戶資料表取得apple的ID後,再從orders資料表計算。結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/201199253N5qVgyr6t.png

SQL範例2:

查出Products資料表,price價格大於平均價格的產品。

select *,(select avg(price) from Products )as 平均值 from Products  where price > (select avg(price) from Products)

子查詢不只是只能放在where後,也可以放在select後面的,當欄位輸出的。
上述SQL的三個select分別查詢相同的資料表,其中一個在select後的子查詢,則為創立一個顯示在資料表不存在的平均值欄位,而在where的子查詢查詢出price平均值後與主查詢where做比較。結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/20119925tL08eF2uq1.png

SQL範例3 EXISTS指令:

EXISTS指令:可以判斷子查詢的結果有沒有回傳資料,若有則會執行查詢中的結果。

從Customers資料表,找出orders和Orderdetail資料表有買ProductID為1的產品的客戶資料。

select * from Customers where exists(select customerID from orders where customerID=Customers.id and (select count(*) from Orderdetial where orderID=orders.id and productID=1 )>0)

上述SQL使用三層巢狀查詢,首先要先將有哪些客戶有訂單編號的資料表找出來(select * from Customers where exists(select customerID from orders where customerID=Customers.id) ,接下來在使用and條件從Orderdetial資料表找出orderID要跟orders的id一樣且 productID要=1的大於0的數量(and (select count(*) from Orderdetial where orderID=orders.id and productID=1 )>0))。
這樣就可以從Customers資料表中查出Orderdetail資料表有買ProductID為1的產品的客戶資料。結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/20119925XLE4ymjGoQ.png

SQL範例4:

使用Orderdetail資料表查出有購買糖果與牛奶糖的訂單資訊。

select * from Orderdetial where exists(select * from Products where (name='糖果' or name='牛奶糖')and id = Orderdetial.productID )

結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/20119925kYTN8YSQpa.png

SQL範例5 IN指令:

IN指令可以檢查是否存在子查詢的資料紀錄中。

從Products與Orderdetail資料表找出訂單編號2沒有購買的products。

select * from Products where id not in (select productID from Orderdetial where orderID=2)

從產品資料表中where條件從id中not in找出不在Orderdetial資料表中orderID=2的productID。結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/20119925NCKtEVchQ4.png

SQL範例6 in的三層巢狀查詢:

從Products、Orderdetial、orders找出order id =1有買了哪些產品。

select * from Products where id in (select productID from Orderdetial where orderID in (select id from orders where id ='1'))

顯示產品的資料表紀錄,在第二層子查詢中找出產品編號,在第三層子查詢找出訂單編號=1的資料,訂單編號1所購買的產品為糖果、餅乾、巧克力糖。結果如下:

https://ithelp.ithome.com.tw/upload/images/20190923/20119925266dwbkH3z.png


上一篇
[iT鐵人賽Day21]SQL語法-in與not in
下一篇
[iT鐵人賽Day23]SQL-INSERT 新增資料的方法
系列文
淺談資料庫&ASP.net&C# 入門36

尚未有邦友留言

立即登入留言