显示每个部门的重复成绩，以及部门名称及其成绩

Question

我可以显示存在多少重复分数，但我无法显示哪个部门。

select distinct *
  , ROW_NUMBER() over (partition by f.depname order by f.stgrade) as ranked
from (
  SELECT COUNT(*) as repeatetive, DepName, stgrade
  FROM TBL_DEPARTMANTS
  CROSS JOIN TBL_GRADES
  group by stgrade, DepName
  having count(*) > 1
) as f

Answer 1

• 不要使用生成多行返回的CROSS JOIN。
• 您不需要子查询，直接在主查询中使用窗口分析函数。
• 删除row_number() over (..)表达式中的partition by f.depname部分。

因此，以下查询非常适合您的需求：

select DepName, stgrade,
       row_number() over (order by g.stgrade,d.DepName) as ranked
  from TBL_DEPARTMANTS d
  left join TBL_GRADES g
    on g.DeptId = d.Id
 group by stgrade, DepName
having count(*) > 1;

假设TBL_GRADES表有一个名为 DeptId 的列(否则你需要再添加一个表，使用 JOIN 语句添加到此查询中(

演示

Answer 2

您的子查询返回部门和年级计算，其中至少存在 2 行。现在，当您在非重复列表中添加 COUNT 时，您只能从子查询输出中获取部门和年级的不同值，考虑到 COUNT 的差异，这将带来一些重复的数据。如果您想获得部门和年级的不同列表，您可以尝试以下脚本-

SELECT DISTINCT
       DepName, 
       stgrade
FROM TBL_DEPARTMANTS
     CROSS JOIN TBL_GRADES
GROUP BY DepName,stgrade             
HAVING COUNT(*) > 1

Answer 3

这可能会有所帮助....

DECLARE @Table TABLE(Id INT IDENTITY(1,1), Department NVARCHAR(10), Score INT)
INSERT INTO @Table VALUES 
('A', 10),
('A', 10),
('A', 20),
('A', 40),
('B', 10),
('B', 20),
('B', 20),
('C', 20)
;WITH DupLicateData
AS
(
  SELECT Id, Department,Score, COUNT(1) OVER(PARTITION BY Department,Score ORDER BY Department,Score ASC) Rnk
  FROM @Table
)
SELECT Id, Department,Score
FROM DupLicateData
WHERE Rnk > 1