現在有兩張表格
表格ㄧ Employee
EmpID EmpName Sex
01. 小王 男
02 小花 女
03 小陳 男
表格二 請假單 AbsDetail
EmpNo AbsType AbsHour
01 a01 3
02 a02 2
03 a03. 4
01. a02 1
利用Cross join輸出以下結果
EmpID EmpName AbsType AbsHour
01 小王 a01 3
01 小王 a02 1
01 小王 a03 0
02 小花 a01 0
02 小花 a02 2
02 小花 a03 0
03 小陳 a01 0
03 小陳 a02 0
03 小陳 a03 4
可以的話盡量詳細一點 我想了好久一直試出來的是
請假類別每一個人都一樣時數 他把全部人的同類別時數加起來了 加上group by也一樣 拜託各位大神了!
大概是這樣吧
declare @Employee table(
EmpID varchar(50)
,EmpName nvarchar(50)
,Sex nvarchar(1)
)
insert into @Employee
values('01',N'小王',N'男')
,('02',N'小花',N'女')
,('03',N'小陳',N'男')
declare @AbsDetail table(
EmpNo varchar(50)
,AbsType varchar(50)
,AbsHour int
)
insert into @AbsDetail
values('01','a01','3')
,('02','a02','2')
,('03','a03','4')
,('01','a02','1')
select EmpID
,EmpName
,AbsType
,isNull((
select sum(AbsHour)
from @AbsDetail b
where b.AbsType = k.AbsType
and a.EmpID = b.EmpNo
),0) AbsHour
from @Employee a
,(
select AbsType
from @AbsDetail
group by AbsType
) k
create table Employee(
EmpID varchar(2)
,EmpName nvarchar(50)
,Sex nvarchar(1)
);
insert into Employee values
('01',N'小王',N'男')
,('02',N'小花',N'女')
,('03',N'小陳',N'男');
create table AbsDetail(
EmpNo varchar(2)
,AbsType varchar(50)
,AbsHour int
);
insert into AbsDetail values
('01','a01','3')
,('02','a02','2')
,('03','a03','4')
,('01','a02','1');
with t1 as (
select distinct AbsType
from AbsDetail
), t2 as (
select EmpID
, EmpName
, AbsType
from t1
, Employee
)
select EmpID
, EmpName
, t2.AbsType
, coalesce(AbsHour,0)
from t2
left join AbsDetail
on EmpID = EmpNo
and t2.AbsType = AbsDetail.AbsType
order by EmpID, t2.AbsType;
CREATE TABLE Employee (
EmpID varchar(20),
EmpName nvarchar(20),
Sex nvarchar(10));
INSERT INTO Employee
VALUES ('01', N'小王', N'男'),
('02', N'小花', N'女'),
('03', N'小陳', N'男');
CREATE TABLE AbsDetail (
EmpNo varchar(20),
AbsType varchar(20),
AbsHour int);
INSERT INTO AbsDetail
VALUES ('01', 'a01', '3'),
('02', 'a02', '2'),
('03', 'a03', '4'),
('01', 'a02', '1');
SELECT Z.*,SUM(ISNULL(W.AbsHour, 0)) AS AbsHour
FROM (
SELECT DISTINCT X.EmpID,X.EmpName,Y.AbsType
FROM Employee AS X, AbsDetail AS Y) AS Z
LEFT JOIN AbsDetail AS W ON W.EmpNo = Z.EmpID AND W.AbsType = Z.AbsType
GROUP BY Z.EmpID, Z.EmpName, Z.AbsType
ORDER BY Z.EmpID, Z.EmpName, Z.AbsType