一个表 :-----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
c b 20
-----------------------
f h 9
-----------------------
h f 9
-----------------------想要得到的结果:
-----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
f h 20
-----------------------c b ,h f 作为重复数据去除。如何写 sql 语句?高手帮忙。
列1 列2 列3
-----------------------
b c 20
-----------------------
c b 20
-----------------------
f h 9
-----------------------
h f 9
-----------------------想要得到的结果:
-----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
f h 20
-----------------------c b ,h f 作为重复数据去除。如何写 sql 语句?高手帮忙。
-----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
f h 9
-----------------------
(
select newc2,min(id) as mi from (
select id,group_concat(newc1 SEPARATOR '') as newc2 from (
select *,列1 as newc1
from ttf
union all
select *,列2
from ttf
order by id,newc1) a
group by id) b
group by newc2) d on c.id=d.mi加入自增字段ID,工作表为TTF,自行修改一下
create table ttf
select * from 临时表
(
select newc2,min(id) as mi from
( select id,group_concat(newc1 SEPARATOR '') as newc2 from
( select *,列1 as newc1 from ttf
union all
select *,列2 from ttf order by id,newc1
) a group by id
) b
group by newc2
)
d on c.id=d.mi
yes
select if(concat(c1,'-',c2)> concat(c2,'-',c1),concat(c1,'-',c2),concat(c2,'-',c1)) as xx from (
SELECT a.c1 as c1,a.c2 as c2,a.c3 as c3 FROM test a,test b
where a.c1=b.c2 and a.c2=b.c1 and a.c3=b.c3
) t group by xx) yy,test a
where yy.xx=concat(a.c1,'-',a.c2);
b和c反了
h和f反了
你自己改改吧,o(∩_∩)o...哈哈!
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
形式跟你所问的表形式很像
----------------------
列1 列2 列3
-----------------------
b c 20
-----------------------
c b 20
-----------------------
f h 9
-----------------------
h f 9
-----------------------
我用了衍生列的办法,生成一列ID号,然后将ID号相加或者相乘,得到重复的一列,然后用MAX函数选出其中一列,在进行JOIN 得到结果!
楼主可以测试下 具体的顺序可以用MAX或者MIN来控制。
假设列1、列2为字符型,列3 为数字型
select dd.列1,dd.列2,dd.列3 from
(select a.列1,a.列2,a.列3,a.newid,count(b.列1) as newid1 from (
select *,ascii(列1)+ascii(列2)+列3 as newid from ttf) a
left join
(select *,ascii(列1)+ascii(列2)+列3 as newid from ttf) b
on a.newid=b.newid and a.列1>=b.列1
group by a.列1,a.列2,a.列3,a.newid) dd
inner join
(select newid,min(newid1) as mi from (
select a.列1,a.列2,a.列3,a.newid,count(b.列1) as newid1 from (
select *,ascii(列1)+ascii(列2)+列3 as newid from ttf) a
left join
(select *,ascii(列1)+ascii(列2)+列3 as newid from ttf) b
on a.newid=b.newid and a.列1>=b.列1
group by a.列1,a.列2,a.列3,a.newid) aa group by newid) ee
on dd.newid=ee.newid and dd.newid1=ee.mi