除了游标之外，我还有其他选择吗？

Question

enter code here我正在开发一个复杂的报表，为此我需要从table-1中选择一些数据，然后代表table-1中的数据对tabel-2，tabel-3，table-4中的数据执行一些处理。

喜欢

select a,b,c, from table-1

while not rs(table-1).eof

processing data from table-2,
processing data from table-3,
processing data from table-4

做

我已经试过了，但是子查询不能解决我的问题。据我所知，光标是我唯一的选择，如果有任何其他解决方案，请指导我

致以问候。

当时我正在做的是在客户端处理数据，然后将其保存到数据库中的临时表中，然后从该表中提取记录到水晶报告中。以下是我的代码

rs.Open "SELECT distinct Empid,empSalary as EmpSalary,EmpJobStatus From TEmployee where EmpJobStatus ='HIRED' and EmpID not in ( SELECT distinct Empid From VEmpPromotion where convert(VARCHAR(10), PromSalaryMonth , 101)='" & Format(FirstDate, "mm/dd/yyyy") & "') Union SELECT distinct Empid,promempSalary as EmpSalary,PromEmpJobStatus as EmpJobStatus From VEmpPromotion where PromEmpJobStatus ='HIRED' and convert(VARCHAR(10), PromSalaryMonth , 101)='" & Format(FirstDate, "mm/dd/yyyy") & "'", cn, 3, 2


While Not rs.EOF
rsD1.Open "select sum(DATEDIFF(MINUTE,timein,timeout))/60 as HoursWorked ,count(EmpLEave) as EmpLeave,count(TimeIN) as DaysWorked from Tattendance where EmpId =" & rs![EmpId] & " and convert(VARCHAR(10), Attendate, 101)>='" & Format(FirstDate, "mm/dd/yyyy") & "' and convert(VARCHAR(10), attendate, 101)<='" & Format(LastDate, "mm/dd/yyyy") & "'", cn, 3, 2
rsD2.Open "select sum(amount),SUM(CASE EmpTrancID WHEN 1  then amount  ELSE 0 END) AS 'SalaryPaid',SUM(CASE EmpTrancID WHEN 2  then amount  ELSE 0 END) AS 'SalaryAdvancePaid',SUM(CASE EmpTrancID WHEN 3  then amount  ELSE 0 END) AS 'WeeklyIncentivePaid',SUM(CASE EmpTrancID WHEN 4  then amount  ELSE 0 END) AS 'MonthlyIncentivePaid',SUM(CASE EmpTrancID WHEN 5  then amount  ELSE 0 END) AS 'InitialAdvancePaid',SUM(CASE EmpTrancID WHEN 6  then amount  ELSE 0 END) AS 'OtherPayments',SUM(CASE EmpTrancID WHEN 7  then amount  ELSE 0 END) AS 'SalaryAdvanceBack',SUM(CASE EmpTrancID WHEN 8  then amount  ELSE 0 END) AS 'InitialAdvanceBack'From TSalAdvance where TrancStatus =1 and EmpId =" & rs![EmpId] & " and convert(VARCHAR(10), PaidDate, 101)<= '" & Format(DTSearch, "mm/dd/yyyy") & "' and convert(VARCHAR(10), PaidDate, 101)>= '" & Format(FirstDate, "mm/dd/yyyy") & "'", cn, 3, 2
rsD4.Open "select sum(amount),SUM(CASE EmpTrancID WHEN 8  then amount  ELSE 0 END) AS 'TInitialAdvanceBack',SUM(CASE EmpTrancID WHEN 5  then amount  ELSE 0 END) AS 'TInitialAdvancePaid'From TSalAdvance where TrancStatus =1 and EmpId =" & rs![EmpId] & " and convert(VARCHAR(10), SalaryMonth, 101)<'" & Format(LastDate, "mm/dd/yyyy") & "'", cn, 3, 2
rsD5.Open "select sum(amount),SUM(CASE EmpTrancID WHEN 2  then amount  ELSE 0 END) AS 'SalaryAdvancePaid',SUM(CASE EmpTrancID WHEN 7  then amount  ELSE 0 END) AS 'SalaryAdvanceBack'From TSalAdvance where TrancStatus =1 and EmpId =" & rs![EmpId] & " and convert(VARCHAR(10), SalaryMonth, 101)>'" & Format(LastDate, "mm/dd/yyyy") & "'", cn, 3, 2
If IsNull(rsD5![SalaryAdvancePaid]) Then NextAdvPaid = 0 Else NextAdvPaid = rsD5![SalaryAdvancePaid]
If IsNull(rsD5![SalaryAdvanceBack]) Then NextAdvBAck = 0 Else NextAdvBAck = rsD5![SalaryAdvanceBack]
rsD3.Open "select * from SalarySummaryAll", cn, 3, 2
rsD3.AddNew
rsD3![EmpId] = rs![EmpId]
rsD3![EmpSalary] = rs![EmpSalary]
rsD3![EmpJobStatus] = Trim(rs![EmpJobStatus])
.........
.........
rsD3.Update
rs.MoveNext
Wend

Answer 1

您的查询看起来很长，但您似乎没有在其中使用太多变量。

我不明白为什么你不能在这里使用一些内部和左(外)连接，比如：

insert into SalarySummaryAll(EmpId, EmpSalary, ...)
select (TEmployeeAlias.EmpIdAlias, TEmployeeAlias.EmpSalaryAlias, ...) 
from
  (select .... From TEmployee where ...) as TEmployeeAlias
  inner join 
  (select ... From TSalAdvance where ...) as TSalAdvance Alias
     on ...
  inner join  
  (select ... From TSalAdvance where ...) as TSalAdvance Alias
     on ...
  left outer join
  (select...) as tableN 
     on ...

你试过这样的东西吗？如果是这样，您遇到了什么问题？