求SQL语句!

Question

select count(id) from a 
group by greatest(d_from,d_to)||least(d_from,d_to);

Answer 1

select tab1.f1,tab1.t1,count(*)/2 from (select d_from f1,d_to t1 from 链路方向表) tab1,(select d_from f2,d_to t2 from 链路方向表) tab2 where tab1.f1=tab2.t2 and tab1.t1=tab2.f2 group tab1.f1,tab1.t1;

Answer 2

如果是一对一的关系
select count(*) from tbname t1,tbname t2
where t1.d_to=t2.d_from(+)
and t1.d_from=t2.d_to;

Answer 3

select d_from,d_to,sum(num) from 
(select D_From,D_To,count(1) num from table_name group by D_From,D_To
union all
select D_To D_From,D_From D_To,count(1) num from table_name group by D_From,D_To)
group by d_from,d_to

Answer 4

正在想用connect by , start with ,好象不行...郁闷

Answer 5

感谢各位高人,偶挨个试,待会给结果.
TO:jiezhi(浪子) 偶承认偶的句子有毛病,但先给出解决方案吧,我想还不至于歧义

Answer 6

snowy_howe(天下有雪)的方法测试如下:
select greatest(d_from,d_to)||least(d_from,d_to),count(id)
from d_cir
group by greatest(d_from,d_to)||least(d_from,d_to);
结果如下:GREATEST(d_from,d_to)||LEAST(d_from,d_to)         COUNT(ID)
10                                                    2
1110                                                  21
1210                                                  24
1310                                                  18
1410                                                  12
1412                                                  7
1510                                                  12
1610                                                  12
1612                                                  7
1614                                                  4
1710                                                  35
1712                                                  4
1713                                                  3
1810                                                  16
1817                                                  1
1910                                                  11
1912                                                  1
1917                                                  2
20                                                    4
30                                                    2
40                                                    2测试结果数量计算是正确的,但是不能确定这个数量是哪一个方向上的.

Answer 7

可以这样嘛：
select greatest(d_from,d_to),least(d_from,d_to),count(1)
from aa
group by greatest(d_from,d_to),least(d_from,d_to);另外修改自已语句：
select d_from,d_to,sum(num) from 
(select D_From,D_To,count(1) num from aa group by D_From,D_To
union all
select D_To D_From,D_From D_To,count(1) num from aa group by D_To,D_From)
group by d_from,d_to

Answer 8

大致写了一下，竟跟 beckhambobo(beckham) 的思路一样：
select d_from, d_to, sum(rec_sum) 
from 
(select a.d_from, a.d_to, 2*count(*) rec_sum from tab a, tab b where a.d_from = b.d_to and a.d_to = b.d_from group by a.d_from, a.d_to)
group by d_from, d_to不过，总感觉好像跟你的真实想法不大一致？泥说“当起点变为终点，终点变为起点时，认为这是同一方向上的。”这条件是当且仅当么？？比如1，3和1，2还算是一个方向么？？刚才写的是把泥给的条件做为“当且仅当”～

Answer 9

TO:beckhambobo(beckham)
   你的方法查询的结果每个方向都重复了一次
结果如下(部分,全部共58条,实际应该是29条):
   D_FROM    D_TO   SUM(NUM)
--------- --------- ---------
        0         1         2
        0         2         4
        0         3         2
        0         4         2
        0         5         2
        0         6         2
        0         7         2
        0         8         2
        1         0         2
        2         0         4
        2         4         2
        2         6         2
        2         7         2
        2         8         2
        3         0         2
        4         0         2
        4         2         2
        5         0         2
        6         0         2
        6         2         2
        7         0         2

Answer 10

对语句
select greatest(d_from,d_to),least(d_from,d_to),count(1)
from aa
group by greatest(d_from,d_to),least(d_from,d_to);中group by greatest(d_from,d_to),least(d_from,d_to);不太明白,高手给个解释好吗?

Answer 11

greatest(..)得参数中最大值,least(..)得参数最小值.
所以，当(D_From = 1,D_To= 2和D_From =2 ,D_To = 1)看成同一分组了。