在mysql5.0里创建了一函数代码如下:
DELIMITER $$;DROP FUNCTION IF EXISTS `newspaper`.`f_hzcode`$$CREATE FUNCTION `f_hzcode`() RETURNS char(1)
BEGIN
declare fpy char(1);
declare pc char(1);
declare cc char(4);
set @fpy = UPPER(left(words,1));
set @pc = (CONVERT(@fpy USING gbk));
set @cc = hex(@pc);
if @cc >= "8140" and @cc <="FEA0" then
begin
select PY from tbl_py where hz>=@pc limit 1 into @fpy;
end;
end if;
Return @fpy;
END$$DELIMITER ;$$执行查询:
select f_hzcode('在');出错提示:
错误代码: 1318
Incorrect number of arguments for FUNCTION newspaper.f_hzcode; expected 0, got 1
(耗费 0 ms)什么原因???
DELIMITER $$;DROP FUNCTION IF EXISTS `newspaper`.`f_hzcode`$$CREATE FUNCTION `f_hzcode`() RETURNS char(1)
BEGIN
declare fpy char(1);
declare pc char(1);
declare cc char(4);
set @fpy = UPPER(left(words,1));
set @pc = (CONVERT(@fpy USING gbk));
set @cc = hex(@pc);
if @cc >= "8140" and @cc <="FEA0" then
begin
select PY from tbl_py where hz>=@pc limit 1 into @fpy;
end;
end if;
Return @fpy;
END$$DELIMITER ;$$执行查询:
select f_hzcode('在');出错提示:
错误代码: 1318
Incorrect number of arguments for FUNCTION newspaper.f_hzcode; expected 0, got 1
(耗费 0 ms)什么原因???
;2. DELIMITER ;
$$3. CREATE FUNCTION `f_hzcode`() RETURNS char(1) DETERMINISTICmysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `newspaper`.`f_hzcode`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE FUNCTION `f_hzcode`() RETURNS char(1) DETERMINISTIC
-> BEGIN
-> declare fpy char(1);
-> declare pc char(1);
-> declare cc char(4);
-> set @fpy = UPPER(left(words,1));
-> set @pc = (CONVERT(@fpy USING gbk));
-> set @cc = hex(@pc);
-> if @cc >= "8140" and @cc <="FEA0" then
-> begin
-> select PY from tbl_py where hz>=@pc limit 1 into @fpy;
-> end;
-> end if;
-> Return @fpy;
-> END$$
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> DELIMITER ;
mysql>
它的表数据如下:
insert into tbl_py values
('A','骜'),
('B','簿'),
('C','错'),
('D','鵽'),
('E','樲'),
('F','鳆'),
('G','腂'),
('H','夻'),
('J','攈'),
('K','穒'),
('L','鱳'),
('M','旀'),
('N','桛'),
('O','沤'),
('P','曝'),
('Q','囕'),
('R','鶸'),
('S','蜶'),
('T','箨'),
('W','鹜'),
('X','鑂'),
('Y','韵'),
('Z','咗');
你能不能帮我改一下,能够实现查每个字的首字母,而不是查第一个字的首字母
`PY` char(1) character set utf8 NOT NULL,
`HZ` char(1) NOT NULL default '',
PRIMARY KEY (`PY`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
insert into tbl_py values
('A','骜'),
('B','簿'),
('C','错'),
('D','鵽'),
('E','樲'),
('F','鳆'),
('G','腂'),
('H','夻'),
('J','攈'),
('K','穒'),
('L','鱳'),
('M','旀'),
('N','桛'),
('O','沤'),
('P','曝'),
('Q','囕'),
('R','鶸'),
('S','蜶'),
('T','箨'),
('W','鹜'),
('X','鑂'),
('Y','韵'),
('Z','咗');
DELIMITER $$DROP FUNCTION IF EXISTS `f_hzcode`$$CREATE FUNCTION `f_hzcode`(
words varchar(255)) RETURNS char(1) CHARSET utf8
BEGIN
declare fpy char(1);
declare pc char(1);
declare cc char(4);
set @fpy = UPPER(left(words,1));
set @pc = (CONVERT(@fpy USING gbk));
set @cc = hex(@pc);
if @cc >= "8140" and @cc <="FEA0" then
begin
select PY from tbl_py where hz>=@pc limit 1 into @fpy;
end;
end if;
Return @fpy;
END$$DELIMITER ;
mysql>
mysql> DROP FUNCTION IF EXISTS `f_hzcode`$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE FUNCTION `f_hzcode`(
-> words varchar(255) CHARSET gbk ) RETURNS varchar(255) CHARSET gbk DETERMINISTIC
-> BEGIN
-> declare iLength int;
-> declare i int default 1;
-> declare c CHAR CHARSET gbk;
-> declare p CHAR CHARSET gbk;
-> declare s varchar(255) CHARSET gbk default '';
->
-> set iLength=CHAR_LENGTH(words);
-> while i<=iLength do
-> set c=mid(words,i,1);
-> select max(PY) into p from tbl_py where HZ<=c;
-> set s=concat(s,p);
-> set i=i+1;
-> end while;
-> Return s;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> select f_hzcode('骜攈箨');
+--------------------+
| f_hzcode('骜攈箨') |
+--------------------+
| AJT |
+--------------------+
1 row in set (0.00 sec)mysql>
查只有连续三个字的并且头字母都为AJT的记录,如果记录中有AJT字母的记录也算符合条件分数追加20分,谢谢。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`id` int(11) NOT NULL auto_increment,
`name` varchar(40) default NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk insert into p(name) values('广州'),('广州文摘'),('晚报'),('广州日报②'),('gz广州'),('羊城晚报');比如:select f_hzcode('gz');
结果:
广州
广州文摘
广州日报②
gz广州select f_hzcode('gzw');
结果:
广州文摘select f_hzcode('ycwb');
结果:
羊城晚报
结果:
羊城晚报
结果:
羊城晚报select f_hzcode('yc');
结果:
羊城晚报select f_hzcode('ycw');
结果:
羊城晚报
就先用你说的这个吧,不过有些字查不出来如:“陲”字mysql> create table t_cosler(
-> f_PY char primary key,
-> cBegin SMALLINT UNSIGNED not null,
-> cEnd SMALLINT UNSIGNED not null
-> );
Query OK, 0 rows affected (0.09 sec)mysql> insert into t_cosler values
-> ('A',0xB0A1,0xB0C4),
-> ('B',0xB0C5,0xB2C0),
-> ('C',0xB2C1,0xB4ED),
-> ('D',0xB4EE,0xB6E9),
-> ('E',0xB6EA,0xB7A1),
-> ('F',0xB7A2,0xB8C0),
-> ('G',0xB8C1,0xB9FD),
-> ('H',0xB9FE,0xBBF6),
-> ('J',0xBBF7,0xBFA5),
-> ('K',0xBFA6,0xC0AB),
-> ('L',0xC0AC,0xC2E7),
-> ('M',0xC2E8,0xC4C2),
-> ('N',0xC4C3,0xC5B5),
-> ('O',0xC5B6,0xC5BD),
-> ('P',0xC5BE,0xC6D9),
-> ('Q',0xC6DA,0xC8BA),
-> ('R',0xC8BB,0xC8F5),
-> ('S',0xC8F6,0xCBF9),
-> ('T',0xCBFA,0xCDD9),
-> ('W',0xCDDA,0xCEF3),
-> ('X',0xCEF4,0xD188),
-> ('Y',0xD1B9,0xD4D0),
-> ('Z',0xD4D1,0xD7F9);
阿a
呵a
吖a
嗄a
腌a
锕a
錒a
厑aes
爱ai
矮ai
挨ai
哎ai
碍ai
癌ai
艾ai
唉ai
哀ai
蔼ai
隘ai
埃ai
皑ai
呆ai
嗌ai
嫒ai
瑷ai
暧ai
捱ai
砹ai
嗳ai
锿ai
霭ai
乂ai
乃ai
伌ai
僾ai
儗ai
凒ai
剀ai
剴ai
叆ai
呃ai
呝ai
啀ai
嘊ai
噫ai
噯ai
堨ai
塧ai
壒ai
奇ai
娭ai
娾ai
嬡ai
嵦ai
愛ai
懓ai
懝ai
敱ai
敳ai
昹ai
曖ai
欬ai
欸ai
毐ai
溰ai
溾ai
濭ai
烠ai
焥ai
璦ai
皚ai
皧ai
瞹ai
硋ai
磑ai
礙ai
絠ai
薆ai
藹ai
諰ai
譪ai
譺ai
賹ai
躷ai
醷ai
鎄ai
鑀ai
閡ai
阂ai
阨ai
阸ai
隑ai
靄ai
靉ai
餲ai
馤ai
騃ai
鯦ai
鱫ai
鴱ai
按an
安an
暗an
岸an
+----+----+
| PY | HZ |
+----+----+
| A | 啊 |
| B | 芭 |
| C | 擦 |
| D | 搭 |
| E | 蛾 |
| F | 发 |
| G | 噶 |
| H | 哈 |
| J | 击 |
| K | 喀 |
| L | 垃 |
| M | 妈 |
| N | 拿 |
| O | 哦 |
| P | 啪 |
| Q | 期 |
| R | 然 |
| S | 撒 |
| T | 塌 |
| W | 挖 |
| X | 昔 |
| Y | 压 |
| Z | 匝 |
+----+----+
23 rows in set (0.00 sec)mysql> select * from p;
+----+------------+
| id | name |
+----+------------+
| 1 | 广州 |
| 2 | 广州文摘 |
| 3 | 晚报 |
| 4 | 广州日报② |
| 5 | gz广州 |
| 6 | 羊城晚报 |
+----+------------+
6 rows in set (0.00 sec)mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `f_hzcode`$$
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> CREATE FUNCTION `f_hzcode`(
-> words varchar(255) CHARSET gbk ) RETURNS varchar(255) CHARSET gbk DETE
RMINISTIC
-> BEGIN
-> declare iLength int;
-> declare i int default 1;
-> declare c CHAR CHARSET gbk;
-> declare p CHAR CHARSET gbk;
-> declare s varchar(255) CHARSET gbk default '';
->
-> set iLength=CHAR_LENGTH(words);
-> while i<=iLength do
-> set c=mid(words,i,1);
-> select max(PY) into p from tbl_py where HZ<=c;
-> set s=concat(s,IFNULL(p,''));
-> set i=i+1;
-> end while;
-> Return s;
-> END$$
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> DELIMITER ;
mysql>
mysql> select * from p where f_hzcode(name) like 'GZ%';
+----+------------+
| id | name |
+----+------------+
| 1 | 广州 |
| 2 | 广州文摘 |
| 4 | 广州日报② |
| 5 | gz广州 |
+----+------------+
4 rows in set (0.00 sec)mysql> select * from p where f_hzcode(name) like 'GZW%';
+----+----------+
| id | name |
+----+----------+
| 2 | 广州文摘 |
+----+----------+
1 row in set (0.00 sec)mysql> select * from p where f_hzcode(name) like 'YCWB%';
+----+----------+
| id | name |
+----+----------+
| 6 | 羊城晚报 |
+----+----------+
1 row in set (0.01 sec)mysql> select * from p where f_hzcode(name) like 'Y%';
+----+----------+
| id | name |
+----+----------+
| 6 | 羊城晚报 |
+----+----------+
1 row in set (0.00 sec)mysql> select * from p where f_hzcode(name) like 'YC%';
+----+----------+
| id | name |
+----+----------+
| 6 | 羊城晚报 |
+----+----------+
1 row in set (0.00 sec)mysql> select * from p where f_hzcode(name) like 'YCW%';
+----+----------+
| id | name |
+----+----------+
| 6 | 羊城晚报 |
+----+----------+
1 row in set (0.00 sec)mysql>