比较头痛的一个查询语句，请指导！
select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,
(select ABS (Transactions.GrossAmount)
from Transactions
where GroupType = 'Allocation'
and LinkType <> 'Payment'and entityid = 137383) as 'Gross Payment Plan Amount'

from Transactions
inner join Person on Transactions.EntityID = Person.ID
inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID
inner join Student on Person.ID = Student.ID
right join Enrolment on Student.ID = Enrolment.StudentID
inner join Stream on Enrolment.StreamID = Stream.ID

请问如何修改子查询，能让entityid等于Person.ID (137383为一个Person的ID)
这样可以查出所有人的信息

[解决办法]

SQL code

select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,ABS(A7.GrossAmount) as 'Gross Payment Plan Amount' from Transactions  inner join Person on Transactions.EntityID = Person.ID  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID  inner join Student on Person.ID = Student.ID  right join Enrolment on Student.ID = Enrolment.StudentID   inner join Stream on Enrolment.StreamID = Stream.ID   INNER JOIN Transactions AS A7 ON (ENTITYID=Person.ID)   WHERE A7.GroupType='Allocation' AND LinkType <> 'Payment'
[解决办法]
SQL code
select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,ABS(ISNULL(A7.GrossAmount,0)) as 'Gross Payment Plan Amount' from Transactions  inner join Person on Transactions.EntityID = Person.ID  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID  inner join Student on Person.ID = Student.ID  right join Enrolment on Student.ID = Enrolment.StudentID   inner join Stream on Enrolment.StreamID = Stream.ID   LEFT INNER JOIN Transactions AS A7 ON (ENTITYID=Person.ID)   WHERE A7.GroupType='Allocation' AND LinkType <> 'Payment'
[解决办法]
是有一定的难度啊
[解决办法]
SQL code
select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,  (select top 1 ABS (Transactions.GrossAmount)    from Transactions  where GroupType = 'Allocation'    and LinkType <> 'Payment'and entityid = Person.ID) as 'Gross Payment Plan Amount'from Transactions  inner join Person on Transactions.EntityID = Person.ID  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID  inner join Student on Person.ID = Student.ID  right join Enrolment on Student.ID = Enrolment.StudentID    inner join Stream on Enrolment.StreamID = Stream.ID