作者shadowjohn (轉角遇到愛)
看板Programming
標題Re: [問題] 1-9位數不重複印出來 (MySQL or MariaDB)
時間Wed Dec 7 11:25:37 2016
※ 引述《mikemagic88 (Mikemagic88)》之銘言：
: 使用者輸入1 印1-9
: 使用者輸入2 印1-98 (11, 22, 33等重複的不印)
: 使用者輸入3 印1-987 (121, 988, 667等有重複的不印)
DROP FUNCTION IF EXISTS is_having_same_char;
CREATE FUNCTION is_having_same_char(num INT(10))
RETURNS INT READS SQL DATA
BEGIN
DECLARE isfound INT;
DECLARE STEP INT;
DECLARE WORD_LENGTH INT;
DECLARE WORD TEXT;
DECLARE THECHAR VARCHAR(1);
SET isfound = 0;
SET STEP=0;
SET WORD_LENGTH=LENGTH(num);
SET WORD='';
SET THECHAR='';
count_loop: LOOP
SET STEP=STEP+1;
SET THECHAR = SUBSTRING(num,STEP,1);
IF INSTR(WORD,THECHAR) != 0 THEN
#SET isfound = 1;
#LEAVE count_loop;
RETURN 1;
END IF;
set WORD = concat(WORD,THECHAR);
IF STEP >= WORD_LENGTH THEN
LEAVE count_loop;
END IF;
END LOOP;
RETURN isfound;
END;
DROP FUNCTION IF EXISTS mycode;
CREATE FUNCTION mycode(num INT(10))
RETURNS LONGTEXT READS SQL DATA
BEGIN
DECLARE step INT;
DECLARE OUTPUT LONGTEXT;
SET OUTPUT = '';
SET step = 0;
count_loop: LOOP
SET step = step + 1;
IF is_having_same_char(step) = 0 THEN
SET OUTPUT = CONCAT(OUTPUT,step,", ");
END IF;
IF step = POW(10,num)-1 THEN
LEAVE count_loop;
END IF;
END LOOP;
RETURN SUBSTRING(OUTPUT,1,LENGTH(OUTPUT)-2);
END;
# 使用方式
select mycode(3);
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 140.134.48.253
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Programming/M.1481081141.A.A0B.html
1F：→ shadowjohn: 效率有點差，掩面 (羞..) 140.134.48.253 12/07 11:30
2F：推 Neisseria: 會想到用 SQL 實作，真是很有才 XD 125.227.36.80 12/07 11:47
3F：推 fourdollars: 94 狂啊！ XD 36.227.52.184 12/07 16:49
4F：→ fourdollars: 誰能來個 brainfuck 吧～XD 36.227.52.184 12/07 16:51