当值同时存储为缩写和全名时，查询以获得国家总数？

Question

我正在编写一个查询，以返回数据库中参与者的状态总数。正常情况下，这应该是非常简单的；但是，我们从客户端迁移的数据最初将状态存储为缩写的2个字母缩写，但是自从接管以来，我们已经标准化地显示了状态的全名。我尝试在语句中使用CASE；但是，我不希望为查询而对系统中存储的每个州/地区进行硬编码，除非这是完成此任务的唯一方法。我的查询贴在下面：

CREATE TABLE #tempstates (STATE_PROVINCE NVARCHAR(100), STATE_ABBREVIATION NVARCHAR(100))

INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('AB', 'AB')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('AE', 'AE')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Alabama', 'AL')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Alaska', 'AK')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('AP', 'AP')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Arkansas', 'AR')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Arizona', 'AZ')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Armed Forces Pacific', 'Armed Forces Pacific')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('BC', 'BC')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('California', 'CA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Colorado', 'CO')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Connecticut', 'CT')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Delaware', 'DE')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('District of Columbia', 'DC')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Florida', 'FL')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('FM', 'FM')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Georgia', 'GA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Hawaii', 'HI')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Idaho', 'ID')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Illinois', 'IL')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Indiana', 'IN')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Iowa', 'IA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Kansas', 'KS')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Kentucky', 'KY')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Louisiana', 'LA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Maine', 'ME')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Maryland', 'MD')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Massachusetts', 'MS')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Michigan', 'MI')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Minnesota', 'MN')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Mississippi', 'MS')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Missouri', 'MO')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Montana', 'MT')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('N/A', 'N/A')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Nebraska', 'NE')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Nevada', 'NV')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('New Hampshire', 'NH')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('New Jersey', 'NJ')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('New Mexico', 'NM')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('New York', 'NY')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('North Carolina', 'NC')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('North Dakota', 'ND')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Ohio', 'OH')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Oklahoma', 'OK')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('ON', 'ON')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Oregon', 'OR')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Pennsylvania', 'PA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Puerto Rico', 'Puerto Rico')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Rhode Island', 'RI')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('South Dakota', 'SD')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('South Carolina', 'SC')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Tennessee', 'TN')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Texas', 'TX')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Utah', 'UT')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Vermont', 'VT')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Virginia', 'VA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Washington', 'WA')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('West Virginia', 'WV')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Wisconsin', 'WI')
INSERT INTO #tempstates (STATE_PROVINCE, STATE_ABBREVIATION) VALUES ('Wyoming', 'WY')

SELECT       ts.STATE_PROVINCE
            ,count(dd.STATE_PROVINCE) as 'State_Count'
FROM DEMOGRAPHIC_DATA dd
JOIN #tempstates ts on dd.STATE_PROVINCE in (ts.STATE_ABBREVIATION, ts.STATE_PROVINCE)
GROUP BY ts.STATE_PROVINCE, dd.STATE_PROVINCE
ORDER BY dd.STATE_PROVINCE, State_Count

DROP TABLE #tempstates

我希望查询只返回显示全名值的状态的计数；但是，也包括它们的缩写版本对应的计数。

编辑:下面的图片描绘了一个例子，我有，亚利桑那州和AZ各有各自的计数，而我希望最终结果产生“亚利桑那47”。我已经更新了我的查询，以包含一个临时表，在其中我将源表加入到其中。我仍然遇到问题，因为输出结果要么是缩写的状态名称，要么是全州名称的计数，而不是如果存在缩写和全名对的情况下合并的总数。

​

Answer 1

您可以创建一个映射表，将两个字母的首字母缩略词映射到要使用的值，然后在查询中加入该表，或者使用它更新值。

CREATE TABLE StateMap (
    Acronym VARCHAR(2) NOT NULL PRIMARY KEY,
    FullName VARCHAR(100)
);

INSERT StateMap (Acronym, FullName)
VALUES ('AK', 'Alaska'),
       ('MI', 'Michigan'),
       ('NY', 'New York')
       ....;

SELECT FullName,
       ,count(FullName) as 'State_Count'
FROM DEMOGRAPHIC_DATA dem
INNER JOIN StateMap sm on sm.Acronym = dem.STATE_PROVINCE;