比较头痛的一个查询语句,请指导!
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)
这样可以查出所有人的信息
[解决办法]
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'
[解决办法]
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'
[解决办法]
是有一定的难度啊
[解决办法]
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