sql - 如何从此表中检索 DISTINCT 值？

Question

这是我的表值。 ( 7 条记录 )

SELECT * FROM tbl1

我无法发布图片，因为我的代表很低。所以我把它链接在这里 /image/CFl0u.png

我写了一个查询来避免最后一条记录，但我仍然得到最后一条记录。 (我需要的只是不同的电子邮件)

SELECT DISTINCT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId FROM tbl1
WHERE EmployerId = 7 AND IsDeleted = 0

上述查询仍会检索与最后一个重复电子邮件记录相同的 7 条记录。

Answer 1

您可以使用ROW_NUMBER与 OVER 子句:

WITH CTE AS
(
  SELECT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId
        , RN = ROW_NUMBER() OVER (PARTITION BY CandEmail ORDER BY ContNum DESC)
  FROM tbl1
  WHERE IsDeleted = 0
)
SELECT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId
FROM CTE WHERE RN = 1

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.