从左表中获取所有行，并根据其他列的值删除重复行

Question

场景: --我有一个主要的薪金表，即基本工资表，人力资源管理表，工资总目表。[Columns are : ID, SalaryHeadName]等

和一个交易表，以保存每个候选人的工资记录。[Columns are : CandidateID, SalaryHeadID, Amount]

现在，我想列出候选人的个人资料页面上的所有薪资头，用户将在该页面前面看到所有标题和可编辑的文本框。

如果事务表包含该特定候选人的特定薪资头的金额，我将填写该金额，否则该文本框将为空。

到目前为止我的疑问是：

WITH cte as
(
Select ID,SalaryHeadName, Amount, DENSE_RANK () over (order by SalaryHeadName desc) as DenseRank 
from VW_GetSalaryStructureData 
where CandidateID = 145 or CandidateID is null 
GROUP BY  ID,SalaryHeadName,Amount

)

SELECT
    ID,SalaryHeadName, Amount, COUNT(Amount) as cnt
FROM
    cte
GROUP BY
    ID,SalaryHeadName, Amount

两个表中的样本数据：

薪金总表数据

 ID SalaryHeadName  MainHead    Description CreatedDate CreatedBy
    1   BASIC   BASIC   GROSS   2018-05-25 16:40:06.080 1
    2   HRA HRA GROSS   2018-05-25 16:40:06.080 1
    3   Conveyance  Conveyance  GROSS   2018-05-25 16:40:06.080 1
    4   Medical Allowance   Medical Allowance   GROSS   2018-05-25 16:40:06.080 1
    5   City Compensation Allowance City Compensation Allowance GROSS   2018-05-25 16:40:06.083 1
    6   Books N Periodicals Allowance   Books N Periodicals Allowance   GROSS   2018-05-25 16:40:06.083 1
    7   Entertainment Allowance Entertainment Allowance GROSS   2018-05-25 16:40:06.083 1
    8   Attire Allowance    Attire Allowance    GROSS   2018-05-25 16:40:06.083 1
    9   Mobile Allowance    Mobile Allowance    GROSS   2018-05-25 16:40:06.087 1
    10  Supplementary Allowance Supplementary Allowance GROSS   2018-05-25 16:40:06.087 1
    11  Leave Allowance Leave Allowance GROSS   2018-05-25 16:40:06.087 1
    12  Bonus   Bonus   GROSS   2018-05-25 16:40:06.087 1
    13  Child Education Allowance   Child Education Allowance   GROSS   2018-05-25 16:40:06.090 1
    14  Washing Allowance   Washing Allowance   GROSS   2018-05-25 16:40:06.090 1
    15  Other Allowance Other Allowance GROSS   2018-05-25 16:40:06.090 1
    16  Telephone Allowance Telephone Allowance GROSS   2018-05-25 16:40:06.090 1
    17  Bonus (ESIC)    Bonus (ESIC)    GROSS   2018-05-25 16:40:06.090 1
    18  Daily Allowance Daily Allowance GROSS   2018-05-25 16:40:06.093 1
    19  Educational Allowance   Educational Allowance   GROSS   2018-05-25 16:40:06.093 1
    20  Performance Allowance   Performance Allowance   GROSS   2018-05-25 16:40:06.093 1
    21  Transport Allowance Transport Allowance GROSS   2018-05-25 16:40:06.093 1
    22  Vehicle N Maintenance Allowance Vehicle N Maintenance Allowance GROSS   2018-05-25 16:40:06.097 1
    23  Driver Salary Allowance Driver Salary Allowance GROSS   2018-05-25 16:40:06.097 1
    24  Business Development Allowance  Business Development Allowance  GROSS   2018-05-25 16:40:06.097 1
    25  LTA LTA GROSS   2018-05-25 16:40:06.097 1
    26  Gross   Gross   GROSS   2018-05-25 16:40:06.097 1
    27  Gratuity CTC    Gratuity CTC    CTC 2018-05-25 16:40:06.100 1
    28  Leave Salary Allowance  Leave Salary Allowance  CTC 2018-05-25 16:40:06.100 1
    29  Bonus CTC   Bonus CTC   CTC 2018-05-25 16:40:06.100 1
    30  GPA GPA CTC 2018-05-25 16:40:06.100 1
    31  EPF EPF CTC 2018-05-25 16:40:06.100 1
    32  ESI ESI CTC 2018-05-25 16:40:06.103 1
    33  CTC CTC CTC 2018-05-25 16:40:06.103 1
    34  PF  PF  NET 2018-05-25 16:40:06.103 1
    35  ESI ESI NET 2018-05-25 16:40:06.103 1
    36  PT  PT  NET 2018-05-25 16:40:06.107 1

事务表数据

ID  CandidateID SalaryHeadID    Amount  CreatedDate CreatedBy
1   151 1   15201   2018-06-11 11:03:07.587 1
2   151 2   1520    2018-06-11 11:03:07.587 1
3   151 3   2510    2018-06-11 11:03:07.587 1
4   151 6   1500    2018-06-11 11:03:07.587 1
5   151 26  50151   2018-06-11 11:03:07.587 1
6   151 33  434 2018-06-11 11:03:07.587 1
7   184 1   23233   2018-06-21 11:09:12.627 1
8   184 2   5455    2018-06-21 11:09:12.627 1
9   184 3   4343    2018-06-21 11:09:12.627 1
10  184 26  50151   2018-06-21 11:09:12.627 1
11  184 33  434 2018-06-21 11:09:12.627 1
12  154 1   23233   2018-06-21 11:10:37.263 1
13  154 2   5455    2018-06-21 11:10:37.263 1
14  154 3   4343    2018-06-21 11:10:37.263 1
15  154 26  50151   2018-06-21 11:10:37.263 1
16  154 33  434 2018-06-21 11:10:37.263 1
17  145 1   23233   2018-06-21 11:09:12.627 1
18  145 2   5455    2018-06-21 11:09:12.627 1
19  145 3   4343    2018-06-21 11:09:12.627 1
20  145 26  50151   2018-06-21 11:09:12.627 1
21  145 33  434 2018-06-21 11:09:12.627 1

该查询的结果是：

请点击查看结果：

现在，我想要的是删除带有NULL值的重复行，并只列出一次SalaryHeads。

期望结果：我希望从结果中删除红色高亮显示的行，如果候选人在事务表中有数量，否则所有标题都为空值，请单击此处查看

在HTML页面上所期望的结果，我正在用MVC 5开发

Answer 1

下面的查询应该返回预期的结果。

BEGIN

WITH cte as ( Select ID,SalaryHeadName, Amount, DENSE_RANK () over (order by SalaryHeadName desc) as DenseRank  from VW_GetSalaryStructureData  where CandidateID = 145 or CandidateID is null  GROUP BY  ID,SalaryHeadName,Amount

)

SELECT
    ID,SalaryHeadName, Amount, COUNT(Amount) as cnt INTO #RESULT_TABLE FROM
    cte GROUP BY
    ID,SalaryHeadName, Amount;

WITH RM_DUPL AS 
(SELECT ROW_NUMBER OVER(PARTITION BY ID ORDER BY AMOUNT NULLS LAST) AS RN,  ID,SalaryHeadName, Amount, cnt FROM #RESULT_TABLE) SELECT  FROM RM_DUPL  WHERE RN = 1 or AMOUNT IS NOT NULL; END;

Answer 2

并不是每个人都知道UNION操作符删除了副本。当您想要去复制数据时，这是很有用的。详情如下：

 SELECT ColA, ColB, ColC
 FROM TableWithDuplicates
 UNION
 SELECT ColA, ColB, ColC
 FROM TableWithDuplicates

结果是TableWithDuplicates的一个重复结果集。

Answer 3

我希望下面的查询将满足您在所需输出图像上提到的需求。

SELECT SH.Id SalaryHeadId, SH.SalaryHeadName, ISNULL(Tr.Amount, 0.00) PerMonth, 
ISNULL(Tr.Amount, 0.00)*12 PerAnnum
FROM Sal_Head SH
LEFT JOIN Transaction_Table Tr ON SH.Id = Tr.SalaryHeadId
WHERE Tr.CandidateId = 145