表自链接问题，急，在线等，谢谢！！！！！

[摩羯伙夫]

有一个表，结构如下

采购申请单号        采购单号            采购类型
PURCHASEAPPLYNO        PURCHASENO            PURCHASETYPE
PA001                P001                外采
PA001                P002                内采
PA002                P003                外采
PA003                P004                内采

一个采购申请单最多生成两条采购单(一条内采和一条外采)

也可能只生成内采和只生成外采

问题是怎么查询一个结果集，有内采和外采的只检索外采，即排除第二条数据

在线等,谢谢！

[寒江易雪_王孙]

贴你要的结果,被内采外采搞晕了

[摩羯伙夫]

要得到的查询结果为

采购申请单号        采购单号            采购类型
PURCHASEAPPLYNO        PURCHASENO            PURCHASETYPE
PA001                P001                外采
PA002                P003                外采
PA003                P004                内采

[Felixzhaowenzhong]

select * from (
select PURCHASEAPPLYNO,PURCHASENO,PURCHASETYPE
from tablename group by PURCHASEAPPLYNO,PURCHASENO,PURCHASETYPE
having(PURCHASEAPPLYNO)>=2)as a
where PURCHASETYPE='外采'

[寒江易雪_王孙]

select * from Z_PURCHASETBL B
where B.PURCHASETYPE='2' --外采
union
select * from Z_PURCHASETBL a
where A.PURCHASETYPE='1' --内采
and a.PURCHASEAPPLYNO not in
(
select PURCHASEAPPLYNO
from Z_PURCHASETBL group by PURCHASEAPPLYNO
having(count(PURCHASEAPPLYNO))=2
)

[pengxuan]

if object_id('tb') is not null
   drop table tb
go
create table tb
(
 PURCHASEAPPLYNO varchar(10),
 PURCHASENO varchar(10),
 PURCHASETYPE varchar(10)
)
go
insert into tb
select 'PA001','P001','外采' union all
select 'PA001','P002','内采' union all
select 'PA002','P003','外采' union all
select 'PA003','P004','内采'
go
select PURCHASEAPPLYNO,PURCHASENO,PURCHASETYPE from 
(
 select *,row=row_number() over(partition by PURCHASEAPPLYNO order by getdate()) from tb
)t where row=1
go
/*
PURCHASEAPPLYNO PURCHASENO PURCHASETYPE
--------------- ---------- ------------
PA001           P001       外采
PA002           P003       外采
PA003           P004       内采

(3 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-20 16:15:31
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([PURCHASEAPPLYNO] varchar(5),[PURCHASENO] varchar(4),[PURCHASETYPE] varchar(4))
insert [tb]
select 'PA001','P001','外采' union all
select 'PA001','P002','内采' union all
select 'PA002','P003','外采' union all
select 'PA003','P004','内采'
--------------开始查询--------------------------
select * from tb t where PURCHASENO=(select MIN(PURCHASENO) from tb where PURCHASEAPPLYNO=t.PURCHASEAPPLYNO)
----------------结果----------------------------
/* 
PURCHASEAPPLYNO PURCHASENO PURCHASETYPE
--------------- ---------- ------------
PA001           P001       外采
PA002           P003       外采
PA003           P004       内采

(3 行受影响)
*/

[摩羯伙夫]

[Quote=引用 5 楼 pengxuan 的回复:]
SQL code


if object_id('tb') is not null
drop table tb
go
create table tb
(
PURCHASEAPPLYNO varchar(10),
PURCHASENO varchar(10),
PURCHASETYPE varchar(10)
)
go
insert into tb
selec……
[/Quote]

为什么外采排1，内才排2，这里面规律什么？