如何进行字段内的内容进行排序
有这样一张表，如图
，我想得到下表的结果，请问大侠如何写语句。需要注意的是合并值内根据值的大小也要排序。
[解决办法]
完善一下
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-11 14:28:07
-- 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] varchar(4),[name] varchar(4),[versions] varchar(1),[beginvalue] int,[endvalue] int)
insert [huang]
select 'ABCD','铭牌','B',10101,19999 union all
select 'ABCD','铭牌','B',10001,10002 union all
select 'ABCD','铭牌','A',10101,19999 union all
select 'ABCD','铭牌','A',10001,10002 union all
select 'ABCD','铭牌','A',10005,10005
--------------开始查询--------------------------
;WITH ym AS (
select TOP 100 PERCENT Id,name,[versions],CASE WHEN CAST([beginvalue] AS VARCHAR)=CAST([endvalue] AS VARCHAR) THEN CAST([beginvalue] AS VARCHAR) ELSE CAST([beginvalue] AS VARCHAR)+'-'+CAST([endvalue] AS VARCHAR) END [区间]
from [huang]
ORDER BY [versions] DESC,[beginvalue])
select a.id,a.name,a.versions,
stuff((select ','+[区间] from ym b
where b.id=a.id and b.name=a.name and b.[versions]=a.[versions]
ORDER BY [区间]
for xml path('')),1,1,'') '区间'
from ym a
group by a.id,a.name,a.versions
ORDER BY a.versions DESC
----------------结果----------------------------
/*
id name versions 区间
---- ---- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABCD 铭牌 B 10001-10002,10101-19999
ABCD 铭牌 A 10001-10002,10005,10101-19999
*/
if object_id('tb') is not null drop table tb
go
create table tb(
[id] varchar(4),名称 varchar(4),版本 varchar(1),
起始值 int,终止值 int
)
insert tb
select 'ABCD','铭牌','B',10101,19999 union all
select 'ABCD','铭牌','B',10001,10002 union all
select 'ABCD','铭牌','A',10101,19999 union all
select 'ABCD','铭牌','A',10001,10002 union all
select 'ABCD','铭牌','A',10005,10005
select distinct
id,
名称,
版本,
stuff(
(
select ','+case when 起始值 = 终止值
then cast(起始值 as varchar)
else cast(起始值 as varchar)+'-'+cast(终止值 as varchar)
end
from tb t2
where t1.id = t2.id and t1.名称 = t2.名称 and
t1.版本 = t2.版本
order by 起始值
for xml path('')
),1,1,''
) as 合并值
from tb t1
order by 版本 desc
/*
id 名称版本合并值
ABCD铭牌B10001-10002,10101-19999
ABCD铭牌A10001-10002,10005,10101-19999
*/