表自链接有关问题，多谢！

表自链接问题，急，在线等，谢谢！！！！！
有一个表，结构如下

SQL code

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

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

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

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

在线等,谢谢！


[解决办法]
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
)
[解决办法]

SQL code

if object_id('tb') is not null   drop table tbgocreate table tb( PURCHASEAPPLYNO varchar(10), PURCHASENO varchar(10), PURCHASETYPE varchar(10))goinsert into tbselect 'PA001','P001','外采' union allselect 'PA001','P002','内采' union allselect 'PA002','P003','外采' union allselect 'PA003','P004','内采'goselect PURCHASEAPPLYNO,PURCHASENO,PURCHASETYPE from ( select *,row=row_number() over(partition by PURCHASEAPPLYNO order by getdate()) from tb)t where row=1go/*PURCHASEAPPLYNO PURCHASENO PURCHASETYPE--------------- ---------- ------------PA001           P001       外采PA002           P003       外采PA003           P004       内采(3 行受影响)*/
[解决办法]
SQL code
------------------------------ 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 allselect 'PA001','P002','内采' union allselect 'PA002','P003','外采' union allselect '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 行受影响)*/ 

 

查看更多 下一篇