求一SQL 请高手指教
表1
ID NAME CALC
1 原価1 1+3
2 原価2 1+2+3
3 原価3 1+2+3+5 表2
ID NAME
1 (TANKA * 5/100)
2 (TANKA * 2/100)
3 (TANKA * 7/100)
5 (TANKA * 9/100)结果
ID NAME CALC CALC_NAME
1 原価1 1+3 (TANKA * 5/100)+(TANKA * 7/100)
2 原価2 1+2+3 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)
3 原価3 1+2+3+5 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)+(TANKA * 9/100)
表1
ID NAME CALC
1 原価1 1+3
2 原価2 1+2+3
3 原価3 1+2+3+5 表2
ID NAME
1 (TANKA * 5/100)
2 (TANKA * 2/100)
3 (TANKA * 7/100)
5 (TANKA * 9/100)结果
ID NAME CALC CALC_NAME
1 原価1 1+3 (TANKA * 5/100)+(TANKA * 7/100)
2 原価2 1+2+3 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)
3 原価3 1+2+3+5 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)+(TANKA * 9/100)
解决方案 »
- 复杂的数据处理
- Float(n)中的n到底有什么作用?
- 执行SQL存储过程查询 得不到想要的结果
- 在全文检索里用order by子句排序后,前面不能加distinct,有解决方法吗?!谢谢
- 为什么用服务器名不能注册,用IP却可以注册?
- 关于SQLServer2005中Sum时的一个问题
- 怎样将表格中的某一列提取出来并且数据项不重复?
- 高手请进==>我的SQL SERVER2000只要有几台工作站同时访问就会有经常连接不上的问题,但一台连接是肯定没问题的,请问何原因?不断加分...
- 听说MS出了SQL SERVER2000是中文版,不知道功能如何?它比7.0的功能多什么?我正想学习SQL 不知道应该学那一个版本?
- 行转列
- SQL变量类型查询
- access百分比怎么导入ms sql里
wang as (select tb1.* ,tb2.name from tb1 left join tb2 on charindex(tb2.id,tb1.calc)>))然后再字符串合并if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go合并表:SQL2000用函数:go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab
--分拆是这个。
分拆列值 有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 1. 旧的解决方法(sql server 2000)
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number)
FROM tb a, master..spt_values b
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ','
2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT a.id, b.value
FROM(
SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb
)a
OUTER aPPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v)
)b DROP TabLE tb /*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc (5 行受影响)
*/
--合并用这个;
--******************************************************************************************
-- 合并列值
--*******************************************************************************************
表结构,数据如下:
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
INSERT TA
SELECT 1, N'原価1', '1+3' UNION ALL
SELECT 2, N'原価2', '1+2+3' UNION ALL
SELECT 3, N'原価3', '1+2+3+5'
CREATE TABLE TB([ID] INT, [NAME] VARCHAR(15))
INSERT TB
SELECT 1, '(TANKA * 5/100)' UNION ALL
SELECT 2, '(TANKA * 2/100)' UNION ALL
SELECT 3, '(TANKA * 7/100)' UNION ALL
SELECT 5, '(TANKA * 9/100)'
GOCREATE FUNCTION F_COMBINESTR(@CALC VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @STR VARCHAR(100)
SET @STR=''
SELECT @STR=@STR+'+'+[NAME] FROM TB WHERE CHARINDEX('+'+RTRIM(ID)+'+', '+'+@CALC+'+')>0
RETURN STUFF(@STR, 1, 1, '')
END
GO
SELECT *,CALC=dbo.F_COMBINESTR(CALC) FROM TADROP TABLE TA,TB
DROP FUNCTION F_COMBINESTR
/*
ID NAME CALC CALC
----------- ---- ---------- ---------------------------------------------------------------------
1 原価1 1+3 (TANKA * 5/100)+(TANKA * 7/100)
2 原価2 1+2+3 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)
3 原価3 1+2+3+5 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)+(TANKA * 9/100)
*/
刚才没说清楚
表1中的CALC 可以是任意的四则表达式 例如 1+2-3*4
表1
ID NAME CALC
1 原価1 1-3*2
2 原価2 (1+2)/3
3 原価3 1+2+3+5 表2
ID NAME
1 (TANKA * 5/100)
2 (TANKA * 2/100)
3 (TANKA * 7/100)
5 (TANKA * 9/100) 结果
ID NAME CALC CALC_NAME
1 原価1 1+3 (TANKA * 5/100)-(TANKA * 7/100)* (TANKA * 2/100)
2 原価2 1+2+3 ((TANKA * 5/100)+(TANKA * 2/100))/(TANKA * 7/100)
3 原価3 1+2+3+5 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)+(TANKA * 9/100)
刚才没说清楚
表1中的CALC 可以是任意的四则表达式 例如 1+2-3*4
表1
ID NAME CALC
1 原価1 1-3*2
2 原価2 (1+2)/3
3 原価3 1+2+3+5
表2
ID NAME
1 (TANKA * 5/100)
2 (TANKA * 2/100)
3 (TANKA * 7/100)
5 (TANKA * 9/100) 结果
ID NAME CALC CALC_NAME
1 原価1 1-3*2 (TANKA * 5/100)-(TANKA * 7/100)* (TANKA * 2/100)
2 原価2 (1+2)/3 ((TANKA * 5/100)+(TANKA * 2/100))/(TANKA * 7/100)
3 原価3 1+2+3+5 (TANKA * 5/100)+(TANKA * 2/100)+(TANKA * 7/100)+(TANKA * 9/100)
if OBJECT_ID ('tb1') is not null
drop table tb1
if OBJECT_ID ('tb2') is not null
drop table tb2
if OBJECT_ID('c_f')is not null
drop function c_f
go
create table tb1 (id int,name varchar(10),calc varchar(10))
insert into tb1 select 1 ,'原1','1+3'
union all select 2,'原2','1+2+3'
union all select 3,'原3','1+2+3+5'
create table tb2 (id int,name varchar(10))
insert into tb2 select 1,'(TANKA*5/100)'
union all select 2,'(TANKA*2/100)'
union all select 3,'(TANKA*7/100)'
union all select 5,'(TANKA*9/100)'go
create function c_f (@id varchar(10))
returns nvarchar(4000)
as
begin
declare @str nvarchar(4000)
set @str=''
select @str=@str+''+ b.name from tb1 a join tb2 b on
charindex(CAST(b.id as varchar(10)),a.calc)>0
where a.id=@id
return stuff(@str,1,1,'')
end
go
select a.*,dbo.c_f(a.id) as calc_name from tb1 aid name calc calc_name
----------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 原1 1+3 TANKA*5/1(TANKA*7/1
2 原2 1+2+3 TANKA*5/1(TANKA*2/1(TANKA*7/1
3 原3 1+2+3+5 TANKA*5/1(TANKA*2/1(TANKA*7/1(TANKA*9/1(3 行受影响)
go
create table [ta]([ID] int,[NAME] varchar(5),[CALC] varchar(7))
insert [ta]
select 1,'原価1','1-3*2' union all
select 2,'原価2','(1+2)/3' union all
select 3,'原価3','1+2+3+5'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NAME] varchar(13))
insert [tb]
select 1,'(TANKA*5/100)' union all
select 2,'(TANKA*2/100)' union all
select 3,'(TANKA*7/100)' union all
select 5,'(TANKA*9/100)'
go
--select * from [ta]
--select * from [tb]
--创建函数r:
if object_id('r','fn') is not null
drop function r
go
create function r(@s nvarchar(1000))
returns nvarchar(1000)
as
begin
declare @r nvarchar(1000),@i int,@si int
set @r='' while @s<>''
begin
set @i=patindex('%[0-9]%',@s)
if @i=0 return case when @r='' then @s else @r end select @r=@r+left(@s,@i-1),@s=stuff(@s,1,@i-1,'') set @si=patindex('%[^0-9]%',@s)
if @si=0
begin
select @r=@r+NAME from tb where ID=@s
return @r
end
else
begin
select @r=@r+NAME from tb where ID=substring(@s,1,@si-1)
set @s=stuff(@s,1,@si-1,'')
end
end
return @r
end
go
--测试结果:
select *,dbo.r(calc) CALC_NAME from ta
/*
ID NAME CALC CALC_NAME
----------- ----- ------- ---------------------------------------------------------------
1 原価1 1-3*2 (TANKA*5/100)-(TANKA*7/100)*(TANKA*2/100)
2 原価2 (1+2)/3 ((TANKA*5/100)+(TANKA*2/100))/(TANKA*7/100)
3 原価3 1+2+3+5 (TANKA*5/100)+(TANKA*2/100)+(TANKA*7/100)+(TANKA*9/100)(3 行受影响)
*/