日期:2014-05-17  浏览次数:20646 次

这两个查询如何写?
----建立测试数据
----环境:MSSQL2000

if exists (select * from sysobjects where id = object_id(N'Table1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
  drop table Table1
end

GO

CREATE TABLE [Table1] (
[fdSn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO



Insert Table1 (fdSn) Values ( 'A001')
Insert Table1 (fdSn) Values ( 'A003')
Insert Table1 (fdSn) Values ( 'A002')
Insert Table1 (fdSn) Values ( 'A099')
Insert Table1 (fdSn) Values ( 'A100')
Insert Table1 (fdSn) Values ( 'A055')
Insert Table1 (fdSn) Values ( 'A059')
Insert Table1 (fdSn) Values ( 'A058')
Insert Table1 (fdSn) Values ( 'A012')


select * from Table1


/*
已知一个表Table1如下:
fdSn
A001
A003
A002
A099
A100
A055
A059
A058
A012


A为前缀,其余3位是流水号
写一个查询或存储过程,把有连接+1的用最小值和最大值标出范围,其它保持不变

即:

查询一:
fdSn
A001-A003
A099-A100
A055
A058-A059
A012


查询二:(把没有连续的单个数最大值用相同的数表示最大值)
fdSn
A001-A003
A099-A100
A055-A055
A058-A059
A012-A012




这个两个查询怎么写呀?

*/


------解决方案--------------------
http://blog.csdn.net/wufeng4552/archive/2009/12/04/4938218.aspx

这个吗?
------解决方案--------------------
SQL code
--1
;WITH t AS
(
SELECT fdSn,fd=left(fdSn,1),sn=CAST(STUFF(fdSn,1,1,'')AS INT)from Table1
)
select fdSn=fdSn+(SELECT ISNULL('-'+MIN(fdSn),'') FROM t AS b
        WHERE fd=a.fd AND sn>a.sn
        AND NOT EXISTS (SELECT 1 FROM t WHERE  fd=b.fd AND sn=b.sn+1)
        AND EXISTS (SELECT 1 FROM t WHERE  fd=b.fd AND sn=a.sn+1)
            )
        from t AS a
 WHERE  NOT EXISTS ( SELECT 1 FROM   t WHERE fd=a.fd AND sn=a.sn-1)

/*
fdSn
-----------------------------------------------------------------
A001-A003
A099-A100
A055
A058-A059
A012

(5 行受影响)


*/
;WITH t AS
(
SELECT fdSn,fd=left(fdSn,1),sn=CAST(STUFF(fdSn,1,1,'')AS INT)from Table1
)
select fdSn=fdSn+'-'+ISNULL((SELECT MIN(fdSn) FROM t AS b
        WHERE fd=a.fd AND sn>a.sn
        AND NOT EXISTS (SELECT 1 FROM t WHERE  fd=b.fd AND sn=b.sn+1)
        AND EXISTS (SELECT 1 FROM t WHERE  fd=b.fd AND sn=a.sn+1)
            ),fdSn)
        from t AS a
 WHERE  NOT EXISTS ( SELECT 1 FROM   t WHERE fd=a.fd AND sn=a.sn-1)
/*
fdSn
-----------------------------------------------------------------
A001-A003
A099-A100
A055-A055
A058-A059
A012-A012

(5 行受影响)

*/