SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME) VALUES
(N'A',N'1',N'已完修'),
(N'B',N'2',N'已完修'),
(N'C',N'3',N'已完修'),
(N'A',N'4',N'未完修'),
(N'B',N'5',N'已完修'),
(N'B',N'6',N'未完修'),
(N'C',N'7',N'已完修'),
(N'C',N'8',N'已完修'),
(N'C',N'9',N'未完修'),
(N'C',N'10',N'未完修'),
(N'C',N'11',N'未完修'),
(N'C',N'12',N'未完修'),
(N'C',N'13',N'未完修'),
(N'C',N'14',N'未完修'),
(N'D',N'15',N'未完修');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME


A
1
已完修

B
2
已完修

C
3
已完修

A
4
未完修

B
5
已完修

B
6
未完修

C
7
已完修

C
8
已完修

C
9
未完修

C
10
未完修

C
11
未完修

C
12
未完修

C
13
未完修

C
14
未完修

D
15
未完修


CREATE TABLE ASSET (
ASSET_ID NVARCHAR(50),
HAVEWARRANTY NVARCHAR(50) NULL,
BRAND_NAME NVARCHAR(50) NULL);

INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME) VALUES
(N'1',N'1',N'brother'),
(N'2',N'0',N'NCC'),
(N'3',N'1',N'X'),
(N'4',N'0',N'Y'),
(N'5',N'1',N'Y'),
(N'6',N'0',N'Z'),
(N'7',N'1',N'Z'),
(N'8',N'0',N'Z'),
(N'9',N'1',N'Z'),
(N'10',N'1',NULL),
(N'11',N'0',NULL),
(N'12',N'0',NULL),
(N'13',N'1',N'brother'),
(N'14',N'0',N'NCC');

SELECT *
FROM ASSET;


ASSET_ID
HAVEWARRANTY
BRAND_NAME


1
1
brother

2
0
NCC

3
1
X

4
0
Y

5
1
Y

6
0
Z

7
1
Z

8
0
Z

9
1
Z

10
1
null

11
0
null

12
0
null

13
1
brother

14
0
NCC


SELECT ROW_NUMBER() OVER (PARTITION BY A.MECHANIC_NAME ORDER BY A.MECHANIC_NAME) AS 'PART NO',*
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
ORDER BY A.MECHANIC_NAME,A.ASSET_ID	


PART NO
MECHANIC_NAME
ASSET_ID
STATE_NAME
ASSET_ID
HAVEWARRANTY
BRAND_NAME


1
A
1
已完修
1
1
brother

2
A
4
未完修
4
0
Y

1
B
2
已完修
2
0
NCC

2
B
5
已完修
5
1
Y

3
B
6
未完修
6
0
Z

1
C
10
未完修
10
1
null

2
C
11
未完修
11
0
null

3
C
12
未完修
12
0
null

4
C
13
未完修
13
1
brother

5
C
14
未完修
14
0
NCC

6
C
3
已完修
3
1
X

7
C
7
已完修
7
1
Z

8
C
8
已完修
8
0
Z

9
C
9
未完修
9
1
Z

1
D
15
未完修
null
null
null


SELECT A.MECHANIC_NAME AS '維修人員',
COUNT(*) AS '接單數',
SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS '保固內',
SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS '保固外',
SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS '已完修',
SUM(CASE WHEN B.ASSET_ID IS NOT NULL 
AND ISNULL(B.BRAND_NAME,'BRAND NAME IS NULL') NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌',
SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌(不含 NULL))'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
GROUP BY A.MECHANIC_NAME


維修人員
接單數
保固內
保固外
已完修
它牌
它牌(不含 NULL))


A
2
1
1
1
1
1

B
3
1
2
2
2
2

C
9
5
4
3
7
4

D
1
0
0
0
0
0

MECHANIC_NAME	ASSET_ID	STATE_NAME
A	1	已完修
B	2	已完修
C	3	已完修
A	4	未完修
B	5	已完修
B	6	未完修
C	7	已完修
C	8	已完修
C	9	未完修
C	10	未完修
C	11	未完修
C	12	未完修
C	13	未完修
C	14	未完修
D	15	未完修

ASSET_ID	HAVEWARRANTY	BRAND_NAME
1	1	brother
2	0	NCC
3	1	X
4	0	Y
5	1	Y
6	0	Z
7	1	Z
8	0	Z
9	1	Z
10	1	null
11	0	null
12	0	null
13	1	brother
14	0	NCC