dream6120
2015-06-16 10:43
浏览 263
已采纳

mysql中的字符串拆分和子查询

first of all i have 1 table in database.

1)tags :

id    name
1     theme1=test1
2     theme1=test2
3     theme1=test3
4     theme2=test1
5     theme2=test2
6     theme2=test3

And i have bunch of id of tags in array. like 1,3.

Now,

1)select name from tags where id=1

result: theme1=test1

(now using wildcard)

2)select id from tags where name like 'theme_test1'

result : 1,4

(here 'theme_test1' need to take from query1)

I am getting output proper but need to use 2 query.I want to do this in single query.

Thanks

图片转代码服务由CSDN问答提供 功能建议

首先,我在数据库中有1个表。

1)标签:

  id name 
1 theme1 = test1 
2 theme1 = test2 
3 theme1 = test3 
4 theme2 = test1 
5 theme2 = test2 \  n6 theme2 = test3 
   
 
 

我在数组中有标签 id 。 如1,3。

现在,

1)从标签中选择名称,其中id = 1

结果:theme1 = test1

(现在使用通配符)

2)从标签中选择id,其中名称为'theme_test1 '

结果:1​​,4

(此处'theme_test1'需要从query1获取)

我输出正确,但需要使用2个查询。我想在单个查询中执行此操作。

谢谢

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dsj60862 2015-06-16 11:08
    已采纳
    SELECT id FROM tags WHERE name LIKE ( 
        SELECT CONCAT(SUBSTRING(name,1,5),'__',SUBSTRING(name,8)) FROM tags WHERE id=1 
    )
    

    Returns 1,4

    But Two queries (or a refactor) might be a better option

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duanhuan5409 2015-06-16 11:06

    If you whant to get the id with the same value you can try this:

    SELECT t2.* FROM yourtable t1
    JOIN youtable t2 on ON  t2.name like concat(substr(t1.name,1,5), '%', substr(t1.name,8))
    WHERE t1.id=1;
    
    评论
    解决 无用
    打赏 举报
  • doumei7420 2015-06-16 11:07

    maybe you can use query :

    select id from tags where name = (select name from tags where id = 1 ).

    You can try that query.

    评论
    解决 无用
    打赏 举报
  • dongzong1866 2015-06-16 11:54

    For performance better use this:

    SELECT t.id 
    FROM r 
    INNER JOIN r AS t ON t.name LIKE CONCAT('theme_=test', SUBSTRING(r.name,-1))
    WHERE r.id = '1'
    

    r is your table in this case.

    NOTE: this answer isn't valid in case that you have theme1=test1 and theme1=test10 values.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题