oracle group按不同的事务ID(oracle group by different transaction ID)

oracle group按不同的事务ID(oracle group by different transaction ID)

我想使用事务ID（TRXID）可视化我的gridcontrol行和操作块。 我想添加一个列，例如trxID是146然后是1然后是149然后是2当155然后是1时162然后是2 ....使用此列我将能够使用COLORCOLUMN可视化我的行块。 我只会使用2种不同的颜色。 有什么建议么？

TRXID COLUMN1 COLUMN2 COLORCOLUMN 144 .. .. 1 144 .. .. 1 144 .. .. 1 151 .. .. 2 151 .. .. 2 167 .. .. 1 167 .. .. 1 167 .. .. 1 185 .. .. 2 190 .. .. 1

这是我的查询：

SELECT D.TRX_ID TRXID, A.AGENT_NAME, S.NAME SKILL_NAME, D.SKILL_LEVEL, CASE D.CHANGE_TYPE WHEN 'DROP' THEN 'DÜŞÜRME' WHEN 'ADD' THEN 'EKLEME' END CHANGE_TYPE, L.LOGIN_NAME, H.CHANGE_DATE, --COLORCOLUMN HERE FROM CC_RTM_DBA.SKILL_CHANGE_DETAIL_HIST D, CC_RTM_DBA.SKILL_CHANGE_HIST H, CC_RTM_DBA.CFG_AGENT A, CC_RTM_DBA.CFG_SKILL S, CC_RTM_DBA.CFG_RTM_LOGIN L WHERE D.TRX_ID = H.ID AND D.AGENT_ID = A.ID AND D.SKILL_ID = S.ID AND L.LOGIN_ID = H.CHANGE_USER ORDER BY D.TRX_ID DESC, A.AGENT_NAME, SKILL_NAME, CHANGE_TYPE

和图片在这里：

I want to visualize my gridcontrol rows with block of operations with the transaction ID(TRXID). I want to add a column that for example trxID is 146 then 1 when 149 then 2 when 155 then 1 when 162 then 2.... With this column I will be able to visualize my block of rows with COLORCOLUMN. I will only use 2 different colors. Any suggestions?

TRXID COLUMN1 COLUMN2 COLORCOLUMN 144 .. .. 1 144 .. .. 1 144 .. .. 1 151 .. .. 2 151 .. .. 2 167 .. .. 1 167 .. .. 1 167 .. .. 1 185 .. .. 2 190 .. .. 1

here is my query:

SELECT D.TRX_ID TRXID, A.AGENT_NAME, S.NAME SKILL_NAME, D.SKILL_LEVEL, CASE D.CHANGE_TYPE WHEN 'DROP' THEN 'DÜŞÜRME' WHEN 'ADD' THEN 'EKLEME' END CHANGE_TYPE, L.LOGIN_NAME, H.CHANGE_DATE, --COLORCOLUMN HERE FROM CC_RTM_DBA.SKILL_CHANGE_DETAIL_HIST D, CC_RTM_DBA.SKILL_CHANGE_HIST H, CC_RTM_DBA.CFG_AGENT A, CC_RTM_DBA.CFG_SKILL S, CC_RTM_DBA.CFG_RTM_LOGIN L WHERE D.TRX_ID = H.ID AND D.AGENT_ID = A.ID AND D.SKILL_ID = S.ID AND L.LOGIN_ID = H.CHANGE_USER ORDER BY D.TRX_ID DESC, A.AGENT_NAME, SKILL_NAME, CHANGE_TYPE

and picture is here :

最满意答案

您可以将函数dense_rank（）与mod（）结合使用。

select trxid, mod(dense_rank() over (order by trxid), 2) as color from t

---

在下面的示例中， dense_rank()给出值1,1,1,2,2,3,4。 mod()将偶数值更改为1，将奇数更改为0。

with t as (select 144 trxid, '..' c1, '..' c2 from dual union all select 144 trxid, '..' c1, '..' c2 from dual union all select 144 trxid, '..' c1, '..' c2 from dual union all select 151 trxid, '..' c1, '..' c2 from dual union all select 151 trxid, '..' c1, '..' c2 from dual union all select 167 trxid, '..' c1, '..' c2 from dual union all select 185 trxid, '..' c1, '..' c2 from dual) select trxid, c1, c2, mod(dense_rank() over (order by trxid), 2) as color from t trxid c1 c2 color 144 .. .. 1 144 .. .. 1 144 .. .. 1 151 .. .. 0 151 .. .. 0 167 .. .. 1 185 .. .. 0

You could use function dense_rank() in combination with mod().

select trxid, mod(dense_rank() over (order by trxid), 2) as color from t

---

In the example below dense_rank() gives as values 1,1,1,2,2,3,4. mod() changes even values to 1 and odd to 0.

with t as (select 144 trxid, '..' c1, '..' c2 from dual union all select 144 trxid, '..' c1, '..' c2 from dual union all select 144 trxid, '..' c1, '..' c2 from dual union all select 151 trxid, '..' c1, '..' c2 from dual union all select 151 trxid, '..' c1, '..' c2 from dual union all select 167 trxid, '..' c1, '..' c2 from dual union all select 185 trxid, '..' c1, '..' c2 from dual) select trxid, c1, c2, mod(dense_rank() over (order by trxid), 2) as color from t trxid c1 c2 color 144 .. .. 1 144 .. .. 1 144 .. .. 1 151 .. .. 0 151 .. .. 0 167 .. .. 1 185 .. .. 0