SQL查询 - 需要一些基本的帮助

I'm in need of some basic TSQL help. Here's my table layout:

我需要一些基本的TSQL帮助。这是我的表格布局：

Orders Table

订单表

SSN
SSN
ZipCode
邮政编码

ZipLookup Table

ZipLookup表

ZipCode
邮政编码
State
州

All columns are varchars.

所有列都是varchars。

How would I get a list of States with the number of distinct SSN's in each state? Preferably, if a particular SSN has orders from multiple states only the state with the most orders would be counted for that SSN.

我如何获得每个州中具有不同SSN数量的国家列表？优选地，如果特定SSN具有来自多个状态的订单，则仅针对该SSN计数具有最多订单的状态。

Thank you for any tips you can give me.

感谢您提供给我的任何提示。

1 个解决方案

#1

First of all, you'd better make sure that you're allowed to be storing SSNs, because there are a whole bunch of privacy laws and regulations that may prohibit it.

首先，你最好确保你被允许存储SSN，因为有一大堆隐私法律和法规可能会禁止它。

The query would look like this:

查询看起来像这样：

SELECT z.State, COUNT(DISTINCT o.SSN) AS SsnCount
FROM ZipLookup z
INNER JOIN Orders o
    ON o.ZipCode = z.ZipCode
GROUP BY z.State

If you need to only count the SSN in its most-frequently-used State:

如果您只需要在最常用的状态下计算SSN：

WITH StateSSNs AS
(
    SELECT
        o.SSN, z.State,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS RowNum
    FROM Orders o
    INNER JOIN ZipLookup z
        ON z.ZipCode = o.ZipCode
    GROUP BY o.SSN, z.State
)
SELECT z.State, COUNT(*) AS SsnCount
FROM ZipLookup z
INNER JOIN StateSSNs s
    ON s.State = z.State
WHERE s.RowNum = 1
GROUP BY z.State

Performance isn't going to be very good, I think this will require at least one full scan and maybe two, but if you want anything better then you'll need to normalize the schema.

性能不会很好，我认为这需要至少一次完整扫描，可能需要两次，但如果你想要更好的东西，那么你需要规范化模式。

#1