iT邦幫忙

0

SQL cross join 問題

現在有兩張表格
表格ㄧ 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也一樣 拜託各位大神了!

1
純真的人
iT邦大師 1 級 ‧ 2021-10-08 16:30:15

大概是這樣吧

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

https://ithelp.ithome.com.tw/upload/images/20211008/20061369wRkgCRx9wV.png

1
一級屠豬士
iT邦大師 1 級 ‧ 2021-10-08 17:05:06
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;

Demo

0
rogeryao
iT邦大師 1 級 ‧ 2021-10-08 19:00:21
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

Demo

我要發表回答

立即登入回答