Question

i 附有下列资料的表格:

CREATE TABLE [dbo].[HR_DEPENDENTS](  
            [PARENT_ID]     [bigint]       NOT NULL,  
            [DEPENDENT_ID]  [bigint]       NOT NULL,  
            [LAST_NAME]     [varchar](100) NOT NULL,  
            [FIRST_NAME]    [varchar](100)     NULL,  
            [DATE_OF_BIRTH] [date]             NULL)  

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 1,  Ray ,  First Child ,cast( 06/01/2001  as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 2,  Ray ,  Second , cast( 06/01/2002 as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 3,  Ray ,  Third ,cast( 06/01/2003  as date))

我使用了下表。

SELECT t01.parent_id  
    ,t01.taxdepn1  
    ,t01.taxdepn2  
    ,t01.taxdepn3  
    ,t01.taxdepn4  
    ,t02.depn1bday  
    ,t02.depn2bday  
    ,t02.depn3bday  
    ,t02.depn4bday  
FROM (SELECT PARENT_ID  
    ,[1] as taxdepn1  
    ,[2] as taxdepn2  
    ,[3] as taxdepn3  
    ,[4] as taxdepn4  
    FROM ( SELECT PARENT_ID  
         , dependent_id  
         , first_name+   +last_name as fullname  
         FROM  dbo.hr_dependents 
         ) AS piv  
    PIVOT ( max(fullname)  
            FOR dependent_id IN ([1], [2], [3], [4])  
          ) AS chld  
    ) T01
    ,(SELECT PARENT_ID2  
            , [1] as depn1bday  
            , [2] as depn2bday  
            , [3] as depn3bday  
            , [4] as depn4bday  
      FROM ( SELECT PARENT_ID as parent_id2  
                   ,dependent_id  
                   ,date_of_birth  
             FROM dbo.hr_dependents ) AS piv1  
      PIVOT ( min(date_of_birth)  
              FOR dependent_id IN ([1], [2], [3], [4])  
            ) AS chld1  
      ) T02  
WHERE T01.PARENT_ID=T02.PARENT_ID2

My worry is I may get the wrong date_of_birth of a particular dependent child.
I m new to sqlserver and I m using sqlexpress (2008).

任何帮助都受到高度赞赏......

Thank You
Elmer

Answer 1

说明中的更正

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 1, Ray , First Child ,cast( 06/01/2001 as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 2, Ray , Second , cast( 06/01/2002 as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 3, Ray , Third ,cast( 06/01/2003 as date))

年限——年限为一年。

Regards,
Elmer

Answer 2

假设每个父母的每套新胎的受抚养人id折,你获得与错误名字有关的错误出生日期。当你抽出桌子时,你把该母子的一笔价值分配给一栏,该栏是独一无二的,在纸浆之间是一致的。

换言之,第1栏是依赖性价值1;这并没有从表上改变,因此,如果你第二次抽出,则源表中第1行的名称与出生日期有关。

我倾向于使用“()号”功能来确定出生顺序,而不是依赖依赖性补贴,但只要你的商业逻辑能够保持一致性,就没有任何实际好处。

友情链接