一个排序问题

显示全部楼层 · 2008-7-25 12:30:27

有一个表t_org(id int primary key,
m1 int,
m2 int,
m3 int,
m4 int,
m5 int);
另外一个临时表t_temp与t_org结构一致。
现在要比较输出t_org表中两两记录之间列相同个数(id除外)，比如3个相同的记录。
t_org表中初始数据如下：
(001,1,2,3,4,5);
(002,2,2,3,4,5);
(003,1,3,3,4,6);
(004,1,4,7,8,5);
(005,5,3,3,4,5);
从第一条记录开始遍历，将两两之间列(id除外)相同的记录插入t_temp，并将每两个相同记录隔开，得到如下记录：
(001,1,2,3,4,5);
(002,2,2,3,4,5);
(888,null,null,null,null,null);--一律用此记录分隔
(001,1,2,3,4,5);
(003,1,3,3,4,6);
(888,null,null,null,null,null);
(002,2,2,3,4,5);
(005,5,3,3,4,5);
(888,null,null,null,null,null);
(003,1,3,3,4,6);
(005,5,3,3,4,5);
(888,null,null,null,null,null);
再用Sys_Refcursor取结果集，但取的结果集并不是严格按照比较插入的顺序输出的。
若要达到理想输出结果，怎样排序？
欢迎前来解惑，甚谢！

千问 · 2008-7-25 12:30:27

最简单的做法是在t_temp表加个字段，用来把插入的数据顺序编号，再按照这个字段排序
或者在插入的时候用append，按rowid排序
[ 本帖最后由 homeworld80 于 2008-9-18 18:54 编辑 ]

千问 · 2008-7-25 12:30:27

直接用SQL不行吗？
with vw_t as (
select id,1 as col_id, m1 as col_value from t_org
UNION ALL
select id,2 as col_id, m2 as col_value from t_org
UNION ALL
select id,3 as col_id, m3 as col_value from t_org
UNION ALL
select id,4 as col_id, m4 as col_value from t_org
UNION ALL
select id,5 as col_id, m5 as col_value from t_org
)
SELECT a.id as id1
,b.id as id2
,sum(case when a.col_value=b.col_value then 1 else 0 end) as cnt
FROM vw_t a,vw_t b
WHERE a.idb.id and a.col_id = b.col_id
GROUP BY a.id,b.id;
如果你只想要输出值相同的组合，就把a.col_value=b.col_value 加到where中。