• 主页

# 求SQL语句!

microchu 2003-06-25 01:15:11

...全文
3 点赞 收藏 16

16 条回复

beckhambobo 2003-06-26
greatest(..)得参数中最大值,least(..)得参数最小值.

microchu 2003-06-26

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);

microchu 2003-06-26
TO:beckhambobo(beckham)

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

microchu 2003-06-25
1,3和1,2 是两个方向的,简单点这些方向好像就是城市与城市之间的关系,如北京和上海
,北京和广州,

shuipipi 2003-06-25

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

beckhambobo 2003-06-25

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

microchu 2003-06-25
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

microchu 2003-06-25

TO:jiezhi(浪子) 偶承认偶的句子有毛病,但先给出解决方案吧,我想还不至于歧义

black_snail 2003-06-25

beckhambobo 2003-06-25
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

BlueskyWide 2003-06-25
sorry,应该是group by

jiezhi 2003-06-25

bzszp 2003-06-25

select count(*) from tbname t1,tbname t2
where t1.d_to=t2.d_from(+)
and t1.d_from=t2.d_to;

BlueskyWide 2003-06-25
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;

snowy_howe 2003-06-25

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

Michaelyfj 2003-06-25

1.7w+

Oracle 基础和管理