时间戳

首页 > 解决方案 > LEFT JOIN 使用不同的？

sql-server - LEFT JOIN 使用不同的？

问题描述

最初，我的桌子上有 15 名员工。其中两个具有相同的姓氏。

如果我将E.MAT_EMP选择保留原样，它会正常工作，并且将选择 15 名员工。

如果我删除它（因为我不希望显示它），但行数、名字、姓氏和月份天数除外。它返回给我 14 行而不是 15 行。检查后，这显然是我具有相同姓/名的行之一。

@StartDate date,
@EndDate date

as
begin

Declare @D1 date = @StartDate
Declare @D2 date = @EndDate

Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1) A For XML Path ('')),1,1,'') )
Declare @SQL varchar(max) = '
Declare @D1 date = '''+cast(@D1 as varchar(50))+'''
Declare @D2 date = '''+cast(@D2 as varchar(50))+'''

Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],*
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt
'
Exec(@SQL)

输出如下：

在此处输入图像描述

我的目标是在保留 15 行的同时简单地删除该列。

标签： sql-server

解决方案

将 * 替换为您要显示的列名，并且不要将其从子查询中删除。

    Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°], [LastName], [FirstName] /*other needed columns here*/
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt

推荐阅读