標量子查詢

今天介紹一種似乎並不常見的查詢方式：標量子查詢。

1. 概念

SQL允許子查詢出現在返回單個值的表達式能夠出現的任何地方，只要該子查詢只返回包含單個屬性的單個元組；這樣的子查詢稱爲標量子查詢（scalar subquery）。例如，下面的這個例子列出所有顧客以及他們擁有的帳戶數：

SELECT customer_name,(SELECT COUNT(*) FROM accout WHERE customer_name = a.customer_name) num_accountsFROM customer a

與下面的查詢結果是一樣的：

SELECT a.customer_name,COUNT(*) num_accountsFROM customer aINNER JOIN accout b ON a.customer_name = b.customer_nameGROUP BY a.customer_name

這樣的查詢要確保只返回一個值而不是多條記錄，不然會產生運行時錯誤。

2. 應用舉例與性能對比

下面舉兩個有稍微有點複雜的查詢需求：

例子一： 查詢每一年加之前年份的積累銷售額。比如查詢2003年的，就是將2003的銷售金額加上以前的銷售金額。

查詢一：

SELECT b.ta_year, SUM(a.ta_num) 銷售金額FROM testA a, testA b   -- 自連接WHERE a.ta_year <= b.ta_yearGROUP BY b.ta_year

查詢二（使用標量子查詢）：

SELECTid,ta_year,(SELECT SUM(ta_num) FROM testA WHERE ta_year <= a.ta_year)  銷售金額FROM testA a

例子二：求實時餘額。

 

查詢一：

SELECTb.卡号,b.日期,b.充值,b.支出,SUM(ISNULL(a.充值,0)) - SUM(ISNULL(a.支出,0)) 余额FROM CardInfo a INNER JOIN CardInfo b ON a.卡号 = b.卡号 AND a.日期<=b.日期GROUP BY b.卡号,b.日期,b.充值,b.支出ORDER BY b.日期

查詢二（使用標量子查詢）：

SELECTa.卡号,a.日期,a.充值,a.支出,(SELECT SUM(ISNULL(充值,0)) - SUM(ISNULL(支出,0)) FROM CardInfo WHERE 卡号 = a.卡号 AND 日期 <= a.日期) 余额FROM CardInfo a

可以看到上面的兩個例子，如果用連接查詢，都要與自身進行連接，比較難懂，而使用標量子查詢則相對更好理解。不過如果在一個查詢中大量使用很長的標量子查詢，會非常影響可讀性。

另外，上面的例子都是查詢二比查詢一性能 快非常多，畢竟查詢一中要group by有隱含的排序操作。