sql疑难杂症

[薇薇]

--测试环境sql2000   
表A   
id                       值   
1                               a   
2                               b   
3                               c   
4                               d   
5                               e   
表B           
id               value   
1                               2   
2                               3   

想要得到的结果   
value               值   
2                                               a,b   
3                                               c,d,e   
[/code……   
[/Quote][code=SQL]   
我补充几句哈，   
value就的值就是确定我在表A要取几行值，但是如果前面已经取过，那么就过滤掉。 
例如我value=2的时候，这时value为表B的第一条数据，那么我就去表B前2条数据。 
value=3的时候，这时我就要在表A取三条数据，此时value=3前面存在一条数据，就是value=2,所以我就必须过滤掉前面2条数据，重第三条开始取。结果就是我想要的结果啊
-----
提示：主键都是自增的。另外不存在其他字段。

[liangCK]

分得太开了。看得痛苦。

[开着拖拉机泡妞]

表a
name num
aa 1
bb 2
cc 4
dd 4
ee 2
一条语句实现如下:
name
aa,bb,cc,dd,ee
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T
SELECT @STR
问题描述：
无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数，
所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
1 aa,bb
2 aaa,bbb,ccc
即，group by id, 求value 的和（字符串相加）
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *FROM (SELECT DISTINCT Id FROM @t)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
( SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
GO
--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
GO
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
SELECT col1,col2=CAST(col2 as varchar(100))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1 col2
a 1
a 1,2
b 1
b 1,2
b 1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO
--3.3.4.1 每组<=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=1 THEN ''
ELSE ','+CAST(MAX(col2) as varchar)
END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ----------
a 1,2
b 1,2
c 3
--*/
--3.3.4.2 每组<=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
ELSE ''
END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ------------
a 1,2
b 1,2,3
c 3
--*/
GO
if not object_id('A') is null
drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> -->

if not object_id('B') is null
drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from B

id cname
1 张三,李四,王五
2 王五,蔡六

(2 個資料列受到影響)
我现在有一字段值为:a,b,c
分隔符为逗号.
我现在想实现目地值为:
字段:
id name
1 a
2 b
3 c
在SQL中咋实现呢?
declare @str varchar(8000)
set @str = 'a1,b1,c2,d1,e3,f5'
set @str = 'select name='''+replace(@str,',',''''+' union all select ''')+''''
set @str='select id=identity(int,1,1),name into #temp from ('+@str+') a select * from #temp drop table #temp'
exec(@str)
--参考:
CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))
INSERT TB
SELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL
SELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'
GO
CREATE FUNCTION F_getStr(@color nvarchar(30))
returns nvarchar(30)
as
begin
declare @str nvarchar(30),@temp nvarchar(30)
set @str=''
set @temp=''
while charindex(',', @color+',')>0
begin
set @temp=left(@color, charindex(',', @color+',')-1)
if charindex(','+@temp+',', ','+@str+',')=0
set @str=@str+','+@temp
set @color=stuff(@color, 1, charindex(',', @color+','), '')
end
return stuff(@str, 1, 1, '')
end
go
SELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TB
DROP TABLE TB
DROP FUNCTION F_getStr
/*
ID COLOR
173160 #特深蓝色
173160 #特深蓝色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
*/

[薇薇]

--测试环境sql2000  
create table a
(
	id int identity(1,1),
    textName varchar(30)
)
go
insert into a
select a union all
select b union all
select c union all
select d union all
select e union all
select f 
go
create table b
(
   id int identity(1,1),
   valueName int
)
go
insert into b
select 2 union all
select 3
    
--想要得到的结果       
value           值       
2              a,b       
3              c,d,e       

[薇薇]

[Quote=引用 3 楼 qqzhengwei 的回复:]
SQL code

--测试环境sql2000
create table a
(
id int identity(1,1),
textName varchar(30)
)
go
insert into a
select a union all
select b union all
select c union all
select d union al……
[/Quote]上面我这个描述种清楚了吧。嘿嘿

[EnForGrass]

看做蛋疼

[arrow_gx]

用以下语句查询看看，具体分析一下，只要改变一下 id in 里面的数字，就可以达到到你的要求，具体如何改 id in ,子机弄吧，应该比较简单的


select stuff((select ','+CAST([value] as varchar(10))
from @表b where id in (1,2,3) for xml path('')),1,1,'')

[百年树人]

if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[值] varchar(1))
insert [A]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e'
go
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[value] int)
insert [B]
select 1,2 union all
select 2,3
go

create view tb_b
as
select t.*,tid=isnull((select top 1 value from b where value<t.value order by value desc),0)+a.number 
from b t
join master..spt_values a on a.type='P' and a.number between 1 and t.value
go

create function dbo.f_str(@value int)
returns varchar(8000)
as
begin
  declare @r varchar(8000)
  select @r=isnull(@r+',','')+a.值
  from (select *,tid=(select count(1) from a where id<=t.id) from a t) a
  join tb_b b on a.tid=b.tid
  where b.value=@value
  return @r
end
go

select value,dbo.f_str(value) as 值 from b group by value
/**
value       值
----------- -----------------
2           a,b
3           c,d,e

(2 行受影响)
**/

[薇薇]

请问：josy
可以不用视图来做吗？

[百年树人]

[Quote=引用 8 楼 qqzhengwei 的回复:]
SQL code
请问：josy
可以不用视图来做吗？
[/Quote]
可以不用视图，但sql2000要用函数

create function dbo.f_str(@value int)
returns varchar(8000)
as
begin
  declare @r varchar(8000)
  select @r=isnull(@r+',','')+a.值
  from (select *,tid=(select count(1) from a where id<=t.id) from a t) a
  join 
   (select t.*,tid=isnull((select top 1 value from b where value<t.value order by value desc),0)+a.number 
    from b t
    join master..spt_values a on a.type='P' and a.number between 1 and t.value
    ) b 
  on a.tid=b.tid
  where b.value=@value
  return @r
end
go

select value,dbo.f_str(value) as 值 from b group by value
/**
value       值
----------- -----------------
2           a,b
3           c,d,e

(2 行受影响)
**/

[寡亾]

[Quote=引用 9 楼 josy 的回复:]

引用 8 楼 qqzhengwei 的回复:
SQL code
请问：josy
可以不用视图来做吗？

可以不用视图，但sql2000要用函数
SQL code
create function dbo.f_str(@value int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=isn……
[/Quote]这种写法是不是忽略了B表中value的和大于A表中的条数，楼主B表中的数据会出现这情况吗？