求一条查询sql语句。。。。。。

[yinxiaoqi]

这里有2个表
ZC_Admission

ID         Adname    Adsex
260          a        12
261          b        13
262          c        14
263          d        15

ZC_PaymentForm

ID         Admission_ID       ptime    pcod
1            260              1        2
2            261              2        2
3            261              3        4
4            262              4        5
5            263              4        5

然后我执行这条SQl语句：

select * from(select row_number()over(order by tempColumn)
tempRowNumber,* from(select top 10 tempColumn=0,* from ZC_Admission)t)tt
left join   a on ID=a.Admission_ID where tempRowNumber>0
结果是这样的，
ID         Adname    Adsex       ID  Admission_ID       ptime    pcod
260         a         12         1     260              1         2
261         b         13         2     261              2         2
261         b         13         2     261              2         2
262         c         14         4     262              4         5
263         d         15         5     263              4         5

我的意思是现在 ID等于261 的有2条 都查出来了，我想 让ID等于261 的这两条记录 根据ID的大小只显示1条记录，保留ID最大的记录。

[Silence_Smile]

group by ID

很久没写sql了。。。错了别骂我。。。

[queatin_man]

select a.adname... b.ptime... from table1 a,table2 b where a.id=261 and b.id=261 group by a.id;

[yinxiaoqi]

[Quote=引用 1 楼 silence_smile 的回复:]
SQL code
group by ID

很久没写sql了。。。错了别骂我。。。
[/Quote]

呵呵 楼上的 说笑了，我也没怎么写了，所以才出来求助啊！

[yinxiaoqi]

晕 版主 在吗？我的帖子 发错位置了！晕了！

[xk1126]

261 b 13 2 261 2 2
261 b 13 2 261 2 2
你这两条数据不是一样吗！~~
你是想根据那个id啊
比较不会max(id)啊，不就得到最大个id了吗

[tanbin_0521]

select a.Adname,a.Adsex, b.ID,b.Admission_ID,b.ptime,b.pcod from ZC_Admission a left join ZC_PaymentForm b
on a.ID=b.Admission_ID where a.ID=261 and b.ID=(selset max(ID) from ZC_Admission )

更具B表的ID

[tanbin_0521]

select top 1 a.Adname,a.Adsex, b.ID,b.Admission_ID,b.ptime,b.pcod from ZC_Admission a left join ZC_PaymentForm b
on a.ID=b.Admission_ID where a.ID=261 group by b.id;

[tianzezhong]

用关键字distinct

[bob1314]

不是有个防重复的关键字么。

[bb12152205gg]

distinct应该可以解决你的问题

[mengyi66]

select * from Admision a,Paymentform p where p.id = (select max(p.id) from Admision a ,Paymentform p where a.id = 261 and a.id = p.Admision_id group by p.id);

[sweetbbs]

if object_id('[ZC_Admission]') is not null drop table [ZC_Admission]
go
create table [ZC_Admission]([ID] int,[Adname] varchar(1),[Adsex] int)
insert [ZC_Admission]
select 260,'a',12 union all
select 261,'b',13 union all
select 262,'c',14 union all
select 263,'d',15
GO
if object_id('[ZC_PaymentForm]') is not null drop table [ZC_PaymentForm]
go
create table [ZC_PaymentForm]([ID] int,[Admission_ID] int,[ptime] int,[pcod] int)
insert [ZC_PaymentForm]
select 1,260,1,2 union all
select 2,261,2,2 union all
select 3,261,3,4 union all
select 4,262,4,5 union all
select 5,263,4,5
GO

SELECT *
FROM ZC_Admission A
LEFT JOIN ZC_PaymentForm B ON A.ID=B.Admission_ID
AND B.ID IN(SELECT MIN(ID) FROM ZC_PaymentForm WHERE Admission_ID=B.Admission_ID)

/**
ID          Adname Adsex       ID          Admission_ID ptime       pcod
----------- ------ ----------- ----------- ------------ ----------- -----------
260         a      12          1           260          1           2
261         b      13          2           261          2           2
262         c      14          4           262          4           5
263         d      15          5           263          4           5

(4 行受影响)
**/

[sweetbbs]

刚才那个是保留ID最小的记录，保留ID最大的记录用下面的语句

SELECT *
FROM ZC_Admission A
LEFT JOIN ZC_PaymentForm B ON A.ID=B.Admission_ID
AND B.ID IN(SELECT MAX(ID) FROM ZC_PaymentForm WHERE Admission_ID=B.Admission_ID)

[zfqcarl]

楼主，发错位置没关系，你干嘛不用关键字distinct ？
select distinct * from(select row_number()over(order by tempColumn)
tempRowNumber,* from(select top 10 tempColumn=0,* from ZC_Admission)t)tt
left join a on ID=a.Admission_ID where tempRowNumber>0

写错别骂我哈

[panxing1101]

select distinct * from(select row_number()over(order by tempColumn)tempRowNumber,* 
from(select top 10 tempColumn=0,* from ZC_Admission)t)tt
left join ZC_PaymentForm a on ID=a.Admission_ID
AND a.PF_ID IN(SELECT max(PF_ID) FROM ZC_PaymentForm WHERE Admission_ID=a.Admission_ID)
where tempRowNumber>0

[yinxiaoqi]

谢谢大家 哈，UP 的OK啦！

[zfqcarl]

楼主，上边我发的帖子是 相同的记录显示一条
用group by ID的话，是按照ID 相同的显示一条

[yinxiaoqi]

[Quote=引用 17 楼 zfqcarl 的回复:]
楼主，上边我发的帖子是 相同的记录显示一条
用group by ID的话，是按照ID 相同的显示一条
[/Quote]

呵呵 谢谢你哈，我的问题解决了！