在sql Server-2005中查找Pramary ID中的最后一条记录
问题描述：
嗨......我在每张身份证记录中都找到了最后一条记录。
Hi....I have Find last record in each ID records.
ID SID Amount Pay Due
1 1 10000 2000 8000
2 2 10000 3000 7000
3 3 10000 5000 5000
4 1 10000 3000 5000
5 2 10000 5000 2000
6 3 10000 2000 3000
7 1 10000 1000 4000
8 2 10000 1000 1000
例如：Out Put。
For Eg:Out Put.
SID Due
1 4000
2 1000
3 3000
答
试试这个：
Try this:
SELECT t1.SID, t1.DUE
FROM YourTable AS t1 RIGHT JOIN (
SELECT SID, MAX(ID) AS ID
FROM YourTable
GROUP BY SID) AS t2 ON t1.SID =t2.SID AND t1.ID=t2.ID
ORDER BY t1.SID
>
检查以下脚本
Hi,
Check the following script
DECLARE @AmtDtls TABLE(ID INT,SID INT, Amount NUMERIC(9,0), Pay NUMERIC(9,0), Due NUMERIC(9,0))
INSERT INTO @AmtDtls (ID, SID, Amount, Pay , Due )
SELECT 1, 1, 10000, 2000, 8000
UNION ALL
SELECT 2, 2, 10000, 3000, 7000
UNION ALL
SELECT 3, 3, 10000, 5000, 5000
UNION ALL
SELECT 4, 1, 10000, 3000, 5000
UNION ALL
SELECT 5, 2, 10000, 5000, 2000
UNION ALL
SELECT 6, 3, 10000, 2000, 3000
UNION ALL
SELECT 7, 1, 10000, 1000, 4000
UNION ALL
SELECT 8, 2, 10000, 1000, 1000
SELECT A.SID, A.Due
FROM @AmtDtls A
INNER JOIN (SELECT MAX(ID) ID, SID FROM @AmtDtls GROUP BY SID) T ON T.ID=A.ID
ORDER BY A.SID
问候，
GVPrabu
Regards,
GVPrabu