表如下,表示tag之间的相关度。但是有重复的关系,比如java和c的相关度是1 ,那么c和java的相关度为1那行就是重复的了。
tag1 tag2 relation
java c 1
c java 1
java ruby 3
ruby java 3
c c# 4
c# c 4
想得到如下输出,去掉重复的信息
java c 1
java ruby 3
c c# 4改怎么做呢?谢谢
tag1 tag2 relation
java c 1
c java 1
java ruby 3
ruby java 3
c c# 4
c# c 4
想得到如下输出,去掉重复的信息
java c 1
java ruby 3
c c# 4改怎么做呢?谢谢
union
select tag2,tag1,relation from tt
select * from tt1 a
where exits(select 1 from tt1 where tag1+tag2=a.tag2+a.tag1 and a.id<id)上述代码有误
from 表 a
where not exists (select 1 from 表 where tag1=a.tag2 and tag2=a.tag1 and relation=a.relation)
and tag1>tag2
SELECT a.* from tt1 a inner join tt1 b on b.tag1+b.tag2=a.tag2+a.tag1 and a.id<b.id
create table t1 (
`tag1` varchar(32),
`tag2` varchar(32),
`relation` tinyint(4)
) engine=myisam;insert into t1 values ('java','c',1);insert into t1 values ('c','java',1);insert into t1 values ('java','ruby',3);insert into t1 values ('ruby','java',3);insert into t1 values ('c','c#',4);insert into t1 values ('c#','c',4);
| tag1 | tag2 | relation |
+------+------+----------+
| c# | c | 4 |
| ruby | java | 3 |
| java | c | 1 |
+------+------+----------+
3 rows in set (0.00 sec)
select * from tt1 a
where exits(select 1 from tt1 where tag1+tag2=a.tag2+a.tag1 and a.id <id)or
SELECT a.* from tt1 a inner join tt1 b on b.tag1+b.tag2=a.tag2+a.tag1 and a.id <b.id
+------+------+----------+
| tag1 | tag2 | relation |
+------+------+----------+
| java | c | 1 |
| c | java | 1 |
| java | ruby | 3 |
| ruby | java | 3 |
| c | c# | 4 |
| c# | c | 4 |
+------+------+----------+
6 rows in set (0.00 sec)mysql> select *
-> from t_nongfusq a
-> where not exists (select 1 from t_nongfusq where tag1>tag2 and tag1=a.tag2 and tag2=a.tag1 and relation=a.relation);
+------+------+----------+
| tag1 | tag2 | relation |
+------+------+----------+
| java | c | 1 |
| ruby | java | 3 |
| c# | c | 4 |
+------+------+----------+
3 rows in set (0.00 sec)mysql>