1、概述
  近维护系统时,客户经常反馈这样一个内容:要在查询列表上加上一个查询条件。这样的要求一点也不过分,并且看起来工作量也不大,不是加一个WHERE条件嘛,但是当调试到DAL层时,立刻石化——遇到在SELECT子句中编写子查询的SQL,
  SELECT SO.order_no,(SELECT SUM(SOD.good_num) FROM [StockOrderDetails] AS SOD WHERE SOD.order_id=SO.id) AS order_num
  FROM [StockOrder] AS SO
  这样的写法缺点如下:
  1>order_num不能在SELECT中继续使用
  2>order_num不能在WHERE子句中使用(SELECT中的子查询不能在WHERE中使用)
  3>性能问题
  这里我们主要讨论第二点:order_num不能在WHERE子句中使用
  2、为什么order_num不能在WHERE子句中使用?
  因为SELECT语句执行的过程是:
  1>. FROM
  2>. WHERE
  3>. GROUP BY
  4>. HAVING
  5>. SELECT
  6>. ORDER BY
  WHERE子句在SELECT子句之前执行,所以在SELECT中的列别名,WHERE中当然是不会认识的。
  3、示例
  业务:采购订单查询列表,需要显示订单的主信息,并且还要显示每一个订单的总的采购数量;
  测试数据:
  不支持可搜索的SQL:
  SELECT SO.order_no,(SELECT SUM(SOD.good_num) FROM [StockOrderDetails] AS SOD WHERE SOD.order_id=SO.id) AS order_num
  FROM [StockOrder] AS SO
  支持可搜索的SQL:
SELECT SO.order_no,ISNULL(SOD.order_num,0) AS order_num
FROM [StockOrder] AS SO
LEFT JOIN
(
SELECT order_id,SUM(good_num) AS order_num FROM [StockOrderDetails]
GROUP BY order_id
) AS SOD ON SO.id=SOD.order_id
WHERE SOD.order_num>=8
  当子查询相当复杂时,可以用WITH AS(子查询部分)来替换 LEFT JOIN部分:
;WITH StockOrderD AS(
SELECT order_id,SUM(good_num) AS order_num FROM [StockOrderDetails]
GROUP BY order_id
)
SELECT SO.order_no,ISNULL(SOD.order_num,0) AS order_num
FROM [StockOrder] AS SO
LEFT JOIN StockOrderD AS SOD ON SO.id=SOD.order_id
WHERE SOD.order_num>=8
  4、总结
  总得来说是将过程性的思维转换为集合的思维。
  本篇文章主要写给刚开始正式写SQL的朋友,在开发时注意一些SQL的写法,可以添加更多的灵活性,降低维护阶段的工作量。