sql-server - 避免用于派生选择中的列的多个重复子查询
问题描述
我有一个由多个子查询组成的视图,这些子查询用于派生选择列表中的列(为了简单起见,我没有指定所有子查询)。我的问题是,编写这样一个包含这么多子查询的查询是完全可以的,还是有更好的方法来重写它以避免它们……任何可以遵循的最佳实践。我尝试查看执行派生查询或 cte 的选项,但由于某种原因,我无法将这部分放在一起。如果可能,我想消除那些重复的子查询。
SELECT a.id,
(
SELECT TOP 1
name
FROM x.dbo.Info l
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND stamp =
(
SELECT MIN(stamp)
FROM x.dbo.Info
WHERE orderno = l.orderno
AND releaseno = l.releaseno
AND status = 'Released'
)
ORDER BY stamp DESC
) [shop_name],
c.line_no,
a.status,
d.family,
(
SELECT TOP 1
name
FROM x.dbo.Info
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND status NOT LIKE 'backflus%'
AND status NOT LIKE 'so%'
ORDER BY stamp DESC
) AS [lastworkplace],
(
SELECT TOP 1
lstatus
FROM x.dbo.Info
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND status NOT LIKE 'backflus%'
AND status NOT LIKE 'so%'
ORDER BY stamp DESC
) AS [laststatus]
FROM BI.dbo.tblz a -- this is a view (not sure if that matters)
LEFT JOIN X.dbo.tblx b
ON b.id = a.salesorder
LEFT JOIN X.dbo.tbls c
ON c.tranid = a.salesorder
AND c.itemid = a.assemblyid
AND c.serialnum = a.ordercode
LEFT JOIN Z.dbo.tbli d
ON d.prodline = LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
WHERE a.id = 'p'
AND
(
LEFT(a.prun, 8) >= '20120101'
OR a.prun IS NULL
)
UNION ALL
SELECT a.id,
(
SELECT TOP 1
name
FROM x.dbo.Info l
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND stamp =
(
SELECT MIN(stamp)
FROM x.dbo.Info
WHERE orderno = l.orderno
AND releaseno = l.releaseno
AND status = 'Released'
)
ORDER BY stamp DESC
) [shop_name],
c.line_no,
a.status,
d.family,
(
SELECT TOP 1
name
FROM x.dbo.Info
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND status NOT LIKE 'backflus%'
AND status NOT LIKE 'so%'
ORDER BY stamp DESC
) AS [lastworkplace],
(
SELECT TOP 1
lstatus
FROM x.dbo.Info
WHERE orderno = a.orderno
AND releaseno = a.releaseno
AND status NOT LIKE 'backflus%'
AND status NOT LIKE 'so%'
ORDER BY stamp DESC
) AS [laststatus]
FROM BI.dbo.tblz a -- this is a view (not sure if that matters)
LEFT JOIN X.dbo.tblx b
ON b.id = a.salesorder
LEFT JOIN X.dbo.tbls c
ON c.tranid = a.salesorder
AND c.itemid = a.assemblyid
AND c.serialnum = a.ordercode
LEFT JOIN Z.dbo.tbli d
ON d.prodline = LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
WHERE a.id = 'm'
AND
(
LEFT(a.prun, 8) >= '20120101'
OR a.prun IS NULL
);
解决方案
您可以使用CTE重写您的选择。它更具可读性。引用文档:
指定一个临时命名结果集,称为公用表表达式 (CTE)。这源自一个简单的查询,并在单个 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可以在 CREATE VIEW 语句中用作其定义的 SELECT 语句的一部分。公共表表达式可以包含对自身的引用。这称为递归公用表表达式。
只是一个简单的示例:
WITH
step1 as
( select a+1 as x, b-1 as y
from t
),
step2 as
( select x*2 as i, y/2 as j
from step1
)
select i+j as r
from step2;
您可以以这种方式链接多个句子。
推荐阅读
- vuejs2 - Vee-validate : 如何在对象构造后附加几个验证规则
- javascript - 使用“x[i].style.padding”将 CSS 填充重新计算为其原始值的一半
- c++ - 将 C 字符串转换为单个整数
- bash - Bash 自动登录强制门户
- actionscript-3 - 在 Actionscript 3.0 中使用 pinchzoom 控制时间线
- javascript - 通过按 ENTER 按钮防止用户多次提交表单
- javascript - 是什么让 JavaScript 函数异步?
- sql - 接受 item_num 并报告其描述、价格和现有数量的 PL/SQL 过程不起作用
- python - 如何在 Python 中隐藏一个值?(显示为 *)
- python - 分析 pandas 中的数据,其中一些值是 dicts 列表