例如表 tst
w_id defect
1 a
1 b
2 c
2 a
3 a
4 b
希望的统计结果是这样:例如1有2个defect,统计的时候只要1个。任意是a或者b都行,同理2也一样,那么结果可以是
defect qty
a 3
b 1
因为id只有4个。所以qty的和只要是4即可。或者这样
defect qty
a 1
b 2
c 1
目的就是sum defect的结果数量=id的数量。id重复的,任意取一个defect即可。
w_id defect
1 a
1 b
2 c
2 a
3 a
4 b
希望的统计结果是这样:例如1有2个defect,统计的时候只要1个。任意是a或者b都行,同理2也一样,那么结果可以是
defect qty
a 3
b 1
因为id只有4个。所以qty的和只要是4即可。或者这样
defect qty
a 1
b 2
c 1
目的就是sum defect的结果数量=id的数量。id重复的,任意取一个defect即可。
defect qty
a 1
b 2
c 1
目的就是sum defect的结果数量=id的数量。id重复的,任意取一个defect即可。没看懂
COUNT(w_Id)
FROM (SELECT w_Id,
Substr(Wm_Concat(Defect), 1, 1) AS Defect
FROM (SELECT *
FROM Tst
ORDER BY Defect)
GROUP BY w_Id)
GROUP BY Defect结果:
DEFECT COUNT(W_ID)
------ -----------
a 3
b 1
SELECT Nvl(Substr(Defect, 1, Instr(Defect, ',', 1) - 1), Defect),
COUNT(w_Id)
FROM (SELECT w_Id,
Wm_Concat(Defect) AS Defect
FROM (SELECT *
FROM Tst
ORDER BY Defect)
GROUP BY w_Id)
GROUP BY Nvl(Substr(Defect, 1, Instr(Defect, ',', 1) - 1), Defect);结果:
DEFECT COUNT(W_ID)
-------------------------------------------------------------------------------- -----------
aaa 3
bb 1
from (select t.*, rank() over(partition by t.id order by t.defect) r_num
from test_defect t) tt
where tt.r_num <> 2 --去提重复值
SN_KEY DEFECT_CODE
449747 M8401
449747 M8401
449747 F8487
449747 F8487
489534 F8487
489628 M8417
490532 M8487
490532 M8487
490532 M8487
490532 M8487
490542 F8404
903691 M8404
904393 M8410
907369 M8123
907942 M8404
908241 M8404
908580 M8401
908580 F8487
908962 F8404
例如上面的數據,我對sn_key要取唯一的,重復的不要。取好了之后,by defect_code做統計。
就是一個有重復記錄的表,要統計這個表的記錄,去掉重復,無論重復幾個,都只取一個。去掉重復以后,再對這個表做sum。
SN_KEY DEFECT_CODE
449747 M8401
449747 M8401
449747 F8487
449747 F8487
這個重復怎么去?得到任何以下的一個結果都行。
SN_KEY DEFECT_CODE
449747 M8401SN_KEY DEFECT_CODE
449747 F8487
by defect_code 做sum
DEFECT_CODE qty
F8487 1