解决方案 »
- oracle区分大小写??
- Oracle 10g : ORA-12514:listener does not currently know of service requested in connect descriptor
- ORA-12528: TNS: 监听程序: 所有适用例程都无法建立新连接
- 求助:关于LINUX SHELL接受存储过程OUT参数
- JPG文件的存储问题
- oracle 10g 的group by是不是和以前的不一样了?
- imp/exp的简单问题,50分~
- 如何登录sql*plus???
- 不能使用参看表结构,很奇怪......
- ODBC访问API的错误:一个字段第二次读取字段值时错误
- 如何修改wmsys.wm_concat 函数返回字符的数量上限
- oracle大表清除,空间可以继续使用吗?
没怎么看明白你的要求哦...
就是根据T1表的Name, A,B,C,在T2表找到完全匹配的组,A,B,C
然后把GroupID 更新给T1目前T2只存在两个组,而且Name与T1中的Name不完全一致,所以给T1一个新的GroupID
在T2表也找到这样一个GroupID 它下边的Name也正是A,B,C 不多不少
NAME GroupID
A 1
B 1
C 1
A 2
B 2
C 2
明天上班给你看看
...如果一条sql实现的话,使用FUNCTION.....
没有找到更好的方法.... 肯定是有更好的方法.... 下面是我的通过function来解决的思路,没有考虑效率,没有考虑优化:--创建测试数据
CREATE TABLE T1(
NAME VARCHAR2(10),
GROUPID NUMBER(10)
);
CREATE TABLE T2(
NAME VARCHAR2(10),
GROUPID NUMBER(10)
);
INSERT INTO T1 VALUES('A',NULL);
INSERT INTO T1 VALUES('B',NULL);
INSERT INTO T1 VALUES('C',NULL);
INSERT INTO T2 VALUES('A',1);
INSERT INTO T2 VALUES('C',1);
INSERT INTO T2 VALUES('B',2);
INSERT INTO T2 VALUES('C',2);
--写一个function返回相应的GROUPID
CREATE OR REPLACE FUNCTION GETGROUPID(PRM_STR VARCHAR2)
RETURN NUMBER AS
N_GROUPID T2.GROUPID%TYPE;
BEGIN
SELECT GROUPID INTO N_GROUPID FROM
(
SELECT REPLACE(TRANSLATE(PRM_STR,STR,','),',') AS RESULT,GROUPID FROM
(SELECT WMSYS.WM_CONCAT(NAME) AS STR,GROUPID FROM T2 GROUP BY GROUPID)O
)
WHERE RESULT IS NULL;
RETURN N_GROUPID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT MAX(GROUPID) INTO N_GROUPID FROM T2;
RETURN N_GROUPID+1;
END;
--测试function 应当返回相应的结果
SELECT GETGROUPID('B,C') FROM DUAL; --2
SELECT GETGROUPID('A,C') FROM DUAL; --1
SELECT GETGROUPID('A,B,C') FROM DUAL; --3
--完成楼主的要求
UPDATE T1 SET T1.GROUPID = (SELECT GETGROUPID(WMSYS.WM_CONCAT(NAME)) FROM T1);
FROM (SELECT ROW_NUMBER() OVER(ORDER BY FLG) AS RN, GroupID
FROM (SELECT FLG, GroupID
FROM (SELECT '1' AS FLG, T2.GroupID, COUNT(1) AS CN1
FROM T2
INNER JOIN T1 ON T2.NAME = T1.NAME
AND T1.NAME IS NOT NULL
GROUP BY T2.GroupID)
WHERE CN1 = (SELECT COUNT(1) FROM T1)
UNION ALL
SELECT '2' AS FLG, MAX(GroupID) + 1 AS GroupID FROM T2))
WHERE RN = 1)
set t1.groupid =
(SELECT decode(MAX(min_groupid),
-1,
MAX(max_groupid) + 1,
MAX(min_groupid)) groupid
FROM (SELECT MIN(decode(t2.NAME, NULL, -1, t2.groupid)) over(PARTITION BY t2.groupid) min_groupid,
MAX(t2.groupid) over() max_groupid,
t2.*
FROM t1
LEFT JOIN t2 PARTITION BY(t2.groupid)
ON t1.NAME = t2.NAME))