mysql 一行多值取其中一个

[FQCWXY]

如果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的值数量不固定

[ACMAIN_CHM]

FQCWXY 结帖率：33.33%

[搬砖的IT农民工]

SELECT dwdm,SUBSTRING_INDEX(***,'|',1) sjdwdm FROM qx_qy
UNION
SELECT dwdm,SUBSTRING_INDEX(***,'|',-1) sjdwdm FROM qx_qy	
						

这是两个的，自己类推

[SQL77]

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*/ 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',''))

[FQCWXY]

ACMAIN_CHM，因为我提问的少，有一次每结帖，加上这次就变的很低了。不好意思，提醒的极对，不知道能否帮忙解答否

[SQL77]

自己写个函数了。

[yl784248831]

用代码实现。。。。