MS Access查询的答案是冲突的原因..... [英] MS Access Query's Answer is Conflict why.....

问题描述

你好，

我在女士访问中写了一个查询，如下所示

Hello,

I write one query in Ms access as follow

SELECT * from EmployeePaymentDetails where EmpId =@EmpID and PayID= ( select MAX(PayId) from  EmployeePaymentDetails where PayDate = ( select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID) ) and PayDate = ( select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID)

我的表有4条记录，如下所示

my table has 4 records as follows

EmployeePaymentDetails
PayID PayDate	      EmpID	AmountPaid	RemainingAmt
1   	19/05/2010	1	0	         5000
1   	19/06/2010	1	0	         10000
2   	19/05/2010	2	0	         5000
2   	19/06/2010	2	0	         10000

如果我通过EmpId = 2，它将为我提供正确的答案，如RemainingAmt = 10000，但是如果我通过EmpId = 1，则dr不包含任何原因?

它应该给我答案RemainingAmt = 10000

if I pass EmpId = 2 it give me correct anwser as RemainingAmt = 10000 but if I pass EmpId = 1 dr doesn''t contains any thing why?

it should give me answer as RemainingAmt= 10000

推荐答案

此语句返回2作为值.

This statement is returning 2 as the value.

(select MAX(PayId) from  EmployeePaymentDetails where PayDate = ( select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID) )

评估为SELECT MAX(PayId)WHERE PayDate =``19/06/2010''.因此，您的总体评价为

That''s evaluating as SELECT MAX(PayId) WHERE PayDate = ''19/06/2010''. So your statement overall is evaluating as

SELECT * from EmployeePaymentDetails where EmpId = 1 and PayID = 2

...这当然不返回任何记录.

由于PayID并非唯一，因此此逻辑并没有真正为您提供任何服务.下面呢..?

...which of course returns no records.

Since PayID isn''t unique, this logic doesn''t really offer you anything. How about the below..?

SELECT 
    *
FROM 
    EmployeePaymentDetails
WHERE 
    (((EmployeePaymentDetails.EmpID)=[@EmpID]) 
AND 
    ((EmployeePaymentDetails.PayDate) =
(
    select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID))
);

这篇关于MS Access查询的答案是冲突的原因.....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！