MYSQL:我可以在子查询中使用外部查询表别名吗

Question

这是我的查询:

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'

我想做什么？假设有 Table1 有 10 个学生姓名，现在 Table2 有 10 条记录(行)用于 table1 中的每个学生。如果我尝试在两个表上使用连接，它将返回 100 条记录(每个学生 10 条记录)。我想要的是，限制每个学生 5 条记录的加入返回的结果所以，我应该为每个学生获得 5 条记录，因此有 50 行输出

表结构( Assets ):

ID | TypeID | CategoryID | Worth | isActive 
| CreationDate | ExpiryDate Value | AssetOwner 

表结构(用户事件)|编号 |事件类型 ID |用户名 |时间 | onAssetID |评论ID

注意事项:1)两个表的ID都是主键2) useractivity中的onAssetID和commentID是外键引用asset Table中的ID

如果您需要更多详细信息，请随时告诉我

Answer 1

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

对此的回答:-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;