一个痛苦的问题，请帮忙

2006-09-16 09:24:00 259 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

[A-21]ARMv8/v9-SMMU系统架构和功能概述本文简述了SMMU的系统架构和核心功能阅读详情

表1
DepId DepName
01 service
02 acount

表2
StateId DepId message
1 01 getphone
2 01 analys
3 02 acount monny
4 02 bank

表3
StateId DepName message
01 service getphone,analys
02 acount acount monny,bank

表1和表2是基本表，本人想把基本表中的信息insert到表3，请各表帮忙写一条SQL,因为
表1中的值不是固定的。

...全文

机械设计师必看：如何用Autodesk Inventor高效完成钣金设计（附实战案例）本文为机械设计师详细解析了如何利用Autodesk Inventor Professional高效完成钣金设计。文章通过一个钣金防护罩的实战案例，系统介绍了从核心设计逻辑、建模流程、参数化技巧到工程图输出的完整工作流，旨在帮助设计师掌握智能化的设计方法，显著提升从概念到制造的设计效率与准确性。阅读详情

y2jwww

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

xiaoxiangqing 2006-10-07

打赏
举报

--drop table tbTest1,tbTest2,tbTest3
if exists(select * from sysobjects where id=object_id('tb1') and type='u')
drop table tb1
if exists(select * from sysobjects where id=object_id('tb3') and type='u')
drop table tb2
if exists(select * from sysobjects where id=object_id('tb3') and type='u')
drop table tb3
go

create table tb1(DepId varchar(10),DepName varchar(10))
create table tb2(StateId int not null ,DepId varchar(10) not null,message varchar(50),link_message varchar(500) null,primary key clustered(DepId,StateId))
create table tb3(DepId varchar(10) not null,Depname varchar(10),message varchar(50))
go

insert tb1
select '01', 'service' union all
select '02', 'acount'
insert tb2(StateId,DepId,message)
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
go

declare @DepId varchar(10),@link_mess varchar(500)
set @DepId=''
update tb2 set @link_mess=case when @DepId<>DepId then message else @link_mess+','+message end,@DepId=DepId,link_message=@link_mess

insert tb3
select a.*,max(link_message) link_message from tb1 a join tb2 b on a.DepId=b.DepId group by a.DepId,a.DepName

select * from tb3
/*
StateId DepName message
01 service getphone,analys
02 acount acount monny,bank
*/

老本 2006-09-30

打赏
举报

用临时表和游标实现好了！

wgsasd311 2006-09-30

打赏
举报

--稍改如下
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('tbTest3') is not null
drop table tbTest3
if object_id('fnJoin') is not null
drop function fnJoin
GO
----创建测试函数,参数为表2的DepId
create function fnJoin(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + message from tbTest2 where DepId = @id
return stuff(@str,1,1,'')
end
GO
----创建测试表
create table tbTest1(DepId varchar(10),DepName varchar(10))
create table tbTest2(StateId int,DepId varchar(10),message varchar(50))
create table tbTest3(Id int identity,Depname varchar(10),message varchar(50))
insert tbTest1
select '01', 'service' union all
select '02', 'acount'
insert tbTest2
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
----查询.如果需要生成表3,请使用into
insert into tbtest3
SELECT b.DepName,dbo.fnJoin(b.DepId)
FROM tbTest2 a
left join tbTest1 b on a.DepId = b.DepId
GROUP BY b.DepId,b.DepName
ORDER BY b.DepId

select * from tbtest3
----清除测试环境
drop table tbTest1,tbTest2,tbtest3
drop function fnJoin

y2jwww 2006-09-16

打赏
举报

上面写错了一点，应该是
表1
DepId DepName
01 service
02 acount

表2
StateId DepId message
1 01 getphone
2 01 analys
3 02 acount monny
4 02 bank

表3
Id DepName message
1 service getphone,analys
2 acount acount monny,bank

表1和表2是基本表，本人想把基本表中的信息insert到表3，请各表帮忙写一条SQL,因为
表1中的值不是固定的。
hellowork(一两清风)
的答案可能还有点问题。可能是我没有把问题说清楚

hellowork 2006-09-16

打赏
举报

if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('fnJoin') is not null
drop function fnJoin
GO
----创建测试函数,参数为表2的DepId
create function fnJoin(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + message from tbTest2 where DepId = @id
return stuff(@str,1,1,'')
end
GO
----创建测试表
create table tbTest1(DepId varchar(10),DepName varchar(10))
create table tbTest2(StateId int,DepId varchar(10),message varchar(50))
insert tbTest1
select '01', 'service' union all
select '02', 'acount'
insert tbTest2
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
----查询.如果需要生成表3,请使用into
SELECT b.DepId as StateId,b.DepName,max(dbo.fnJoin(a.DepId)) as message --into 表3 /*保存到表3中*/
FROM tbTest2 a
left join tbTest1 b on a.DepId = b.DepId
GROUP BY b.DepId,b.DepName
ORDER BY b.DepId

----清除测试环境
drop table tbTest1,tbTest2
drop function fnJoin

/*结果
StateId DepName message
-------------------------------------------------------
01 service getphone,analys
02 acount acount monny,bank
*/

一个痛苦的问题，请帮忙

相关推荐