alias WHERE子句来简化查询

Question

在SQL中，您可以对WHERE表达式进行别名以简化查询吗？

示例我有

SELECT * FROM T
WHERE 
(x*y<15 AND x*z > 20)
OR 
(x*y>20 AND x*z < 100)

有没有一种方法可以将x*y表示为a，将x*z表示为b，这样我就可以将查询重写为

SELECT * FROM T
WHERE 
(a<15 AND b>20)
OR 
(a>20 AND b<100)

我在SQL服务器上

Answer 1

您可以尝试这种方式。

SELECT *
FROM
(
    SELECT x, y, z, (x*y) xy, (x*z) xz
    FROM T
) R
WHERE (xy < 15 AND xz > 20) OR (xy > 20 AND xz < 100)

Answer 2

如果使用SQL Server 2005或更高版本，则可以使用公共表表达式：

DECLARE @myTable TABLE([x] INT, [y] INT, [z] INT)
INSERT INTO @myTable VALUES(2, 1, 1)
INSERT INTO @myTable VALUES(3, 2, 2)
INSERT INTO @myTable VALUES(4, 2, 3)
INSERT INTO @myTable VALUES(5, 2, 5)
INSERT INTO @myTable VALUES(6, 4, 5)
;WITH CTE ([x], [y], [z], [xy], [xz]) AS
(
    SELECT x, y, z, x*y, x*z FROM @myTable
)
SELECT x, y, z 
FROM CTE
WHERE 
    (xy < 15 AND xz > 20)
    OR (xy > 20 AND xz < 100)

Answer 3

WITH T1
     AS
     (
      SELECT T.*,
              x * y AS a, 
              x * z AS b
        FROM T
     )
SELECT * FROM T1
WHERE 
(a<15 AND b>20)
OR 
(a>20 AND b<100);