sql - 需要大于 1 比 1 的结果

Question

我有一个查询，我正在拉取与某个国家/地区匹配的用户的结果，这是我目前的查询:

SELECT
  UserColder.ContactName,
  CountryUser.[User ID],
  Country.Name,
  Country.ID
FROM
  dbo.Country
  INNER JOIN dbo.CountryUser
    ON Country.ID = CountryUser.[Foreign ID]
  INNER JOIN dbo.UserColder
    ON CountryUser.[User ID] = UserColder.ID

但我想做的是让它只返回一个国家有多个用户 ID。所以如果我的数据是。

Bob   United States
Tom   United States
Steve United Stated

Frank Canada
Billy Canada

Lou   China

我希望结果是

Bob
Tom
Steve

对于美国，

Frank
Billy

对于加拿大，但不要拉下中国，因为它只有一个用户与之相关。但是拉下另一个，因为美国和加拿大有 creator 然后 1 用户绑定(bind)到它。这在查询中是否可行？

谢谢!

Answer 1

如果没有我面前的数据库，我不确定语法，但是 rank 函数对此有好处:

SELECT * FROM
(
SELECT
  UserColder.ContactName,
  CountryUser.[User ID],
  Country.Name,
  Country.ID,
  COUNT(UserColder.ContactName) OVER (PARTITION BY Country.ID) _RANK
FROM
  dbo.Country
  INNER JOIN dbo.CountryUser
    ON Country.ID = CountryUser.[Foreign ID]
  INNER JOIN dbo.UserColder
    ON CountryUser.[User ID] = UserColder.ID
) A
WHERE A._RANK > 1

感谢评论编辑 :) 还添加了概念证明:

SELECT * 
INTO #TEMP
FROM
(
SELECT 'UK' Country, 'Dave' Name
UNION
SELECT 'UK' Country, 'Bob' Name
UNION
SELECT 'UK' Country, 'Alex' Name
UNION
SELECT 'China' Country, 'Mike' Name
UNION
SELECT 'Holland' Country, 'Paul' Name
UNION
SELECT 'Holland' Country, 'James' Name
) A

SELECT * FROM #TEMP

SELECT * FROM
(SELECT Country, Name, Count(Name) OVER (PARTITION BY Country) _RANK FROM #TEMP) A
WHERE A._RANK > 1