在Teradata数据库查询以下sql语句时:
select caller.*,catcher.*
from
(select top 300 c_usr_nbr,sum(int_cnt),count(c_usr_nbr)
from sna.yk_gmr_frd_out
group by c_usr_nbr
order by sum(int_cnt) desc) caller
inner join
(select top 300 vc_b_nbr,sum(int_cnt),count(vc_b_nbr)
from sna.yk_gmr_frd_out
group by vc_b_nbr
order by sum(int_cnt) desc) catcher
on caller.c_usr_nbr=catcher.vc_b_nbr
报错如下:
error select failed 3706 syntax error: all expressions in a derived table must hava an explicit name。后来发现是由于连接的2个表中,字段sum(int_cnt)和字段count(c_usr_nbr)冲突,修改如下:
select caller.*,catcher.*
from
(select top 300 c_usr_nbr,sum(int_cnt) as sum1,count(c_usr_nbr) as count1
from sna.yk_gmr_frd_out
group by c_usr_nbr
order by sum(int_cnt) desc) caller
inner join
(select top 300 vc_b_nbr,sum(int_cnt) as sum2,count(vc_b_nbr) as count2
from sna.yk_gmr_frd_out
group by vc_b_nbr
order by sum(int_cnt) desc) catcher
on caller.c_usr_nbr=catcher.vc_b_nbr
order by sum1 desc;