sql - 在 SQL 的 WHERE 语句中添加别名

Question

我正在尝试弄清楚如何为州缩写添加别名。

示例:

查询:

SELECT DISTINCT CHAPTER, COUNT(CHAPTER)AS MEMBERS
FROM Name
WHERE MEMBER_RECORD = '1'
AND COUNTRY in ('',' ','United States')
AND CHAPTER IN 
('AL',
'AK',
'AZ')

我想将完整的州名称添加到 AL、AK 和 AZ。

例子:

AND CHAPTER IN 
('AL' AS 'ALABAMA',
'AK' AS 'ALASKA',
'AZ' AS 'ARIZONA')

Answer 1

您可以使用 CASE 表达式:

SELECT  CASE 
            WHEN CHAPTER = 'AL' THEN 'ALABAMA'
            WHEN CHAPTER = 'AK' THEN 'ALASKA'
            WHEN CHAPTER = 'AZ' THEN 'ARIZONA'
        END AS CHAPTER, 
        COUNT(*) AS MEMBERS
FROM Name
WHERE MEMBER_RECORD = '1'
AND COUNTRY in ('',' ','United States')
AND CHAPTER IN ('AL','AK','AZ')
GROUP BY CASE 
            WHEN CHAPTER = 'AL' THEN 'ALABAMA'
            WHEN CHAPTER = 'AK' THEN 'ALASKA'
            WHEN CHAPTER = 'AZ' THEN 'ARIZONA'
         END;

但我建议您创建一个别名表并使用它执行JOIN:

SELECT  A.CHAPTERNAME, 
        COUNT(*) AS MEMBERS
FROM dbo.Name AS N
LEFT JOIN dbo.Aliases AS A
    ON N.CHAPTER = A.CHAPTER
WHERE MEMBER_RECORD = '1'
AND COUNTRY in ('',' ','United States')
AND CHAPTER IN ('AL','AK','AZ')
GROUP BY A.CHAPTERNAME;