有点烦人的sql语句

2009-05-13 10:08:07 228 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Spring AI Alibaba Skill 实战：定义、注册与渐进式披露的企业级落地摘要：很多团队在接入大模型时，第一步做到的是“能对话”，第二步遇到的瓶颈却是“不能可靠执行”。Skill 的本质不是给模型多几个函数，而是把企业系统中的可控能力，以可治理、可审计、可扩展的方式交给 Agent。本文围绕 Spring AI Alibaba Skill 的定义、注册与渐进式披露，结合电商客服与售后场景，系统讲清楚其底层原理、架构设计、生产级实现、高并发治理与工程实践。@Component@Tool(description = "根据订单号查询订单状态、支付状态和物流状态")扫描包含。阅读详情

员工有多样权限，有多个负责解决的问题
tbluser
userid username roleid problemid
1 name1 1;2;3 1;2
2 name2 2 2;3
3 name3 2;3 1;2

tblrole
roleid rolename
1 role1
2 role2
3 role3

tblproblem
problemid problemname
1 problem1
2 problem2
3 problem3

需要查出 userid username rolename problename

谢谢

...全文

Coze视频混剪智能体工作流，1小时制作100条带货混剪视频，全平台通用摘要：本文介绍如何利用Coze智能体工作流一键生成全平台带货混剪视频。该工作流支持双视频素材查重处理、智能文案重塑、画面重组等核心功能，通过多节点协同实现批量生产。系统可自动下载视频、提取字幕、生成新脚本，并完成剪辑合成，支持飞书表格批量操作。该工作流已在iThinkAi扣子团队空间开放复制使用，帮助用户高效制作爆款带货视频。阅读详情

weasea

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

shenzhenNBA 2009-05-13

打赏
举报

create function getRolename(@roleid varchar(255))
returns varchar(4000)
as
begin
declare @rName varchar(255)
declare @rids varchar(255)
declare @cid int
declare @i int
set @rName=''
set @rids=@roleid
set @i=1
set @cid=0

while @i <len(@rids)
begin
select @cid=convert(int,substring(@rids,@i,1))
select @rName=@rName+[rolename]+"," from [tblrole] where [roleid]=@cid
select @i=@i+2
end
set @rName=left(@rName,len(@rName)-1)
return @rName
end

少了一个地方，修改

shenzhenNBA 2009-05-13

打赏
举报

create function getProblemame(@problemid varchar(255))
returns varchar(4000)
as
begin
declare @pName varchar(255)
declare @pids varchar(255)
declare @cpid int
declare @j int
set @pName=''
set @pids=@problemid
set @cid=0
set @j=1

while @j <len(@pids)
select @cpid=convert(int,substring(@pids,@j,1))
select @rName=@rName+[problemname]+"," from [tblproblem] where [problemid]=@cpid
select @j=@j+2
end
select @pName=left(@pName,len(@pName)-1)
return @pName
end

少了一个地方，修改

shenzhenNBA 2009-05-13

打赏
举报

我的也出来了，呵呵，慢点了，三表联动，都结萜了，还是发吧

select userid,username,dbo.getRolename(roleid),dbo.getProblemame(problemid)
from tbluser order by userid

-------------------------------------------------------------------
create function getRolename(@roleid varchar(255))
returns varchar(4000)
as
begin
declare @rName varchar(255)
declare @rids varchar(255)
declare @cid int
declare @i int
set @rName=''
set @rids=@roleid
set @i=1
set @cid=0

while @i<len(@rids)
begin
select @cid=convert(int,substring(@rids,@i,1))
select @rName=@rName+[rolename]+"," from [tblrole] where [roleid]=@cid
@i=@i+2
end
set @rName=left(@rName,len(@rName)-1)
return @rName
end

-------------------------------------------------------------------

create function getProblemame(@problemid varchar(255))
returns varchar(4000)
as
begin
declare @pName varchar(255)
declare @pids varchar(255)
declare @cpid int
declare @j int
set @pName=''
set @pids=@problemid
set @cid=0
set @j=1

while @j<len(@pids)
select @cpid=convert(int,substring(@pids,@j,1))
select @rName=@rName+[problemname]+"," from [tblproblem] where [problemid]=@cpid
end
select @pName=left(@pName,len(@pName)-1)
return @pName
end

tengjian1981 2009-05-13

打赏
举报

sqlserver 2005语法

tengjian1981 2009-05-13

打赏
举报







select *,

STUFF((

select ','+RoleName

from

(select a.UserID,b.RoleID,b.RoleName from tbluser a

left join tblrole b on Charindex(';'+cast(b.roleid as varchar(12))+';',';'+a.roleid+';')>0 

) c where c.UserID=d.UserID FOR XML PATH('')), 1, 1, '') as RoleNameList,

STUFF((

select ','+problemname

from

(select a.UserID,b.problemid,b.problemname from tbluser a

left join tblproblem b on Charindex(';'+cast(b.problemid as varchar(12))+';',';'+a.problemid+';')>0 

) c where c.UserID=d.UserID FOR XML PATH('')), 1, 1, '') as problemNameList 

from tbluser d







/* 结果









1	name1	1;2;3	1;2	role1,role2,role3	problem1,problem2

2	name2	2	2;3	role2	                problem2,problem3

3	name3	2;3	1;2	role2,role3	        problem1,problem2

rmini 2009-05-13

打赏
举报

三个表的联合查询

weasea 2009-05-13

打赏
举报

再次仰望。。。。。

mugua604 2009-05-13

打赏
举报

create table t1(id int,name varchar(10),classids varchar(20))

insert into t1 select 1,'张三','1,2,3' 

insert into t1 select 2,'李四','2,3' 

insert into t1 select 3,'王五','1,3' 



create table t2(classid int,classname varchar(20))

insert into t2 select 1,'数学' 

insert into t2 select 2,'语文' 

insert into t2 select 3,'英语' 

go





create function f_str(@str varchar(400))

returns varchar(100)

as

begin

   set @str=','+@str+','

   

   select @str=replace(@str,','+rtrim(classid)+',',','+classname+',') from t2

   

   set @str=substring(@str,2,len(@str)-2)

   

   return @str

end

go



select *,dbo.f_str(classids) as classnames  from t1

/*

id          name       classids             classnames                                                                                           

----------- ---------- -------------------- ---------------------------------------------------------------------------------------------------- 

1           张三         1,2,3                数学,语文,英语

2           李四         2,3                  语文,英语

3           王五         1,3                  数学,英语

*/

go



drop function f_str

drop table t1,t2

go

wxg22526451 2009-05-13

打赏
举报

--> Test Data: [tbluser]

if object_id('[tbluser]') is not null drop table [tbluser]

create table [tbluser] ([userid] int,[username] varchar(5),[roleid] varchar(50),[problemid] varchar(50))

insert into [tbluser]

select 1,'name1','1;2;3','1;2' union all

select 2,'name2','2','2;3' union all

select 3,'name3','2;3','1;2'

--> Test Data: [tblrole]

if object_id('[tblrole]') is not null drop table [tblrole]

create table [tblrole] ([roleid] int,[rolename] varchar(5))

insert into [tblrole]

select 1,'role1' union all

select 2,'role2' union all

select 3,'role3'

--> Test Data: [tblproblem]

if object_id('[tblproblem]') is not null drop table [tblproblem]

create table [tblproblem] ([problemid] int,[problemname] varchar(8))

insert into [tblproblem]

select 1,'problem1' union all

select 2,'problem2' union all

select 3,'problem3'



--select * from [tbluser]

--select * from [tblrole]

--select * from [tblproblem]

--Code

go

create function f_hb(@id varchar(10))

returns varchar(1000)

as

begin

  declare @str varchar(1000)

  set @str=''

  select @str=@str+';'+[problemname] from tblproblem where charindex(';'+cast(problemid as varchar)+';',';'+@id+';')>0

  return stuff(@str,1,1,'')

end

go

create function f_hb2(@id varchar(10))

returns varchar(1000)

as

begin

  declare @str varchar(1000)

  set @str=''

  select @str=@str+';'+[rolename] from tblrole where charindex(';'+cast(roleid as varchar)+';',';'+@id+';')>0

  return stuff(@str,1,1,'')

end

go  

select userid,username,rolename=dbo.f_hb2(roleid),problemname=dbo.f_hb(problemid) from tbluser

drop function f_hb,f_hb2



--Drop



--Result

/*



userid      username rolename                                                                                                                                                                                                                                                         problemname                                                                                                                                                                                                                                                      

----------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

1           name1    role1;role2;role3                                                                                                                                                                                                                                                problem1;problem2

2           name2    role2                                                                                                                                                                                                                                                            problem2;problem3

3           name3    role2;role3                                                                                                                                                                                                                                                      problem1;problem2

*/

liangCK 2009-05-13

打赏
举报

我写的是2005的.2000要写函数.

liangCK 2009-05-13

打赏
举报

---------------------------------

--  Author: liangCK 小梁

---------------------------------

 

--> 生成测试数据: @tbluser

DECLARE @tbluser TABLE (userid INT,username VARCHAR(5),roleid VARCHAR(5),problemid VARCHAR(3))

INSERT INTO @tbluser

SELECT 1,'name1','1;2;3','1;2' UNION ALL

SELECT 2,'name2','2','2;3' UNION ALL

SELECT 3,'name3','2;3','1;2'

 

--> 生成测试数据: @tblrole

DECLARE @tblrole TABLE (roleid INT,rolename VARCHAR(5))

INSERT INTO @tblrole

SELECT 1,'role1' UNION ALL

SELECT 2,'role2' UNION ALL

SELECT 3,'role3'

 

--> 生成测试数据: @tblproblem

DECLARE @tblproblem TABLE (problemid INT,problemname VARCHAR(8))

INSERT INTO @tblproblem

SELECT 1,'problem1' UNION ALL

SELECT 2,'problem2' UNION ALL

SELECT 3,'problem3'



--SQL查询如下:



SELECT 

    A.userid,

    A.username,

    STUFF(B.xmldoc.value('.','varchar(50)'),1,1,'') AS rolename,

    STUFF(C.xmldoc.value('.','varchar(50)'),1,1,'') AS problemname

FROM @tbluser AS A

    CROSS APPLY (

        SELECT xmldoc=

               (

                    SELECT 

                        ',' + rolename AS [text()]

                    FROM @tblrole 

                    WHERE ';'+A.roleid +';' LIKE '%;' + RTRIM(roleid) + ';%'

                    FOR XML PATH(''),TYPE

               )

    ) AS B

    CROSS APPLY (

         SELECT xmldoc=

               (

                    SELECT 

                        ',' + problemname AS [text()]

                    FROM @tblproblem

                    WHERE ';'+A.problemid +';' LIKE '%;' + RTRIM(problemid) + ';%'

                    FOR XML PATH(''),TYPE

               )

    ) AS C



/*

userid      username rolename                                           problemname

----------- -------- -------------------------------------------------- --------------------------------------------------

1           name1    role1,role2,role3                                  problem1,problem2

2           name2    role2                                              problem2,problem3

3           name3    role2,role3                                        problem1,problem2



(3 row(s) affected)

*/

--小F-- 2009-05-13

打赏
举报

仰视各位大侠

weasea 2009-05-13

打赏
举报

请楼上给出详细建议
谢谢

jinjazz 2009-05-13

打赏
举报

建议你改表结构，或者增加一张对照表

weasea 2009-05-13

打赏
举报

2000
是否2000和2005有句子区别啊

快乐_石头 2009-05-13

打赏
举报

分解字符串包含的信息值后然后合并到另外一表的信息

(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)



/*问题描述

tba

ID  classid   name

1     1,2,3   西服 

2     2,3     中山装

3     1,3     名裤

tbb 

id   classname

1     衣服

2     上衣

3     裤子



我得的结果是

id   classname            name

1     衣服,上衣,裤子      西服 

2     上衣，裤子          中山装

3     衣服，裤子          名裤

*/



-----------------------------------------------------

--sql server 2000中的写法

create table tba(ID int,classid varchar(20),name varchar(10))

insert into tba values(1,'1,2,3','西服')

insert into tba values(2,'2,3'  ,'中山装')

insert into tba values(3,'1,3'  ,'名裤')

create table tbb(ID varchar(10), classname varchar(10))

insert into tbb values('1','衣服')

insert into tbb values('2','上衣')

insert into tbb values('3','裤子')

go



--第1种方法，创建函数来显示

create function f_hb(@id varchar(10))

returns varchar(1000)

as

begin

  declare @str varchar(1000)

  set @str=''

  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0

  return stuff(@str,1,1,'')

end

go 

select id,classid=dbo.f_hb(classid),name from tba

drop function f_hb

/*

id          classid       name       

----------- ------------- ---------- 

1           衣服,上衣,裤子 西服

2           上衣,裤子      中山装

3           衣服,裤子      名裤

（所影响的行数为 3 行）

*/



--第2种方法.update

while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))

update tba

set classid= replace(classid,tbb.id,tbb.classname)

from tbb

where charindex(tbb.id,tba.classid)>0

select * from tba

/*

ID          classid              name       

----------- -------------------- ---------- 

1           衣服,上衣,裤子       西服

2           上衣,裤子            中山装

3           衣服,裤子            名裤

（所影响的行数为 3 行）

*/

drop table tba,tbb



------------------------------------------------------------------------

--sql server 2005中先分解tba中的classid,然后再合并classname

create table tba(ID int,classid varchar(20),name varchar(10))

insert into tba values(1,'1,2,3','西服')

insert into tba values(2,'2,3'  ,'中山装')

insert into tba values(3,'1,3'  ,'名裤')

create table tbb(ID varchar(10), classname varchar(10))

insert into tbb values('1','衣服')

insert into tbb values('2','上衣')

insert into tbb values('3','裤子')

go



SELECT id , classname , name FROM

(

  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 

  (

    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A

    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B

  ) tbc , tbb where tbc.classid = tbb.id

  ) T

)A 

OUTER APPLY

(

  SELECT [classname]= STUFF(REPLACE(REPLACE((

    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 

    (

      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A

      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B

    ) tbc , tbb where tbc.classid = tbb.id

  ) N

  WHERE id = A.id and name = A.name

  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')

)N

order by id



drop table tba,tbb



/*

id          classname      name

----------- -------------- ----------

1           衣服,上衣,裤子 西服

2           上衣,裤子      中山装

3           衣服,裤子      名裤

(3 行受影响)

*/

liangCK 2009-05-13

打赏
举报

2000?2005?

有点烦人的sql语句

相关推荐