将字符串与MySQL中的字段值连接[重复]

问题描述：

This question already has an answer here:

MySQL concatenation operator 6 answers

I have the need to concatenate a string with a field value in a MySQL query in order to LEFT JOIN two tables.

Table one has a column called "category_id" with numeric values, such as 61, 78, 94 and such.

Table two has a column called "query" which refers to a request route mechanism, and it has values such as "product_id=68", "category_id=74", "manufacturer_id=99" and so on.

So in my query I require to join the tables when ever a concatenated string derived from a set string and the value of the "category_id" column matches the query field.

My SQL statement is currently:

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' + tableOne.category_id

I have tried using the || operator instead of the + operator, but still no luck. Is it possible to do this in MySQL, or have I jumped the gun here?

</div>

此问题已经存在这里有一个答案： p>

MySQL 连接运算符
6 answers span> li> ul> div>
我需要在MySQL查询中将字符串与字段值连接起来，以便LEFT JOIN两个表。 p> \ n

表1有一个名为“category_id”的列，带有数值，例如61,78,94等。 p>

表二有一个名为“query”的列，它指的是请求路由机制，它具有诸如“product_id = 68”，“category_id = 74”，“manufacturer_id = 99”等值。 p>

所以在我的查询中我要求当从一个集合字符串派生的串联字符串时加入表 “category_id”列的值与查询字段匹配。 p>

我的SQL语句当前是： p>
```
  SELECT * FROM tableOne 
LEFT  JOIN tableTwo 
ON tableTwo.query ='category_id ='+ tableOne.category_id 
  code>  pre> 
 
 我试过使用|| 运算符而不是+运算符，但仍然没有运气。 是不是可以在MySQL中执行此操作，或者我在这里跳枪？ p> 
  div>
```

答

Have you tried using the concat() function?

ON tableTwo.query = concat('category_id=',tableOne.category_id)

答

MySQL uses CONCAT() to concatenate strings

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = CONCAT('category_id=', tableOne.category_id)

答

SELECT ..., CONCAT( 'category_id=', tableOne.category_id) as query2  FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = query2

答

Here is a great answer to that:

SET sql_mode='PIPES_AS_CONCAT';

Find more here: https://stackoverflow.com/a/24777235/4120319

Here's the full SQL:

SET sql_mode='PIPES_AS_CONCAT';

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' || tableOne.category_id;

将字符串与MySQL中的字段值连接[重复]

相关推荐