Re: [問題] 1-9位數不重複印出來 (MySQL or MariaDB)
※ 引述《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://www.ptt.cc/bbs/Programming/M.1481081141.A.A0B.html
→
12/07 11:30, , 1F
12/07 11:30, 1F
推
12/07 11:47, , 2F
12/07 11:47, 2F
推
12/07 16:49, , 3F
12/07 16:49, 3F
→
12/07 16:51, , 4F
12/07 16:51, 4F