一个表名字叫A,字段是:AID(int 自动ID),AName(varchar(10))
这个表有两条数据 :
一、1 |A
二、2|B另一个表名字:B,字段:BID(int 自动ID),AID(varchar(10)),BName(varchar(10))
表B中AID是存储A表上的AID,用(,)分割出来,当表B中的AID值是:1,2现在视图就把两个表合在一起显示: BID,AName 这两个字段。我要的效果就是AName 就是:A,B 但不知道这样的SQL视图怎么写?
这个表有两条数据 :
一、1 |A
二、2|B另一个表名字:B,字段:BID(int 自动ID),AID(varchar(10)),BName(varchar(10))
表B中AID是存储A表上的AID,用(,)分割出来,当表B中的AID值是:1,2现在视图就把两个表合在一起显示: BID,AName 这两个字段。我要的效果就是AName 就是:A,B 但不知道这样的SQL视图怎么写?
if object_id('[a]') is not null drop table [a]
create table [a] (aid int,aname varchar(1))
insert into [a]
select 1,'a' union all
select 2,'b'
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (bid int,aid varchar(10),bname varchar(10))
insert into [b]
select 1,'1,2',null
gocreate function get_name(@aid varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+aname from a where charindex(','+ltrim(aid)+',',','+@aid+',')>0
return @sql
end
go
select bid,bname=dbo.get_name(aid) from [b]--结果:
bid bname
----------- ------------
1 a,b
1. 创建表,添加测试数据
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb 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 tb
///
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 row(s) affected)
/
2 在SQL2000只能用自定义函数实现
2.1 创建合并函数fn_strSum,根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@values, 1, 1, '')
END
GO
调用函数
SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum
2.2 创建合并函数fn_strSum2,根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum2(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
RETURN @values
END
GO
调用函数
SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum2
3 在SQL2005中的新解法
3.1 使用OUTER APPLY
SELECT
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '', ''), 1, 1, '')
)N