select S_ID,S_STATUS,P_KIND,BUS_STRING from EHT
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000000
现在具体需求如下:
S_STATUS:如果是1 10 11 状态统一为1
否则为0
P_KIND:如果是1 状态为1
如果是0 5 状态为0
问题一:功能串为50位,把刚才的放至49,50位上去
例如
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000000
就变为
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000011问题二:或者把刚才的补至51,52位上去,功能串由50位变为52委
id 状态1 状态2 功能串(52位)
1 1 1 0000000010000000000010000000000000000000000000000011
问题一40分,问题二60分
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000000
现在具体需求如下:
S_STATUS:如果是1 10 11 状态统一为1
否则为0
P_KIND:如果是1 状态为1
如果是0 5 状态为0
问题一:功能串为50位,把刚才的放至49,50位上去
例如
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000000
就变为
id 状态1 状态2 功能串(50位)
1 1 1 00000000100000000000100000000000000000000000000011问题二:或者把刚才的补至51,52位上去,功能串由50位变为52委
id 状态1 状态2 功能串(52位)
1 1 1 0000000010000000000010000000000000000000000000000011
问题一40分,问题二60分
S_ID NOT NULL VARCHAR2(30)
S_STATUS NUMBER(2)
P_KIND NUMBER(12)
BUS_STRING VARCHAR2(128)
希望还注意一下SQL效率,小弟先谢谢了!
主要是Oracle我不是太懂...
decode(P_KIND,1,1,decode(P_KIND,0,0,decode(P_KIND,5,0,P_KIND))) as P_KIND,
case when decode(S_STATUS,1,1,decode(S_STATUS,10,1,decode(S_STATUS,11,1,0))) = 1
then substr(BUS_STRING,0,48) || '11'
else BUS_STRING
end case as BUS_STRING
from EHT;
select S_ID, decode(S_STATUS,1,1,decode(S_STATUS,10,1,decode(S_STATUS,11,1,0))) as S_STATUS,
decode(P_KIND,1,1,decode(P_KIND,0,0,decode(P_KIND,5,0,P_KIND))) as P_KIND,
case when decode(S_STATUS,1,1,decode(S_STATUS,10,1,decode(S_STATUS,11,1,0))) = 1
then BUS_STRING || '11'
else BUS_STRING
end case as BUS_STRING
from EHT;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
S_ID NUMBER Y
S_STATUS NUMBER Y
P_KIND NUMBER Y
BUS_STRING VARCHAR2(50) Y SQL>
SQL> INSERT INTO EHT SELECT 1,1,1,'00000000100000000000100000000000000000000000000000' FROM DUAL;1 row insertedSQL> INSERT INTO EHT SELECT 2,10,5,'00000000100000000000100000000000000000000000000000' FROM DUAL;1 row insertedSQL> SELECT * FROM EHT; S_ID S_STATUS P_KIND BUS_STRING
---------- ---------- ---------- --------------------------------------------------
1 1 1 00000000100000000000100000000000000000000000000000
2 10 5 00000000100000000000100000000000000000000000000000SQL>
SQL> SELECT A.S_ID,B.SS,B.PP,SUBSTR(A.BUS_STRING,1,48)||B.SS||B.PP FROM EHT A
2 INNER JOIN (
3 SELECT S_ID,CASE S_STATUS WHEN 1 THEN 1 WHEN 10 THEN 1 WHEN 11 THEN 1 ELSE 0 END SS,
4 CASE P_KIND WHEN 1 THEN 1 WHEN 0 THEN 0 WHEN 5 THEN 0 END PP FROM EHT) B
5 ON A.S_ID=B.S_ID; S_ID SS PP SUBSTR(A.BUS_STRING,1,48)||B.S
---------- ---------- ---------- ----------------------------------------------------
1 1 1 00000000100000000000100000000000000000000000000011
2 1 0 00000000100000000000100000000000000000000000000010
SELECT A.S_ID,B.SS,B.PP,A.BUS_STRING||B.SS||B.PP FROM EHT A
INNER JOIN (
SELECT S_ID,CASE S_STATUS WHEN 1 THEN 1 WHEN 10 THEN 1 WHEN 11 THEN 1 ELSE 0 END SS,
CASE P_KIND WHEN 1 THEN 1 WHEN 0 THEN 0 WHEN 5 THEN 0 END PP FROM EHT) B
ON A.S_ID=B.S_ID;
FROM(
select S_ID,
CASE WHEN S_STATTUS=1 THEN 1
WHEN S_STATUS=10 THEN 1
WHEN S_STATUS=11 THEN 1
ELSE 0
END S_STATUS,
CASE WHEN P_KIND='05' THEN 0
ELSE 1
END P_KIND,
SUBSTR(BUS_STRING,1,48) BUS_STRING
FROM EHT
)SELECT S_ID,S_STATUS,P_KIND,BUS_STRING||S_STATUS||P_KIND
FROM(
select S_ID,
CASE WHEN S_STATTUS=1 THEN 1
WHEN S_STATUS=10 THEN 1
WHEN S_STATUS=11 THEN 1
ELSE 0
END S_STATUS,
CASE WHEN P_KIND='05' THEN 0
ELSE 1
END P_KIND,
BUS_STRING
FROM EHT
)
decode(p_kind,1,1,0) p_kind,
substr(bus_string,1,48) || decode(s_status,1,1,10,1,11,1,0) || decode(p_kind,1,1,0)
FROM eht; SELECT s_id,decode(s_status,1,1,10,1,11,1,0) s_status,
decode(p_kind,1,1,0) p_kind,
bus_string || decode(s_status,1,1,10,1,11,1,0) || decode(p_kind,1,1,0)
FROM eht;