SQL Server 2017

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

DECLARE @Data TABLE
(
    [order] NVARCHAR(10),
    sn NVARCHAR(10),
    ag NVARCHAR(10),
    af NVARCHAR(10)
)

INSERT INTO @Data 
([order], sn, ag, af) 
    VALUES
('a','0001','123','0'),
('a','0001','124','0'),
('a','0001','125','0'),
('a','0001','126','0'),
('a','0003','123','1'),
('a','0003','123','2'),
('a','0003','124','0'),
('a','0003','125','0'),
('a','0003','126','0'),
('b','0005','123','0'),
('b','0005','124','0'),
('b','0006','123','0'),
('b','0006','124','1'),
('b','0006','124','2'),
('b','0006','125','0')

;WITH A AS
(
    SELECT [order], sn,
           SUM(CASE WHEN af<>'0' THEN 1 ELSE 0 END) AS rule1,
           SUM(CASE WHEN ag='126' AND af='0' THEN 1 ELSE 0 END) AS rule2,
           SUM(CASE WHEN ag IN('123','124','125') AND af<>'0' THEN 1 ELSE 0 END) AS rule3
    FROM @Data
    GROUP BY [order], sn
)
--SELECT * FROM A
,B AS
(
    SELECT [order], sn,
    	   CASE WHEN rule1>0 THEN 1 ELSE 0 END AS bb,
    	   CASE WHEN rule2>=1 AND rule3=0 THEN 1 ELSE 0 END AS ff
    FROM A
)
--SELECT * FROM B
,C AS
(
    SELECT [order],
    	   SUM(bb) AS bb,
    	   SUM(ff) AS ff
    FROM B
    GROUP BY [order]
)

SELECT * FROM C ORDER BY [order]

order	bb	ff
a	1	1
b	1	0