如果从另一个关联内多个数值内取第一条?如:表T1 通过和CODE关联T2
code name dec
1002 内存1 测试1
如:表T2 code
1002 备注1001
1002 备注1002v88
1003 备注1002v55
1002 备注1002v1
1006 备注1006我要得到如下显示1,取第一条:
code name dec
1002 内存1 测试1 备注1001
更进一步的结果如下显示,要把相关信息横向并在一起:
code name dec
1002 内存1 测试1 备注10011002v881002v551002v11006
code name dec
1002 内存1 测试1
如:表T2 code
1002 备注1001
1002 备注1002v88
1003 备注1002v55
1002 备注1002v1
1006 备注1006我要得到如下显示1,取第一条:
code name dec
1002 内存1 测试1 备注1001
更进一步的结果如下显示,要把相关信息横向并在一起:
code name dec
1002 内存1 测试1 备注10011002v881002v551002v11006
A.*
,= STUFF(B..value('/R[1]','nvarchar(MAX)'),1,1,'')
FROM T1 a
OUTER APPLY (
SELECT
QQs = (
SELECT N';' +
FROM T2
WHERE CODE = A.CODE
ORDER BY
FOR XML PATH(''), ROOT('R'), TYPE
)
) AS B
create table t1
(code varchar(4), name nvarchar(10), dec nvarchar(10))
create table t2
(code varchar(4), nvarchar(100))insert into t1
select '1002', '内存1', '测试1'insert into t2
select '1002', '备注1001' union all
select '1002', '备注1002v88' union all
select '1002', '备注1002v55' union all
select '1002', '备注1002v1' union all
select '1002', '备注1006'
create function f_hb(@code varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+[] from t2 where charindex(','+cast(code as varchar)+',',','+@code+',')>0
return @str
end
go select code,name,dec,'备注' + replace(dbo.f_hb(code),'备注','') from t1
insert into #tb
select '1002','内存1','测试1'create table #ta(code varchar(20), varchar(20))
insert into #ta
select '1002','备注1001'
union all
select '1002','备注1002v88'
union all
select '1003','备注1002v55'
union all
select '1002','备注1002v1'
union all
select '1006','备注1006'select top 1 a.*,='备注'+REPLACE((select ','+ from #ta for xml path('')),',备注','') from #tb a left join #ta b on a.code=b.code
--*******************************************************************************************
表结构,数据如下:
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. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
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
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb
if object_id('tb1') is not null
drop table tb1
go
create table tb1
(
code varchar(10),
name varchar(10),
dec varchar(10)
)
go
insert into tb1
select '1002','内存1','测试1'
go
if object_id('tb2') is not null
drop table tb2
go
create table tb2
(
code varchar(10),
varchar(20)
)
go
insert into tb2
select '1002','备注1001' union all
select '1002','备注1002v88' union all
select '1002','备注1002v55' union all
select '1002','备注1002v1' union all
select '1002','备注1006'
go
--1.
select top 1 tb1.*,tb2. from tb1 inner join tb2 on tb1.code=tb2.code
--2.
select code,name,dec,'备注'+replace((select ''+ from tb2 where code=tb1.code for xml path('')),'备注','') from tb1
/*
code name dec
---------- ---------- ---------- --------------------
1002 内存1 测试1 备注1001(1 行受影响)code name dec
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1002 内存1 测试1 备注10011002v881002v551002v11006(1 行受影响)
*/
正确如下create table T1(
code varchar(50),
[name] varchar(50),
dec varchar(50))create table T2
(
code varchar(50),
varchar(50))insert into T1 select
'1002',' 内存1',' 测试1' insert into T2 values
('1002' ,'备注1001' )
insert into T2 values('1002' ,'备注1002v88' )
insert into T2 values('1003 ','备注1002v55' )
insert into T2 values('1002' ,'备注1002v1 ' )
insert into T2 values('1006' ,'备注1006' )
select T1.code,T1.name,T1.dec,REPLACE((select ','+ from T1 for xml path('')),',备注','') from T1 left join T2 ON T1.code=T2.code where T2. ='备注1001'
declare @strs varchar(100)
set @strs='';
select @strs=@strs+ from T2
select T1.code,T1.name,T1.dec, replace (@strs,'备注','') from T1
code name dec
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1002 内存1 测试1 1001(1 行受影响)code name dec
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1002 内存1 测试1 10011002v881002v551002v1 1006(1 行受影响)
insert into t1 select 1002 ,'内存1','测试1'
create table T2(code int, nvarchar(20))
insert into t2 select 1002,'备注1001'
insert into t2 select 1002,'备注1002v88'
insert into t2 select 1003,'备注1002v55'
insert into t2 select 1002,'备注1002v1'
insert into t2 select 1006,'备注1006'
go
--关联第一条,对现有数据:
select top 1 a.*,b. from t1 a inner join t2 b on a.code=b.code
/*
code name dec
----------- ---------- ---------- --------------------
1002 内存1 测试1 备注1001(1 行受影响)
*/
--如果t1 有多条记录,要以code关联取第一条,则:
select a.*,b. from t1 a inner join t2 b on a.code=b.code
where not exists(select 1 from t2 where code=b.code and <b.)
/*
code name dec
----------- ---------- ---------- --------------------
1002 内存1 测试1 备注1001(1 行受影响)
*/
--进一步:
--楼主的结果并非关联,对现有的数据,如果关联,则:
select code,name,dec,stuff((select ','+right(,len()-2) from t2 where code=a.code for xml path('')),1,1,'') from t1 a group by code,name,dec
/*
code name dec
----------- ---------- ---------- --------------------------------------------------------------------------------
1002 内存1 测试1 1001,1002v88,1002v1(1 行受影响)v55,1006 code 与现有数据不关联
*/
go
drop table t1,t2
create table aa (code varchar(10),name varchar(10),dec varchar(10))
create table bb (code varchar(10), varchar(100))
go
insert into aa select '1002','内存1','测试1'
insert into bb select '1002','备注1001' union all
select '1002','备注1002v88' union all
select '1003','备注1002v55' union all
select '1002','备注1002v1' union all
select '1006','备注1006'
go
select * from aa
select * from bbgo
select code,name,dec,
from (
select aa.*,b.,b.rw from aa left join (select *,ROW_NUMBER() over (order by getdate()) as rw from bb)as b on aa.code=b.code
) as a
where a.rw in (select MIN(rw) from (
select aa.*,b.,b.rw from aa left join (select *,ROW_NUMBER() over (order by getdate()) as rw from bb)as b on aa.code=b.code
) as b group by b.code)go
select aa.code,aa.name,aa.dec, '备注'+REPLACE((select ''+ from bb where aa.code=bb.code for xml path('')),'备注','') from aa