clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432116 distinct fiddles created so far.

CREATE TABLE table_a (invoice_id nvarchar(20), item nvarchar(20), category nvarchar(20), barcode nvarchar(20) ,name nvarchar(20));
 hidden batch(es)


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
 hidden batch(es)


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 飲料
 hidden batch(es)


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 零食
 hidden batch(es)