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,
SENDCHOICE_NAME NVARCHAR(50) NULL);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME,SENDCHOICE_NAME) VALUES
(N'A',N'1',N'已完修',N'自行送件'),
(N'B',N'2',N'已完修',N'自行寄件'),
(N'C',N'3',N'已完修',N'派員收件'),
(N'A',N'4',N'未完修',N'到府維修'),
(N'B',N'5',N'已完修',N'自行送件'),
(N'B',N'6',N'未完修',N'自行寄件'),
(N'C',N'7',N'已完修',N'派員收件'),
(N'C',N'8',N'已完修',N'到府維修'),
(N'C',N'9',N'未完修',N'自行送件'),
(N'C',N'10',N'未完修',N'自行寄件'),
(N'C',N'11',N'未完修',N'派員收件'),
(N'C',N'12',N'未完修',N'到府維修'),
(N'C',N'13',N'未完修',N'自行送件'),
(N'C',N'14',N'未完修',N'自行寄件'),
(N'D',N'15',N'未完修',N'派員收件');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME
SENDCHOICE_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,
MACHINEUSE_NAME NVARCHAR(50) NULL);

INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME,MACHINEUSE_NAME) VALUES
(N'1',N'1',N'brother',N'展示機'),
(N'2',N'0',N'NCC',N'教學機'),
(N'3',N'1',N'X',N'活動用'),
(N'4',N'0',N'Y',N'整理用'),
(N'5',N'1',N'Y',N'展示機'),
(N'6',N'0',N'Z',N'教學機'),
(N'7',N'1',N'Z',N'活動用'),
(N'8',N'0',N'Z',N'整理用'),
(N'9',N'1',N'Z',N'展示機'),
(N'10',N'1',NULL,N'教學機'),
(N'11',N'0',NULL,N'活動用'),
(N'12',N'0',NULL,N'整理用'),
(N'13',N'1',N'brother',N'展示機'),
(N'14',N'0',N'NCC',N'教學機');

SELECT *
FROM ASSET;


ASSET_ID
HAVEWARRANTY
BRAND_NAME
MACHINEUSE_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
SENDCHOICE_NAME
ASSET_ID
HAVEWARRANTY
BRAND_NAME
MACHINEUSE_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
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	SENDCHOICE_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	MACHINEUSE_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	教學機