SQL> edi 已写入 file afiedt.buf 1 declare 2 v number; 3 t number; 4 k number; 5 begin 6 v:=12345678; 7 t:=0; 8 for i in 1..length(v) loop 9 t:=t+substr(v,i,1); 10 end loop; 11 k:=mod(t,10); 12 dbms_output.put_line('此八位数每位数字和对10取余的结果是:'||to_char(k)); 13* end; SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on SQL> / 此八位数每位数字和对10取余的结果是:6PL/SQL 过程已成功完成。
是8个88888888SQL> edi 已写入 file afiedt.buf 1 declare 2 v number; 3 t number; 4 k number; 5 begin 6 v:=88888888; 7 t:=0; 8 for i in 1..length(v) loop 9 t:=t+substr(v,i,1); 10 end loop; 11 k:=mod(t,10); 12 dbms_output.put_line('此八位数每位数字和对10取余的结果是:'||to_char(k)); 13* end; SQL> / 此八位数每位数字和对10取余的结果是:4PL/SQL 过程已成功完成。
SQL> edi 已写入 file afiedt.buf 1 create or replace function fun_mod(v number) return number 2 as 3 t number; 4 k number; 5 begin 6 t:=0; 7 for i in 1..length(v) loop 8 t:=t+substr(v,i,1); 9 end loop; 10 k:=mod(t,10); 11 return k; 12* end; SQL> /函数已创建。SQL> select fun_mod(88888888) from dual;FUN_MOD(88888888) ----------------- 4
mod 最后一位,就OK啦嘛.............select mod(substr('12345678',8,1),10) from dual;
-- (如果你的字段是字符串类型的话,灵活的算法是:select mod( substr( string_col, length(string_col)-1, 1), 10) from your_tb;
-- (如果你的字段是字符串类型的话,灵活的算法是:select mod( substr( string_col, length(string_col), 1), 10) from your_tb;
substr还可以这样select mod(sum(substr('12345678',level,1)),10) from dual connect by level <= length('12345678')
高兴了一下,不过 显然是错的 '00001999'SQL> select mod(28,10) from dual;MOD(28,10) ---------- 8SQL> select mod(9,10) from dual; MOD(9,10) ---------- 9
借用下ITPUB的测试数据,貌似将l/1e7改成l*1e-7更快一些 WITH t AS ( SELECT 1e7+LEVEL l FROM dual CONNECT BY LEVEL<=1e6) SELECT SUM(m) FROM( SELECT MOD( trunc(l*1e-7) + trunc(l*1e-6) + trunc(l*1e-5 ) + trunc(l*1e-4 )+ trunc(l*1e-3 ) + trunc(l*1e-2)+ trunc(l*1e-1)+ l ,10) m FROM t);
SQL> edi
已写入 file afiedt.buf 1 declare
2 v number;
3 t number;
4 k number;
5 begin
6 v:=12345678;
7 t:=0;
8 for i in 1..length(v) loop
9 t:=t+substr(v,i,1);
10 end loop;
11 k:=mod(t,10);
12 dbms_output.put_line('此八位数每位数字和对10取余的结果是:'||to_char(k));
13* end;
SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on
SQL> /
此八位数每位数字和对10取余的结果是:6PL/SQL 过程已成功完成。
已写入 file afiedt.buf 1 declare
2 v number;
3 t number;
4 k number;
5 begin
6 v:=88888888;
7 t:=0;
8 for i in 1..length(v) loop
9 t:=t+substr(v,i,1);
10 end loop;
11 k:=mod(t,10);
12 dbms_output.put_line('此八位数每位数字和对10取余的结果是:'||to_char(k));
13* end;
SQL> /
此八位数每位数字和对10取余的结果是:4PL/SQL 过程已成功完成。
select
floor(12345678/10000000)+
floor(mod(12345678,10000000)/1000000)+
floor(mod(12345678,1000000)/100000)+
floor(mod(12345678,100000)/10000) +
floor(mod(12345678,10000)/1000) +
floor(mod(12345678,1000)/100) +
floor(mod(12345678,100)/10) +
floor(mod(12345678,10)/1)
from dual
SQL> edi
已写入 file afiedt.buf 1 create or replace function fun_mod(v number) return number
2 as
3 t number;
4 k number;
5 begin
6 t:=0;
7 for i in 1..length(v) loop
8 t:=t+substr(v,i,1);
9 end loop;
10 k:=mod(t,10);
11 return k;
12* end;
SQL> /函数已创建。SQL> select fun_mod(88888888) from dual;FUN_MOD(88888888)
-----------------
4
mod 最后一位,就OK啦嘛.............select mod(substr('12345678',8,1),10) from dual;
connect by level <= length('12345678')
显然是错的
'00001999'SQL> select mod(28,10) from dual;MOD(28,10)
----------
8SQL> select mod(9,10) from dual; MOD(9,10)
----------
9
select mod(TRUNC(12345678 / 1E7) + TRUNC(12345678 / 1E6) + TRUNC(12345678 / 1E5) +
TRUNC(12345678 / 1E4) + TRUNC(12345678 / 1E3) + TRUNC(12345678 / 1E2) +
TRUNC(12345678 / 1E1) + 12345678,
10) m
from dual
newkid老大的答案
这样也可以
SELECT MOD((8+87+876+8765+87654+876543+8765432+87654321) ,10) FROM dual;
底层的话换算成2进制就很快啊,MOD算法本身就是...
WITH t AS (
SELECT 1e7+LEVEL l FROM dual CONNECT BY LEVEL<=1e6)
SELECT SUM(m) FROM(
SELECT MOD(
trunc(l*1e-7) +
trunc(l*1e-6) +
trunc(l*1e-5 ) +
trunc(l*1e-4 )+
trunc(l*1e-3 ) +
trunc(l*1e-2)+
trunc(l*1e-1)+
l
,10) m
FROM t);
是我推荐的,我认为这个题目值得思考,可以动动脑筋
思路和效率是开发的关键
按字符串/数值形式、SQL/PLSQL、等等,不同的写法效率上有很大差别
上面我写的那个测试时间为3.0秒,而写了一个函数通过EXECUTE IMMEIDATE则跑了20分钟
练一练,体验下隐式转换、上下文切换导致的性能开销,我觉得不错
请教
floor(12345678/10000000)+
floor(mod(12345678,10000000)/1000000)+
floor(mod(12345678,1000000)/100000)+
floor(mod(12345678,100000)/10000) +
floor(mod(12345678,10000)/1000) +
floor(mod(12345678,1000)/100) +
floor(mod(12345678,100)/10) +
floor(mod(12345678,10)/1)
from dual
select mod(sum(val), 10)
from (select substr('12345678', rownum, 1) val
from dual
connect by rownum < 9)
{
return ((values[0] - '0') + (values[1] - '0') + (values[2] - '0') + (values[3] - '0') + (values[4] - '0') + (values[5] - '0') + (values[6] - '0') + (values[7] - '0')) % 10;
}
{
return ((values[0] + values[1] + values[2] + values[3] + values[4] + values[5] + values[6] + values[7] - ('0' << 3))) % 10;
}
#include <stdio.h>
#include <stdlib.h>typedef unsigned char uint_8;uint_8 sum(uint_8 num){
uint_8 result = 0;
uint_8 shift = 128;//10000000
int ctn = 0;
for(ctn = 7; ctn >=0;ctn--){
result +=(num&shift)>>ctn;
shift>>=1;
}
return result;
}int main(int argc, char **argv){
uint_8 num = 3;//00000011
printf("%d\n",sum(num));
num = 4;//00000100
printf("%d\n",sum(num));
return 0;
}这个不算快;
直接当成字符uint_8 sum(uint_8 num){
return (num[0]+num[1]+num[2]+num[3]+num[4]+num[5]+num[6]+num[7]);
}第三种办法,查表法,这个比较快先构造表uint_t table[] = {0,1,1,2,1,2,2,3,1,......};
表的构造是这样子的,00000000表示0,其中没有1,所以table[0] = 0; 00000001表示1, 其中只有一个1
所以,table[1] = 1; 00000011表示3, 其中有两个1, 所以table[3] = 2;这样依次类推下去,至到table[128] = 8; 这个求和就简单了,直接uint_8 sum(uint_8 num){
return table[num];
}
from (select substr('88888888',rownum,1) as a from dual connect by rownum<length('88888888'));