今天别人问了个问题,被困住了 请大家帮忙解决
有两个表如下:
表A
ID value
1001 120000
1002 150000表B
ID MEMO
1001 CASE WHEN 0<VALUE<100000 THEN 0.1
CASE WHEN 100000<=VALUE<200000 THEN 0.2
CASE WHEN VALUE>=200000 THEN 0.3
END
1002需得到 120000*0.2
有两个表如下:
表A
ID value
1001 120000
1002 150000表B
ID MEMO
1001 CASE WHEN 0<VALUE<100000 THEN 0.1
CASE WHEN 100000<=VALUE<200000 THEN 0.2
CASE WHEN VALUE>=200000 THEN 0.3
END
1002需得到 120000*0.2
WHEN VALUE<200000 AND VALUE>=100000 THEN VALUE*0.2
WHEN VALUE>=200000 THEN VALUE*0.3 END FROM TA
GO
CREATE TABLE TA(ID INT, value INT)
INSERT TA SELECT 1001, 120000
INSERT TA SELECT 1002, 150000
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT, MENO DECIMAL(19,3))
INSERT TB SELECT 1001,NULL
INSERT TB SELECT 1002,NULL
UPDATE TB SET MENO= CASE WHEN VALUE>0 AND VALUE<100000 THEN VALUE*0.1
WHEN VALUE<200000 AND VALUE>=100000 THEN VALUE*0.2
WHEN VALUE>=200000 THEN VALUE*0.3 END
FROM TA WHERE TA.ID=TB.ID
SELECT * FROM TB
/*ID MENO
----------- ---------------------
1001 24000.000
1002 30000.000*/
CASE WHEN 0 <a.VALUE <100000 THEN 0.1
CASE WHEN 100000 <=a.VALUE <200000 THEN 0.2
CASE WHEN a.VALUE>=200000 THEN 0.3
END
FROM 表A A JOIN 表B ON A.ID=B.ID
(
ID char(6),
[value] int
)
insert into #A1 select '1001',120000
union all select '1002',150000select ID,sum(case when [value] between 0 and 99999 then [value]*0.1
else
case when [value] between 100000 and 200000 then [value]*0.2
else
[value]*0.3 end
end
) from #A1 group by ID
ID
------ ---------------------------------------
1001 24000.0
1002 30000.0(2 行受影响)
嵌套
就行了
else
case when [value] between 100000 and 200000 then [value]*0.2
else
[value]*0.3 end
end)
)'value' from #A1 group by ID
ID value
------ ---------------------------------------
1001 24000.0
1002 30000.0(2 行受影响)
你这条语句要稍微改改.
CASE WHEN 0 <VALUE and VALUE<100000 THEN 0.1
CASE WHEN 100000 <=VALUE and VALUE<200000 THEN 0.2
CASE WHEN VALUE>=200000 THEN 0.3
END 或者CASE WHEN VALUE between 0 < and 99999 THEN 0.1
CASE WHEN value between 100000 and 200000 THEN 0.2
CASE WHEN VALUE>=200000 THEN 0.3
END
insert into A values(1001 ,120000)
insert into A values(1002 ,150000)
create table B(ID int,MEMO varchar(200))
insert into B values(1001 ,'CASE WHEN VALUE between 0 and 99999 THEN 0.1 WHEN value between 100000 and 199999 THEN 0.2 WHEN VALUE>=200000 THEN 0.3 END ')
insert into B values(1002 ,'')
godeclare @sql1 as varchar(200)
declare @sql2 as varchar(10)select @sql1 = b.memo , @sql2 = cast(a.value as varchar) from a , b where a.id = b.id and b.memo <> ''exec('select ' + @sql2 + '*' + @sql1 + ' from (select ' + @sql2 + ' as value) t')drop table A , B/*
----------
24000.0
*/
在动态执行字符串就行了
create table #t1(
ID varchar(10), value int )
insert into #t1
select '1001', 120000 union all
select '1002', 150000 create table #t2(
ID varchar(10), MEMO varchar(8000))
insert into #t2
select '1001', 'CASE WHEN A.VALUE>0 AND A.VALUE<100000 THEN A.VALUE*0.1 ' +
' WHEN A.VALUE>=100000 AND A.VALUE<200000 THEN A.VALUE*0.2 ' +
' WHEN A.VALUE>=200000 THEN A.VALUE*0.3 '+
'END' union all
select '1002', ''
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',','')+MEMO+SPACE(1)+''''+ID+'''' FROM #T2 WHERE MEMO<>''
SELECT @S='SELECT '+@S+' FROM #T1 A JOIN #T2 B ON A.ID=B.ID WHERE MEMO<>'''''
EXEC (@S)/*
1001
--------------
24000.0
*/
不知道是不是用的不是一个版本的sql server的问题
declare @sql1 as varchar(200)
declare @sql2 as varchar(10)
declare @sql3 as varchar(2000)
select @sql1 = b.memo , @sql2 = cast(a.value as varchar) from a , b where a.id = b.id and b.memo <> ''
select @sql3='select ' + @sql2 + '*(' + @sql1 + ') from (select value from a, b where a.id=b.id and b.memo<>'''') t'
print @sql3
exec(@sql3)