---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-17 10:58:16 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([Name] nvarchar(12),[from] nvarchar(6),[to] nvarchar(6)) insert [huang] select 'QWCE','NPI','ESI' union all select 'QWCE','ESI','MP' union all select 'QWCE','MP','NPI' union all select 'QWCE','NPI','ESI' union all select 'Test01','NPI','ESI' --------------生成数据-------------------------- ;WITH cte AS ( select * ,ROW_NUMBER()OVER(PARTITION BY name ORDER BY GETDATE())id from [huang]) SELECT name,MAX([from])[from],MIN([to])[to] FROM ( SELECT name,(SELECT [from] cte WHERE id=1 AND name=a.name)[from],(SELECT [to] cte WHERE id >=all(SELECT id FROM cte WHERE name=a.name))[to] FROM cte a)a GROUP BY name ----------------结果---------------------------- /* name from to ------------ ------ ------ QWCE NPI ESI Test01 NPI ESI */
根据创建时间排的,上面第一个搜索出来的就是group by name, created_on
上条回错了。。根据创建时间排的,上面第一个搜索出来的就是order by name, created_on
如果是group by name, created_on, 得到如下结果. 那么每一条记录的created_on值都是一样的,无法排序. Name from to QWCE NPI ESI QWCE ESI MP QWCE MP NPI QWCE NPI ESI Test01 NPI ESI
如果是group by name, created_on, 得到如下结果. 那么每一条记录的created_on值都是一样的,无法排序. Name from to QWCE NPI ESI QWCE ESI MP QWCE MP NPI QWCE NPI ESI Test01 NPI ESI创建时间实际肯定是不一样的,所以可以排序的我按照 DBA_Huangzj 的方法试了是可以的。。不过还是谢谢你啦 呵呵
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-17 10:58:16
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Name] nvarchar(12),[from] nvarchar(6),[to] nvarchar(6))
insert [huang]
select 'QWCE','NPI','ESI' union all
select 'QWCE','ESI','MP' union all
select 'QWCE','MP','NPI' union all
select 'QWCE','NPI','ESI' union all
select 'Test01','NPI','ESI'
--------------生成数据--------------------------
;WITH cte AS (
select * ,ROW_NUMBER()OVER(PARTITION BY name ORDER BY GETDATE())id
from [huang])
SELECT name,MAX([from])[from],MIN([to])[to]
FROM (
SELECT name,(SELECT [from] cte WHERE id=1 AND name=a.name)[from],(SELECT [to] cte WHERE id >=all(SELECT id FROM cte WHERE name=a.name))[to]
FROM cte a)a
GROUP BY name
----------------结果----------------------------
/*
name from to
------------ ------ ------
QWCE NPI ESI
Test01 NPI ESI
*/
根据创建时间排的,上面第一个搜索出来的就是group by name, created_on
上条回错了。。根据创建时间排的,上面第一个搜索出来的就是order by name, created_on
那么每一条记录的created_on值都是一样的,无法排序.
Name from to
QWCE NPI ESI
QWCE ESI MP
QWCE MP NPI
QWCE NPI ESI
Test01 NPI ESI
恩,按照你的方法可以,你好厉害哦~~~那么快就给出答案了哎 我得好好学学SQL 脑子总反应太慢了 ( ˇˍˇ )
那么每一条记录的created_on值都是一样的,无法排序.
Name from to
QWCE NPI ESI
QWCE ESI MP
QWCE MP NPI
QWCE NPI ESI
Test01 NPI ESI创建时间实际肯定是不一样的,所以可以排序的我按照 DBA_Huangzj 的方法试了是可以的。。不过还是谢谢你啦 呵呵