SQL Server 2017

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

CREATE TABLE table_a
	(invoice_id nvarchar(20), item nvarchar(20), category nvarchar(20), barcode nvarchar(20) ,name nvarchar(20));
INSERT INTO table_a SELECT invoice_id = N'AA002',item = N'1' ,
category = N'211',barcode=N'XXX01',name=N'零食'
INSERT INTO table_a SELECT invoice_id = N'AA001',item = N'2' ,
category = N'212',barcode=N'XXX02',name=N'飲料'
INSERT INTO table_a SELECT invoice_id = N'AA001',item = N'1' ,
category = N'211',barcode=N'XXX01',name=N'零食'
INSERT INTO table_a SELECT invoice_id = N'AA003',item = N'1' ,
category = N'212',barcode=N'XXX02',name=N'飲料'
INSERT INTO table_a SELECT invoice_id = N'AA003',item = N'2' ,
category = N'211',barcode=N'XXX01',name=N'零食'
INSERT INTO table_a SELECT invoice_id = N'AA004',item = N'3' ,
category = N'211',barcode=N'XXX01',name=N'零食'
INSERT INTO table_a SELECT invoice_id = N'AA005',item = N'1' ,
category = N'212',barcode=N'XXX02',name=N'飲料'
7 rows affected

SELECT *
FROM table_a 
WHERE 1=1
ORDER BY invoice_id,item


invoice_id
item
category
barcode
name


AA001
1
211
XXX01
零食

AA001
2
212
XXX02
飲料

AA002
1
211
XXX01
零食

AA003
1
212
XXX02
飲料

AA003
2
211
XXX01
零食

AA004
3
211
XXX01
零食

AA005
1
212
XXX02
飲料


SELECT table_a.*
FROM table_a 
INNER JOIN (
SELECT invoice_id
FROM table_a 
GROUP BY invoice_id
HAVING COUNT(invoice_id) = 1 
) AS TempTable ON TempTable.invoice_id=table_a.invoice_id
WHERE 1=1
AND category='211'


invoice_id
item
category
barcode
name


AA002
1
211
XXX01
零食

AA004
3
211
XXX01
零食

invoice_id	item	category	barcode	name
AA001	1	211	XXX01	零食
AA001	2	212	XXX02	飲料
AA002	1	211	XXX01	零食
AA003	1	212	XXX02	飲料
AA003	2	211	XXX01	零食
AA004	3	211	XXX01	零食
AA005	1	212	XXX02	飲料