mysql 表别名 子查询_MYSQL：我可以在子查询中使用外部查询表别名

Here is my query:

select uact.onAssetID as AssetID, a1.value as AssetValue,

uact.CommentID, a2.value from useractivity uact inner join asset a1 on

uact.onAssetID=a1.ID inner join (select * from asset inner join

useractivity on uact.onAssetID=a1.ID group by a1.ID limit 3) a2 on

uact.CommentID=a2.ID;

Error: ERROR 1054 (42S22): Unknown column 'uact.onassetID' in 'on clause'

What I am trying do?

Imagine there is Table1 with 10 student names, now there is Table2 which has 10 records(rows) for each of the student present in table1. If I try to use join on both tables, it will return 100 records(10 records for each student). What I want is, to limit the result returned by join by 5 records per students

So, I should get 5 records per student hence 50 output rows

Table Structure(asset):

ID | TypeID | CategoryID | Worth | isActive

| CreationDate | ExpiryDate Value | AssetOwner

Table Structure(useractivity)

| ID | ActivityTypeID | UserID | Time | onAssetID | CommentID

Notes:

1) ID for both table is a primary key

2) onAssetID and commentID in useractivity are foriegn keys referring to ID in asset Table

Feel free to let me know if you need more details

解决方案What I am trying do? Imagine there is Table1 with 10 student names,

now there is Table2 which has 10 records(rows) for each of the student

present in table1. If I try to use join on both tables, it will return

100 records(10 records for each student). What I want is, to limit the

result returned by join by 5 records per students So, I should get 5

records per student hence 50 output rows

Answer for this:-

Table1(studentid,.......)

Table2(id,.........,studentid)

select s.*,temp.* from (SELECT @var:=if(@var2= a.studentid,@var+1,1) sno, @var2:= studentid, a.*

FROM Table2 a,(select @var:=0) b, (select @var2:=0) c

order by studentid) temp, Table1 s where temp.sno<=5;

For your table structures:- Table Structure(asset): ID | TypeID |

CategoryID | Worth | isActive | CreationDate | ExpiryDate Value |

AssetOwner Table Structure(useractivity): | ID | ActivityTypeID |

UserID | Time | onAssetID | CommentID

select s.*,temp.*

from

(SELECT @var:=if(@var2= a.onAssetID,@var+1,1) sno, @var2:= onAssetID, a.*

FROM useractivity a,(select @var:=0) b, (select @var2:=0) c

order by onAssetID) temp, asset s

where temp.sno<=5;