如何从表生成数据到另一个表？ [英] How to generate data from a table to another table?

问题描述

我在数据库中有一个名为Employee的表，它有4列，即

* EmployeeID

* EmployeeName

*指定

*报告经理

每次在aspx页面中添加新记录时都会更新新的详细信息。

示例：

  EID EMPLOYEE_NAME指定REPORTING_MANAGER  
 1 TIM COOK CEO Null 
 2 MILLER CTO TIM COOK 
 3 BRAD COO TIM COOK 
 4 WILLIAM HRD TIM COOK 
 5 JOHN TECH MANAGER（DEV）WILLIAM 
 6 WALLACE TECH MANAGER（TEST）WILLIAM 
 7 SUNDAR SWIFT DEV JOHN 
 8 PETER SWIFT DEV JOHN 
 9 STEVE TEST ENGINEER WALLACE 

i希望从上表生成如下表所示的表格，

  EID EMPLOYEE_NAME REPORTING_MANAGER_ID  
 1 TIM COOK Null 
 2 MILLER 1 
 3 BRAD 1 
 4 WILLIAM 1 
 5 JOHN 4 
 6 WALLACE 4 
 7 SUNDAR 5 
 8彼得5 
 9 STEVE 6 

请帮帮我。

提前感谢。

解决方案

按照maciaj

i认为使用left join而不是inner join

 更新 t1  SET  t1.Reporting_manager_id = t2.Eid 
  FROM 员工 AS  t1  LEFT   JOIN 员工 AS  t2  ON  t1.Employee_name = t2.Reporting_manager 

因为在员工表中，某些记录管理器为null所以它不是包括在结果中.......





我希望它的帮助

谢谢

uttam

添加专栏 [ ^ ] Reporting_manager_id 到员工表，然后在查询下方运行：

 更新 t1  SET  t1.Reporting_manager_id = t2.Eid 
  FROM 员工 AS  t1 < span class =code-keyword> INNER   JOIN 员工 AS  t2  ON  t1.Employee_name = t2.Reporting_manager 

现在，您可以删除 Reporting_manager 列 [ ^ ]

I have a table in database called Employee which has 4 columns namely
*EmployeeID
*EmployeeName
*Designation
*Reporting Manager
which gets updated with new details everytime a new record is added in aspx page.
EXAMPLE:

EID     EMPLOYEE_NAME     DESIGNATION       REPORTING_MANAGER
1        TIM COOK            CEO                   Null
2        MILLER              CTO                   TIM COOK
3        BRAD                COO                   TIM COOK
4        WILLIAM             HRD                   TIM COOK
5        JOHN                TECH MANAGER(DEV)     WILLIAM
6        WALLACE             TECH MANAGER(TEST)    WILLIAM
7        SUNDAR              SWIFT DEV             JOHN
8        PETER               SWIFT DEV             JOHN
9        STEVE               TEST ENGINEER         WALLACE

i Wish to generate a table from above table like the one below,

EID     EMPLOYEE_NAME      REPORTING_MANAGER_ID
1        TIM COOK          Null
2        MILLER            1
3        BRAD              1
4        WILLIAM           1
5        JOHN              4
6        WALLACE           4
7        SUNDAR            5
8        PETER             5
9        STEVE             6

Please help me out.
Thank in advance.

解决方案

As per maciaj
i think use left join instead of inner join

UPDATE t1 SET t1.Reporting_manager_id = t2.Eid
FROM Employee AS t1 LEFT JOIN Employee AS t2 ON t1.Employee_name = t2.Reporting_manager

because in employee table some record manager has null so it's not including in result so.......


I hope its helps
Thanks
uttam

Add column[^] Reporting_manager_id to Employee table, then run below query:

UPDATE t1 SET t1.Reporting_manager_id = t2.Eid
FROM Employee AS t1 INNER JOIN Employee AS t2 ON t1.Employee_name = t2.Reporting_manager

Now, you can remove Reporting_manager column[^].

这篇关于如何从表生成数据到另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！