TSQL-SQL 2000

Question

我正为这个问题苦苦挣扎。我有一个表 A，如下所示：

Employee_ID      Dependant_FirstName     DOB
1                John                    12/12/1980
1                Lisa                    11/11/1982
2                Mark                    06/06/1985
2                Shane                   07/07/1982
2                Mike                    03/04/1990
3                NULL                    NULL

并想像这样复制表 B 中的这些数据（知道表 A 中最多只能有 6 个家属）：

Employee_ID  Dependant1_FirstName  DOB        Dependant2_FirstName DOB         Dependant3_FirstName  DOB   
1            John                  12/12/1980 Lisa                 11/11/1982  NULL                  NULL
2            Mark                  06/06/1985 Shane                07/07/1982  Mike                  03/04/1990
3            NULL                  NULL       NULL                 NULL        NULL                  NULL

非常感谢您的帮助。

马克

Answer 1

看看这个例子：

http://ryanfarley.com/blog/ archive/2005/02/17/1712.aspx

这里他将父键的子元素连接到一个字符串中，这应该允许您写出一个平面记录。

Answer 2

这是一个仅适用于您的示例数据的工作示例，旨在让您了解我将如何做到这一点。我正在使用基于出生日期和姓名的伪造家属计数器。请记住，如果员工有同名的双胞胎，它就会崩溃，但如果他们这样做，那么他们就应该承受他们所拥有的终生数据混乱:)

另外，请考虑升级该 SQL Server。或者将这种转向转移到您的报告工具而不是数据库。

CREATE TABLE #employees (employee_id INTEGER, Dependant_FirstName VARCHAR(20), DOB DATETIME)

INSERT INTO #employees VALUES (1,                'John',                    '12/12/1980')
INSERT INTO #employees VALUES (1,                'Lisa',                    '11/11/1982')
INSERT INTO #employees VALUES (2,                'Shane',                   '07/07/1982')
INSERT INTO #employees VALUES (2,                'Mark',                    '06/06/1985')
INSERT INTO #employees VALUES (2,                'Mike',                    '03/04/1990')
INSERT INTO #employees VALUES (3,                NULL,                    NULL)

SELECT 
    employee_id, 
    MAX(CASE WHEN dep_count = 1 THEN Dependant_FirstName ELSE NULL END) 'Dependant1_FirstName',
    MAX(CASE WHEN dep_count = 1 THEN DOB ELSE NULL END) 'Dependant1_DOB',
    MAX(CASE WHEN dep_count = 2 THEN Dependant_FirstName ELSE NULL END) 'Dependant2_FirstName',
    MAX(CASE WHEN dep_count = 2 THEN DOB ELSE NULL END) 'Dependant2_DOB',
    MAX(CASE WHEN dep_count = 3 THEN Dependant_FirstName ELSE NULL END) 'Dependant3_FirstName',
    MAX(CASE WHEN dep_count = 3 THEN DOB ELSE NULL END) 'Dependant3_DOB'
FROM
    (
        SELECT 
            employee_id, 
            Dependant_FirstName, 
            DOB, 
            (
                SELECT 
                    COUNT(*) 
                FROM 
                    #employees deps 
                WHERE 
                    #employees.employee_id = deps.employee_id AND 
                    CONVERT(VARCHAR, #employees.DOB, 126) + #employees.Dependant_FirstName <= 
                        CONVERT(VARCHAR, deps.DOB, 126) + deps.Dependant_FirstName
            ) 'dep_count'
        FROM 
            #employees
    ) add_dep_count_query
GROUP BY 
    employee_id

Answer 3

您可以

• 创建一个视图
• 计算一个虚构的排名
• 组来查找每个员工 ID 的最大排名
• 返回结果。

注意：我在示例中省略了 DOB 列

声明

CREATE VIEW dbo.VIEW_Employees_Ranking AS 
  SELECT  Ranking = ISNULL(e6.Employee_ID, 0)
            + ISNULL(e5.Employee_ID, 0)
            + ISNULL(e4.Employee_ID, 0)
            + ISNULL(e3.Employee_ID, 0)
            + ISNULL(e2.Employee_ID, 0)
            + ISNULL(e1.Employee_ID, 0)          
          , e1.Employee_ID
          , Name1 = e1.Dependant_FirstName
          , Name2 = e2.Dependant_FirstName
          , Name3 = e3.Dependant_FirstName
          , Name4 = e4.Dependant_FirstName
          , Name5 = e5.Dependant_FirstName
          , Name6 = e6.Dependant_FirstName
  FROM    dbo.Employees e1
          LEFT OUTER JOIN dbo.Employees e2 ON e2.Employee_ID = e1.Employee_ID AND e2.DOB > e1.DOB
          LEFT OUTER JOIN dbo.Employees e3 ON e3.Employee_ID = e2.Employee_ID AND e3.DOB > e2.DOB
          LEFT OUTER JOIN dbo.Employees e4 ON e4.Employee_ID = e3.Employee_ID AND e4.DOB > e3.DOB
          LEFT OUTER JOIN dbo.Employees e5 ON e5.Employee_ID = e4.Employee_ID AND e5.DOB > e4.DOB
          LEFT OUTER JOIN dbo.Employees e6 ON e6.Employee_ID = e5.Employee_ID AND e6.DOB > e5.DOB
GO

SELECT  er.*
FROM    dbo.VIEW_Employees_Ranking er
        INNER JOIN (
          SELECT  Ranking = MAX(Ranking)
                  , Employee_ID
          FROM    dbo.VIEW_Employees_Ranking
          GROUP BY
                  Employee_ID
        ) ermax ON ermax.Ranking = er.Ranking AND ermax.Employee_ID = er.Employee_ID

Answer 4

请检查此代码，它可能对您有用。

declare @Emp_Id int
declare @Name int
declare @DOB int
declare @Count int
set @Count=1
DECLARE x_cursor CURSOR FOR
SELECT distinct  Employee_ID from tableA
OPEN x_cursor
FETCH NEXT FROM x_cursor
INTO @Emp_Id
WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE second_cursor CURSOR FOR
SELECT distinct  Dependant_FirstName,DOB from tableA
where Employee_ID=@Emp_Id
OPEN second_cursor
FETCH NEXT FROM second_cursor
INTO @Name,@DOB

WHILE @@FETCH_STATUS = 0
BEGIN


if(@Count=1)
    begin 
        insert into tableB (Employee_ID , Dependant1_FirstName,DOB)
        values(@Emp_Id,@Name,@DOB)
        set @Count=@Count+1
    end
else
    begin
        exec('Update  tableB set  Dependant'+@count+'_FirstName='+@Name+' ,DOB'+@Count+'='+@DOB+'  where Employee_ID='+@Emp_Id)
        set @Count=@Count+1
    end

   FETCH NEXT FROM second_cursor
   INTO @Name,@DOB
END
CLOSE second_cursor
DEALLOCATE second_cursor


 set @Count=1
   FETCH NEXT FROM x_cursor
   INTO @Emp_Id

END
CLOSE x_cursor;
DEALLOCATE x_cursor
GO