日期:2014-05-17 浏览次数:20481 次
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-23 12:46:24
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[FNname] varchar(10))
insert [huang]
select 1,'BookID' union all
select 2,'BookName' union all
select 3,'Auhor' union all
select 4,'BookID' union all
select 5,'BookName' union all
select 6,'Auhor' union all
select 7,'BookID' union all
select 8,'BookName' union all
select 9,'Auhor' union all
select 10,'BookID' union all
select 11,'BookName' union all
select 12,'Auhor' union all
select 13,'RelationID' union ALL
select 14,'BookID' union all
select 15,'BookName' union all
select 16,'Auhor' UNION ALL
SELECT 17,'RelationID'
--------------开始查询
;WITH cte AS (
select * ,ROW_NUMBER()OVER(ORDER BY id) idt
FROM huang a
WHERE [FNname]='BookID'),cte2 AS (
SELECT a.id,a.fnname,a.idt,CASE WHEN b.id-1 IS NULL THEN (SELECT MAX(id) FROM huang ) ELSE b.id-1 END AS endid
FROM cte a left JOIN cte b ON a.idt=b.idt-1)
SELECT *
FROM huang
WHERE EXISTS (SELECT 1 FROM (
SELECT MIN