這應該是在考邏輯不是考語法齁~~ XDD

補充原po的描述：

0001這個sn，【第一條件：有出現過126，而且126對應的af都是0。】(換句話說，126不旦有出現，而且並沒有發生【出現了126且對應的af不是0】的狀況)，再加上【第二條件：其他非126的全部都是0。】(並沒有發生其中一個不是0的狀況)，因此ff會計入0001這個sn；0001這個sn，並沒有發生bb的條件【af至少一個不是0】，所以沒有計入bb。

0003這個sn，雖然滿足第一條件：ag= '126' and af = '0'，但不滿足第二條件，它多筆資料中曾經出現過!= '0'，故不計入ff；另外，因為滿足bb條件【af至少一個不是0】故計數在bb欄位中。

0005這個sn，無ag= '126' and af = '0'，不滿足第一條件，故不計入ff；因沒滿足bb條件【af至少一個不是0】故不計在bb欄位中。

0006這個sn，無ag= '126' and af = '0'，不滿足第一條件，故不計入ff；因為滿足bb條件【af至少一個不是0】故計數在bb欄位中。

請問是否同意廢除【終止運轉的條文】？不同意的話是要繼續運轉還是不要繼續運轉？

完整語法如下：

select D."order",SUM(bb_tag) bb,SUM(ff_tag) ff
from(
select C."order",C.sn,C.bb_tag,C.ff_tag
from(
select B.*,
CASE WHEN (tag3+tag4+tag5+tag6)>0               THEN 1 ELSE 0 END bb_tag,
CASE WHEN (tag3+tag4+tag5+tag6)=0 and tag6_2>=1 THEN 1 ELSE 0 END ff_tag
from(
select A.*,
SUM(tag123)   over (PARTITION BY "order",sn) tag3,
SUM(tag124)   over (PARTITION BY "order",sn) tag4,
SUM(tag125)   over (PARTITION BY "order",sn) tag5,
SUM(tag126)   over (PARTITION BY "order",sn) tag6,
SUM(tag126_2) over (PARTITION BY "order",sn) tag6_2

from(
SELECT *,
CASE WHEN ag = '123' and af != '0' THEN 1 ELSE 0 END tag123,
CASE WHEN ag = '124' and af != '0' THEN 1 ELSE 0 END tag124,
CASE WHEN ag = '125' and af != '0' THEN 1 ELSE 0 END tag125,
CASE WHEN ag = '126' and af != '0' THEN 1 ELSE 0 END tag126,
CASE WHEN ag = '126' and af  = '0' THEN 1 ELSE 0 END tag126_2
FROM "ithelp_20181210_1"
) A
) B
) C
group by C."order",C.sn,C.ff_tag,C.bb_tag
) D
group by D."order" order by D."order"
--2018/12/10 PM20:39 精簡過

講幾個關鍵就好：

1. 要知道被ff和bb計算的【必要條件】是什麼？什麼狀態下會破功不被計算？像ff有個必要條件是一定要出現126而且為0，沒出現126就不會被計算，這是tag126_2的設計原因。
2. ag縱使在相同的order和sn之下，仍不是唯一值，這是必須做B層的原因，先將相同ag有沒有出現破功的狀況給判斷出來。
3. 針對order和sn做over partition，讓tag在相同的order和sn有唯一值，取得唯一值之後，就可以直接對order,sn,tag做group by(D層做的事)，最後再計算個數(最外層)。

1.假設 af 最小值 ='0'
2.測試資料多加一筆 ('b','0005','126','0')
3.MS SQL :

Type A:
-- [ag_123],[ag_124],[ag_125],[ag_126] 4 個皆為 '0' , ff=1

SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN [ag_123]+[ag_124]+[ag_125]+[ag_126]='0000' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]

Type B:
-- [ag_126]='0' 且 其它 3 個為 '0' 或 null , ff=1

SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN (([ag_123]='0' or [ag_123] is null) 
and ([ag_124]='0' or [ag_124] is null)
and ([ag_125]='0' or [ag_125] is null)) and [ag_126]='0' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8d811467bda186f735efd63da6b834cf

不知道大大用哪一種SQL，小弟使用 MsSql。

SQL語法

;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]

資料SQL

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')

說明

第一段
先以 order + sn 將資料分組，並計算下面三個規則
這三個規則是我歸納出可以在第一階段計算出來的部分

1. rule1: 計算 af<>'0' 的數量
2. rule2: 計算 ag='126' AND af='0' 的數量
3. rule3: 計算 ag IN('123','124','125') AND af<>'0' 的數量，這個部分使用反向邏輯

order   sn   rule1 rule2 rule3
-----|------|-----|-----|------
  a	 | 0001	|  0  |  1  |  0
  a	 | 0003	|  2  |  1  |  2
  b	 | 0005	|  0  |  0  |  0
  b	 | 0006	|  2  |  0  |  2

第二段
到第二階段就可以計算出每個 sn 的 bb 和 ff

1. bb: 判斷 rule1>0
2. ff: 判斷 rule2>=1 且 rule3=0

order   sn    bb    ff
-----|------|-----|-----
  a	   0001	   0	 1
  a	   0003	   1	 0
  b	   0005	   0	 0
  b	   0006	   1	 0

第三段
再以 order 分組一次並加總 bb 和 ff，就是期望的結果

order   bb    ff
-----|-----|-----
  a     1	  1
  b     1	  0

測試SQL

MySql 版本，不使用 CTE

SELECT `order`,
        SUM(bb) AS bb,
        SUM(ff) AS ff
FROM 
(
	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 
	(
		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
	) AS A
) AS B
GROUP BY `order`
ORDER BY `order`

測試SQL