SELECT *
FROM Price AS a
WHERE (((Exists (select 1 from Price where a.scancode=scancode and (ScanCode>a.ScanCode or (ScanCode=a.ScanCode and id >a.id ))))=False))
ORDER BY a.ScanCode;
======================
特别是这一句是什么意思
Exists (select 1 from Price where a.scancode=scancode and (ScanCode>a.ScanCode or (ScanCode=a.ScanCode and id >a.id )
FROM Price AS a
WHERE (((Exists (select 1 from Price where a.scancode=scancode and (ScanCode>a.ScanCode or (ScanCode=a.ScanCode and id >a.id ))))=False))
ORDER BY a.ScanCode;
======================
特别是这一句是什么意思
Exists (select 1 from Price where a.scancode=scancode and (ScanCode>a.ScanCode or (ScanCode=a.ScanCode and id >a.id )
如果有,这个exists返回true,否则返回false
不过这个语句有着一个明显的逻辑问题。where a.scancode=scancode and (ScanCode>a.ScanCode or (ScanCode=a.ScanCode and id >a.id ))这个条件有一种可能性不可能出现,假设a.scancode=scancode,那么scancode>a.scancode永远不可能出现scancode>a.scancode,那么整个条件始终都是false
FROM Price AS a
WHERE NOT Exists (
select top 1 * from Price
where (a.scancode=scancode and id>a.id)
OR (ScanCode>a.ScanCode and id>a.id)
)
ORDER BY a.ScanCode;这样的不知道是不是你想要的
这是我的答复,当然也许其他人的也是正确的。不过你改过来的时候改错了,你最好把你期望的需求写出来select * from csdntest1 a
where [ID] IN (SELECT TOP 1 ID from csdntest1 b
where b.[name]=a.[name] order by salary desc,ID desc)测试用例如下
ID name salary nyear
1 a 134 2007
2 b 23 2007
3 c 76 2007
4 a 1351 2007
5 b 324 2007
6 c 230 2007
7 a 46 2007
8 b 98 2007
9 c 721 2007
10 a 1351 2007
11 b 324 2007
12 c 721 2007期望中结果
10 a 1351 2007
11 b 324 2007
12 c 721 2007实际测试结果
10 a 1351 2007
11 b 324 2007
12 c 721 2007
price表
ID scancode price istrue
1 001 12 true
2 002 34 true
3 001 32 false
4 002 36 true
5 001 13 true
6 002 35 falseID scancode price 这三个字段都要,同样一个scandcode只出现一条记录并且要istrue字段为true才行
where [ID] IN (SELECT TOP 1 ID from price b
where b.scancode=a.scancode and istrue='true'
order by b.price desc,ID desc)