我如何从这样的两个表中获取数据？ [英] how do i get data from two tables like this?

问题描述

我有两张桌子Table1

I have two table Table1

LeaveCode
CL
EL
PL
KL
ML

和表2

and Table2

EmpCode	LeaveCode	Balances
1	CL	4
1	EL	3
1	KL	5
2	CL	2
2	EL	5
2	PL	2
2	ML	5
3	KL	2
3	ML	4
4	PL	2
5	CL	4
5	KL	4
5	PL	6

现在我想要数据，以便所有员工都拥有所有的假期代码行，如果表2中的数据对于特定员工不存在，则结果表中的余额将为零。

Now i want data so that all employees have all leavecode row and if data doesn''t exist in Table2 for a particular employee then balance will be zero in resulting table.

推荐答案

这种方式...

注意Temp1可留条和临时2是有利的

this way...
Note Temp1 leavetable and temp2 is employeetable

with a as
(
    Select distinct T1.LeaveCode,EmpCode 
    from Temp1 T1
    cross Join Temp2 T2
)
select T1.LeaveCode,T1.EmpCode,isnull(balance,0) as balance 
from a as T1
left join Temp2 T2 ON T1.LeaveCode=T2.FK_LeaveCode and T1.EmpCode=T2.EmpCode
order by T1.EmpCode

试用...

Trial...

Create table Temp1(LeaveCode VarChar(2) Primary Key)

Insert into Temp1 Values('AL')
Insert into Temp1 Values('BL')
Insert into Temp1 Values('CL')
Insert into Temp1 Values('DL')
Insert into Temp1 Values('EL')


Create table Temp2(EmpCode Int,FK_LeaveCode VarChar(2) references Temp1(LeaveCode),Balance Int)

Insert into Temp2 Values(1,'AL',3)
Insert into Temp2 Values(1,'BL',2)
Insert into Temp2 Values(1,'CL',1)

Insert into Temp2 Values(2,'AL',12)
Insert into Temp2 Values(2,'BL',12)
Insert into Temp2 Values(2,'CL',7)
Insert into Temp2 Values(2,'EL',8)


Insert into Temp2 Values(3,'DL',9)
Insert into Temp2 Values(3,'AL',22)

Insert into Temp2 Values(4,'CL',13)
Insert into Temp2 Values(4,'EL',14)
Insert into Temp2 Values(4,'DL',1);

with a as
(
Select distinct T1.LeaveCode,EmpCode from Temp1 T1
cross Join Temp2 T2
)
select T1.LeaveCode,T1.EmpCode,isnull(balance,0) as balance from a as T1
left join Temp2 T2 ON T1.LeaveCode=T2.FK_LeaveCode and T1.EmpCode=T2.EmpCode
order by T1.EmpCode


Drop Table Temp2
Drop Table Temp1

数据透视表怎么样？

How about pivot table?

DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1800)
DECLARE @pt NVARCHAR(4000)


SET @cols = STUFF(( SELECT DISTINCT '],[' + [LeaveCode]
                        FROM Table1
                        ORDER BY '],[' + [LeaveCode]
                        FOR XML PATH('')), 1, 2, '') + ']'

SET @dt = 'SELECT EmpCode, LeaveCode, COALESCE(Balances,0) AS Balances' + 
           'FROM Table2 RIGHT OUTER JOIN Table1 ON Table2.[LeaveCodeId] = Table1.[LeaveCodeId] '

SET @pt = 'SELECT Empcode, ' + @cols + ' ' + 
           'FROM (' + @dt + ') AS DT' +
           'PIVOT(MAX(Balances) FOR EmpCode IN(' + @cols + ')) AS PT ' +
           'ORDER BY EmpCode' 

EXEC(@pt)

结果：

Result:

EmpCode	CL      EL     KL     PL     ML
1       4	3	5     0      0
2	2	5       0     2      5
... and so on 

如你所见，它更易读;）

As you can see, it''s more readible ;)

你好



你的SQL QUERY是...........

Hi

Your SQL QUERY is ...........

SELECT tab.EMPLOYEE_ID , tab.LeaveCode , ISNULL(BALANCE,0)  FROM dbo.Tab_LeaveBAL (NOLOCK) 
RIGHT JOIN
(SELECT TabEmp.EMPLOYEE_ID,TabLeave.LeaveCode    FROM dbo.Tab_Leave as TabLeave (NOLOCK)
CROSS JOIN (
SELECT EMPLOYEE_ID  FROM dbo.Tab_LeaveBAL (NOLOCK)
GROUP BY EMPLOYEE_ID ) as TabEmp  ) AS tab
ON tab.EMPLOYEE_ID = tab_LeaveBal.EMPLOYEE_ID and tab.LeaveCode  = tab_LeaveBal.LEAVE_ID

输出

Output

EMPLOYEE_ID LeaveCode BALANCE
----------- --------- -----------
1           CL        4
1           EL        3
1           PL        0
1           KL        5
1           ML        0
2           CL        2
2           EL        5
2           PL        2
2           KL        0
2           ML        5
3           CL        0
3           EL        0
3           PL        0
3           KL        2
3           ML        4
4           CL        0
4           EL        0
4           PL        2
4           KL        0
4           ML        0
5           CL        4
5           EL        0
5           PL        6
5           KL        4
5           ML        0

（25行受影响）

(25 row(s) affected)

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