mysql join 字段重复,在mysql中JOIN上的列名重复

I've got a problem with this sql

SELECT COUNT( * )

FROM (

SELECT *

FROM `user` `t`

JOIN `user_relation` r ON ( t.user_id = r.follower_id

OR t.user_id = r.user_id )

WHERE r.status = "active"

AND (

r.user_id =125

OR r.follower_id =125

)

AND t.user_id !=125

GROUP BY t.username

)sq

I always get an error: "#1060 - Duplicate column name 'user_id' " Can anyone help/explain, what I did wrong?

Thanks in advance

Jan

解决方案

You need to provide aliased column in inner query

SELECT COUNT( * )

FROM (

SELECT t.*

FROM `user` `t`

JOIN `user_relation` r ON ( t.user_id = r.follower_id

OR t.user_id = r.user_id )

WHERE r.status = "active"

AND (

r.user_id =125

OR r.follower_id =125

)

AND t.user_id !=125

GROUP BY t.username

)sq

Since you are interested in count(*) only you can return either t.* or r.* or any one column , the condition being that column names in inner query MUST be unique or if are same in both tables than prefixed with table alias name.