如果test中country的第一个值为wo,则取下一个,如果都为wo,则取wo
create table test(id int,country varchar(500));
insert into test(id,country) values (1,'wo|wo|us|wo'); /*该行取us*/
insert into test(id,country) values (2,'cn|wo|jp'); /*该行取cn*/
insert into test(id,country) values (3,'uk'); /*该行取uk*/
insert into test(id,country) values (4,'an|wo|wo'); /*该行取an*/
insert into test(id,country) values (5,'wo|wo|wo'); /*该行取wo*/ 麻烦高手指点下,这个该怎么实现。country的值数量不固定
create table test(id int,country varchar(500));
insert into test(id,country) values (1,'wo|wo|us|wo'); /*该行取us*/
insert into test(id,country) values (2,'cn|wo|jp'); /*该行取cn*/
insert into test(id,country) values (3,'uk'); /*该行取uk*/
insert into test(id,country) values (4,'an|wo|wo'); /*该行取an*/
insert into test(id,country) values (5,'wo|wo|wo'); /*该行取wo*/ 麻烦高手指点下,这个该怎么实现。country的值数量不固定
结帖率:33.33%
UNION
SELECT dwdm,SUBSTRING_INDEX(***,'|',-1) sjdwdm FROM qx_qy
这是两个的,自己类推
INSERT INTO test(id,country) VALUES (1,'wo|wo|us|wo'); /*该行取us*/
INSERT INTO test(id,country) VALUES (2,'cn|wo|jp'); /*该行取cn*/
INSERT INTO test(id,country) VALUES (3,'uk'); /*该行取uk*/
INSERT INTO test(id,country) VALUES (4,'an|wo|wo'); /*该行取an*/
INSERT INTO test(id,country) VALUES (5,'wo|wo|wo'); /*该行取wo*/
DROP FUNCTION IF EXISTS f_substr;
DELIMITER //
CREATE FUNCTION f_substr(country VARCHAR(500))
RETURNS VARCHAR(15)
BEGIN
-- declare @s varchar(15);
-- DECLARE @t varchar(500);
SET @t=country;
SET @t=CONCAT('|',@t);
IF CHAR_LENGTH(REPLACE(@t,'|wo',''))=0
THEN
SET @s='wo';
ELSE
SET @s=REPLACE(@t,'|wo','');
SET @S=TRIM(LEADING '|' FROM @s);
SET @s=CONCAT(@s,'|');
SET @S=SUBSTRING(@s,1,LOCATE('|',@s)-1);
END IF;
RETURN @s;
END
//
DELIMITER ; # SELECT * FROM test WHERE CONCAT('|', country ,'|') NOT LIKE '%\|wo|%'
SELECT *,f_substr(country) FROM test ;
/*
id country f_substr(country)
1 wo|wo|us|wo us
2 cn|wo|jp cn
3 uk uk
4 an|wo|wo an
5 wo|wo|wo wo
*/
# select replace('wo|wo|wo','wo','');
# SELECT CHAR_LENGTH(REPLACE('|wo|wo|wo','|wo',''))