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的写法，可以添加更多的灵活性，降低维护阶段的工作量。
