- 论坛徽章:
- 0
|
原帖由 DQP 于 2008-12-18 18:14 发表
select * from test t
where exists (
select 1 from test
where t.col3 = col3
and t.col1 = col2
and t.col2 = col1)
...
挺有帮助的 不过查询结果少了一行 还在研究 好了给出结果
自己写的
select test.* from test
join
(
select all_,max(fromcity) as cnt from
(
select test.*,tmp1.id1*tmp2.id2 as all_ from test
left join
(
select fromcity,
(select count(*) as id1 from
(select fromcity from test
union
select tocity from test)
tbl1 where tbl1.fromcity < tbl.fromcity ) as id1 from
(
select fromcity from test
union
select tocity from test
) tbl
) tmp1 on test.fromcity = tmp1.fromcity
left join
(
select fromcity,
(select count(*) as id2 from
(select fromcity from test
union
select tocity from test)
tbl1 where tbl1.fromcity < tbl.fromcity ) as id2 from
(
select fromcity from test
union
select tocity from test
) tbl
) tmp2 on test.tocity = tmp2.fromcity
) tmp3 group by all_
) tmp4 on test.fromcity = tmp4.cnt
顺便自己建立个测试用表 TEST,FROMCITY 和 TOCITY 是TEST表的其中两列
形式很像
----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
c b 20
-----------------------
f h 9
-----------------------
h f 9
-----------------------
用了衍生列的办法,生成一列ID号,然后将ID号相加或者相乘,得到重复的一列,然后用MAX函数选出其中一列,在进行JOIN 得到结果!
具体的顺序可以用MAX或者MIN来控制。
[ 本帖最后由 Lning 于 2008-12-19 09:27 编辑 ] |
|