I'm trying to use a calculated column in a where clause.
我正在尝试在where子句中使用计算列。
I've trying everything from CROSS APPLY, to sub-query select but it does not give me the anything near what I need.
我已尝试从CROSS APPLY到子查询选择的所有内容,但它并没有给我任何接近我需要的东西。
My query so far:
我的查询到目前为止:
SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
WHERE Sales > 100
GROUP BY p.Code, c.AccountNumber, Sales
This does not work, as 'Sales' is an invalid column
这不起作用,因为'Sales'是无效的列
3 个解决方案
#1
12
You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE
clause (Also, note SUM()
is specified just once, after the multiplication):
您需要将内部查询包装在派生表或CTE中,以便能够在WHERE子句中使用派生列(另外,注意SUM()仅在乘法后指定一次):
SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales
FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;
#2
1
If it's a calculated column you can use "HAVING".
如果它是计算列,您可以使用“HAVING”。
SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber, Sales
HAVING SALES > 100;
#3
1
You can use the common table expression for this
您可以使用公用表表达式
`;WITH CTE AS
(
SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber, Sale
)
SELECT
*
FROM
CTE WHERE CTE.Sales>100 `
#1
12
You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE
clause (Also, note SUM()
is specified just once, after the multiplication):
您需要将内部查询包装在派生表或CTE中,以便能够在WHERE子句中使用派生列(另外,注意SUM()仅在乘法后指定一次):
SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales
FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;
#2
1
If it's a calculated column you can use "HAVING".
如果它是计算列,您可以使用“HAVING”。
SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber, Sales
HAVING SALES > 100;
#3
1
You can use the common table expression for this
您可以使用公用表表达式
`;WITH CTE AS
(
SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c
LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id
LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber, Sale
)
SELECT
*
FROM
CTE WHERE CTE.Sales>100 `