现在数据库记录如下:
------------------------------------------------------------
ID sname
1 A,B,C,D,
2 B,C,D
3 E,F,A
4 B,A,C
5 A,B,D
100 ........
我想把这100条记录中ID小于6的记录弄出来。
然后把sname相加,并去掉重复。
最后的结果应该是:A,B,C,D,E,F
或者得到记录集
A
B
C
D
E
F
这样的一个记录集也行,
请问下有什么办法实现吗?
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-10 16:34:18
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[sname] varchar(8))
insert [huang]
select 1,'A,B,C,D,' union all
select 2,'B,C,D' union all
select 3,'E,F,A' union all
select 4,'B,A,C' union all
select 5,'A,B,D'
--------------开始查询--------------------------
SELECT DISTINCT
SUBSTRING([sname],number,CHARINDEX(',',[sname]+',',number)-number) as [sname]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([sname])
and type='p'
and substring(','+[sname],number,1)=','
AND id<6
----------------结果----------------------------
/*
*/