主页 / dba / 问题 / 4169
Accepted
yellowblood
Asked: 2011-07-29 00:17:11 +0800 CST

如何在选择（SQL Server）中使用变量？

  • 772

如果我想计算一列并在多于一列中使用结果，如何在不进行两次计算的情况下执行此操作？

例子：

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
      ,hashbytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com') as HashedEmail
FROM sometable

如何避免在不使用多个选择的情况下写两次？

sql-server select

2 个回答

  1. Best Answer

    派生表

    SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    (
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
   ) foo

    或 CTE：

    ;WITH cEMail AS
(
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
)
SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    cEMail

    但是，在这种情况下，我会考虑使用预先计算哈希的计算列

    • 13

  2. 首先，我认为您想编写 hashbytes 而不是 haststring。hashstring 不是 SQL 函数。

    这是我希望你想要的代码。

    DECLARE @yourvariable nvarchar (50) =  '@somedomain.com';

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + @yourvariable  as EMail
      ,HashBytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) +  @yourvariable) as HashedEmail 
FROM sometable;
    • 3

相关问题