if QTY>1 then 10
if QTY>10 then 20
........
十位的时候十进
if QTY>100 then 200
.....
百位的时候百进
if QTY>1000 then 2000
.....
千位的时候千进
请问上面的语句如何用SQL语句实现
if QTY>10 then 20
........
十位的时候十进
if QTY>100 then 200
.....
百位的时候百进
if QTY>1000 then 2000
.....
千位的时候千进
请问上面的语句如何用SQL语句实现
case QTY>1 then 10 最多只能套十个
现在要求每个QTY值都进行进位
求FQTY
表A
PName QTY FQTY
A 1 10
B 11 20
C 23 30
A 101 200
.........
B 1100 2000
C 3999 4000
WHEN QTY>10 then 20
WHEN QTY>100 then 200
WHEN QTY>1000 then 2000
END
FROM TB
insert into tb values('A' , 1 , 0)
insert into tb values('B' , 11 , 0)
insert into tb values('C' , 23 , 0)
insert into tb values('A' , 101 , 0)
insert into tb values('B' , 1100 , 0)
insert into tb values('C' , 3999 , 0)
goselect pname,qty ,
case when qty/10 = 0 then qty
when qty/10 > 0 and qty/10 < 10 then (qty/10+1) * 10
when qty/100 = 0 then qty
when qty/100 > 0 and qty/100 < 10 then (qty/100+1) * 100
when qty/1000 = 0 then qty
when qty/1000 > 0 and qty/1000 < 10 then (qty/1000+1) * 1000
end as FQT
from tbdrop table tb/*
pname qty FQT
---------- ----------- -----------
A 1 1
B 11 20
C 23 30
A 101 200
B 1100 2000
C 3999 4000(所影响的行数为 6 行)
*/
套嵌case语句来一个个判断行不通
insert into tb values('A' , 1 , 0)
insert into tb values('B' , 11 , 0)
insert into tb values('C' , 23 , 0)
insert into tb values('A' , 101 , 0)
insert into tb values('B' , 1100 , 0)
insert into tb values('C' , 3999 , 0)
insert into tb values('C' , 39999 , 0)
insert into tb values('C' , 399900 , 0)
goselect pname,qty ,
case when qty/10 >= 0 and qty/10 < 10 then (qty/10+1) * 10
when qty/100 >= 0 and qty/100 < 10 then (qty/100+1) * 100
when qty/1000 >= 0 and qty/1000 < 10 then (qty/1000+1) * 1000
when qty/10000 >= 0 and qty/10000 < 10 then (qty/10000+1) * 10000
when qty/100000 >= 0 and qty/100000 < 10 then (qty/100000+1) * 100000
when qty/1000000 >= 0 and qty/1000000 < 10 then (qty/1000000+1) * 1000000
when qty/10000000 >= 0 and qty/10000000 < 10 then (qty/10000000+1) * 10000000
when qty/100000000 >= 0 and qty/100000000 < 10 then (qty/100000000+1) * 100000000
end as FQT
from tbdrop table tb/*
pname qty FQT
---------- ----------- -----------
A 1 10
B 11 20
C 23 30
A 101 200
B 1100 2000
C 3999 4000
C 39999 40000
C 399900 400000(所影响的行数为 8 行)
*/
CREATE TABLE A(PName NVARCHAR(10),QTY INT)
INSERT INTO A(PName ,QTY)
SELECT 'A',1 UNION ALL
SELECT 'B',11 UNION ALL
SELECT 'C',23 UNION ALL
SELECT 'A',101 UNION ALL
SELECT 'B',1100 UNION ALL
SELECT 'C',3999 UNION ALL
SELECT 'B',11000 UNION ALL
SELECT 'C',39990 UNION ALL
SELECT 'B',110000 UNION ALL
SELECT 'C',399900
GO
--查詢數據
SELECT PName,QTY,FQTY=
(CASE
WHEN QTY>0 AND QTY<=10 THEN CEILING(QTY/10.0)*10
WHEN QTY>10 AND QTY<=100 THEN CEILING(QTY/10.0)*10
WHEN QTY>100 AND QTY<=1000 THEN CEILING(QTY/100.0)*100
WHEN QTY>1000 AND QTY<=10000 THEN CEILING(QTY/1000.0)*1000
WHEN QTY>10000 AND QTY<=100000 THEN CEILING(QTY/10000.0)*10000
WHEN QTY>100000 AND QTY<=1000000 THEN CEILING(QTY/100000.0)*100000
END)
FROM A
GO
--刪除測試數據
DROP TABLE A
GO
/*查詢結果:
(10 個資料列受到影響)
PName QTY FQTY
---------- ----------- ---------------------------------------
A 1 10
B 11 20
C 23 30
A 101 200
B 1100 2000
C 3999 4000
B 11000 20000
C 39990 40000
B 110000 200000
C 399900 400000(10 個資料列受到影響)
*/
不過那樣比這個CASE好像更麻煩!
create table tb(PName varchar(10) , QTY int, FQTY int)
insert into tb values('A' , 1 , 0)
insert into tb values('B' , 11 , 0)
insert into tb values('C' , 23 , 0)
insert into tb values('A' , 101 , 0)
insert into tb values('B' , 1100 , 0)
insert into tb values('C' , 3999 , 0)
gocreate function fint(@s bigint) returns bigint
as
begin
declare @i bigint,@r bigint
set @i=1
set @r=1
while @i<len(@s)
begin
set @r=@r*10
set @i=@i+1
end
if @r<>@S
set @r=(@s/@r+1)*@r
return @r
end
goselect pname,qty,fqt=dbo.fint(qty) from tbdrop table tb
drop function fint/*
pname qty fqt
---------- ----------- --------------------
A 1 1
B 11 20
C 23 30
A 101 200
B 1100 2000
C 3999 4000
*/