表名:量距起点名 终点名 距离
A B 350.583
B C 1261.512
B A 350.615
C B 1261.501
C D1 18.077
........................如何去掉重复的边,例如:“A B”和“B A”只能算一条边,例如上面5条记录查询结果其边数为3,而不是5。这个表记录最多时可达3800个记录,这个SQL语句如何写
A B 350.583
B C 1261.512
B A 350.615
C B 1261.501
C D1 18.077
........................如何去掉重复的边,例如:“A B”和“B A”只能算一条边,例如上面5条记录查询结果其边数为3,而不是5。这个表记录最多时可达3800个记录,这个SQL语句如何写
select t.num1,t.num2,count(*)
from (
select num1,num2 from tx
union all
select num2,num1 from tx
) t
group by t.num1,t.num2;
| num1 | num2 |
+------+------+
| a | b |
| b | c |
| b | a |
| c | b |
| c | d |
+------+------+
5 rows in set (0.00 sec)
+------+------+----------+
| num1 | num2 | count(*) |
+------+------+----------+
| a | b | 2 |
| b | c | 2 |
| c | d | 1 |
+------+------+----------+
3 rows in set (0.00 sec)
from (
select num1,num2 from tx
union select num2,num1 from tx
) t
declare @num1 int,@num2 int
select @num1=count(1) from [量距]
select @num2=count(1) from [量距] a
where not exists(
select 1 from [量距]
where a.[起点名]=[终点名] and a.[终点名]=[起点名] and a.[距离]>[距离])select (@num1+@num2)/2
where 起点名>终点名
and exists (select 1 from 量距 where 终点名=a.起点名 and 起点名=a.终点名)数据库的问题还是交给数据库专家。