是否可以使用临时列WHERE子句
- 我想在
RequestStatus_hod
为IN PROCESS
- 但查询似乎显示所有的数据通过忽略where子句
- 当你只显示数据可以看到,我试图重复
WHERE RequestStatus_hod = 'IN PROCESS'
但它只是显示为NULL
- 我试图使用临时列
RequestStatus_hods
并获得an invalid column error
SQL语句:是否可以使用临时列WHERE子句
SELECT DISTINCT
A.RequestNumber, A.EmployeeId,
STUFF((SELECT ', ' + CAST(B.RequestDetailsId AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS RequestDetailIds,
STUFF((SELECT ', ' + CAST(B.StockId AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS StockIds,
STUFF((SELECT ', ' + CAST(B.RequestQuantity AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS RequestQuantity,
STUFF((SELECT ', ' + G.ItemName
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
JOIN [dbo].[ITEM_MASTER] G ON F.ItemId = G.ItemId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS ItemNames,
STUFF((SELECT ', ' + CAST(F.StockQuantity AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS AvailableQuantity,
STUFF((SELECT ', ' + B.RequestStatus_hod
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS RequestStatus_hods -- Temporary Column --
FROM
[dbo].[REQUISITION] A
JOIN
[dbo].[USER] D ON D.EmployeeId = A.EmployeeId
JOIN
[dbo].[REQUISITION_DETAILS] E ON E.EmployeeId = D.EmployeeId
WHERE
RequestStatus_hods = 'IN PROCESS' -- Col RequestStatus_hod FROM table REQUISITION_DETAILS --
结果:
一种解决方案是重复WHERE
子句中的临时列的定义。 SQL Server不支持直接在WHERE
中使用别名,但它仍然足够智能(在大多数情况下)可以确定不需要对表达式进行两次评估。因此,不要在WHERE
中写入RequestStatus_hod = 'IN PROCESS'
,而要将该列的实际定义复制并粘贴到RequestStatus_hods
的位置。
但是,更好的选择可能是将您的列提取到APPLY
子句中。这样,你肯定知道它不会运行两次。
例如:
SELECT DISTINCT
A.RequestNumber, A.EmployeeId,
STUFF((SELECT ', ' + CAST(B.RequestDetailsId AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS RequestDetailIds,
STUFF((SELECT ', ' + CAST(B.StockId AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS StockIds,
STUFF((SELECT ', ' + CAST(B.RequestQuantity AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS RequestQuantity,
STUFF((SELECT ', ' + G.ItemName
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
JOIN [dbo].[ITEM_MASTER] G ON F.ItemId = G.ItemId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS ItemNames,
STUFF((SELECT ', ' + CAST(F.StockQuantity AS VARCHAR(255))
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
JOIN [dbo].[STOCK] F ON F.StockId = B.StockId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId -- AND RequestStatus_hod = 'IN PROCESS'
FOR XML PATH ('')), 1, 2, '') AS AvailableQuantity,
xx.RequestStatus_hods -- Temporary Column --
FROM
[dbo].[REQUISITION] A
JOIN
[dbo].[USER] D ON D.EmployeeId = A.EmployeeId
JOIN
[dbo].[REQUISITION_DETAILS] E ON E.EmployeeId = D.EmployeeId
CROSS APPLY
(SELECT STUFF((SELECT ', ' + B.RequestStatus_hod
FROM [dbo].[REQUISITION] C
JOIN [dbo].[REQUISITION_DETAILS] B ON C.RequestDetailsId = B.RequestDetailsId
WHERE C.RequestNumber = A.RequestNumber AND C.EmployeeId = A.EmployeeId
FOR XML PATH ('')), 1, 2, '') as RequestStatus_hods) xx
WHERE
xx.RequestStatus_hods = 'IN PROCESS' -- Col RequestStatus_hod FROM table REQUISITION_DETAILS --
Theres两个' xx.RequestStatus_hods':'CROSS APPLY'后的'STUFF'上无法绑定多部分标识符** AND **:'无效的对象名称STUFF' – 2014-12-04 07:47:18
@AthirahHazira对不起,错过了一小块!它。请再试一次,请 – Ruslan 2014-12-04 07:53:07
好的,工作!谢谢 :') – 2014-12-04 08:04:01
尝试
WHERE E.RequestStatus_hod = 'IN PROCESS'
将无法工作要么 – 2014-12-04 05:45:38
完全取出WHERE
条款,并尝试这而不是
JOIN
[dbo].[REQUISITION_DETAILS] E
ON E.EmployeeId = D.EmployeeId
AND E.RequestStatus_hod = 'IN PROCESS'
仍然不会工作 – 2014-12-04 05:44:38
好吧首先,让我们叫的东西与他们的名字,RequestStatus_hods是不是“临时列”,这是摆在查询的结果集的列与计算别名“RequestStatus_hods”。
我并不真正理解那些涉及CROSS JOINS的解决方案,所以我无法评估它们,但我估计在这些情况下性能并不是最好的。
我对这类问题有两种不同的方法 1.-而不是在where子句中的“RequestStatus_hods”,使用完全相同的表达式,您正在使用该表达式来构建列中的值。它会工作,但如果结果集包含大量记录,则性能将受到影响。 2:我包围查询如下
SELECT * FROM (<YOUR-QUERY>) AS
RESULTSET WHERE RESULTSET.[RequestStatus_hods] = 'IN PROCESS'
所以您的查询变为内,由临时表(取决于谁的引擎解析查询),但效果是,你必须要与表你想要的领域。这种方法的表现通常不是很糟糕。
希望这会有所帮助。
它是** RequestStatus_hods **还是** RequestStatus_hod **,因为您的临时列说RequestStatus_hods,但您的where条件说RequestStatus_hod – CodingDefined 2014-12-04 04:39:32
@CodingDefined其实我想过滤临时列'RequestStatus_hods'。 'RequestStatus_hod'是表'REQUISITION_DETAILS'的原始列 – 2014-12-04 04:40:54
在最后一次选择的条件中添加条件'B.RequestStatus_hod ='IN PROCESS''。 – CodingDefined 2014-12-04 04:45:09