SQLServer 查找单条记录

[meceky]

  <table>
    <tr><td>Id</td><td>Uid</td><td>Name</td></tr>
    <tr><td>1</td><td>1</td><td>name1</td></tr>
    <tr><td>2</td><td>1</td><td>name2</td></tr>
    <tr><td>3</td><td>1</td><td>name1</td></tr>
    <tr><td>2</td><td>1</td><td>name2</td></tr>
  </table>

Id为主键，如何从这个表根据Uid选出记录，且每个UId只选出一条记录。

[--小F--]

select * from [table] t where id=(select max(id) from [table] where uid=t.uid)

[--小F--]

不过看你的数据 貌似ID也重复了 不是主键么？？怎么会重复？

[中国风]

寫一個給樓主參照一下

DECLARE @x XML
SET @x='<table> <tr><td>Id</td><td>Uid</td><td>Name</td></tr> <tr><td>1</td><td>1</td><td>name1</td></tr> <tr><td>2</td><td>1</td><td>name2</td></tr> <tr><td>3</td><td>1</td><td>name1</td></tr> <tr><td>2</td><td>1</td><td>name2</td></tr> </table> '

;WITH a
AS
(
SELECT C.v.value('.','nvarchar(100)') AS VALUE,row=ROW_NUMBER()OVER(ORDER BY (SELECT 1))
FROM  @x.nodes('/table/tr/td')C(v)
)
SELECT VALUE AS 'UID' 
FROM a  AS b 
WHERE exists(SELECT 1 FROM a WHERE b.row%3=row AND Value='UID' AND VALUE<>b.Value)
/*
1
1
1
1
*/

[meceky]

[Quote=引用 2 楼 fredrickhu 的回复:]

不过看你的数据 貌似ID也重复了 不是主键么？？怎么会重复？
[/Quote]
呵呵，那个是我写错了！