How to implement multi-column splicing query in mysql query results

I encountered a problem before. There are two tables in the mysql database. One address table stores location information such as provinces, cities, and cities. The other user table has three fields corresponding to the three location information in the address table. (Very strange table format), as shown in the figure:

Now we need to query the data in the user table and save the data based on the user table For the value of the urban area, the corresponding location information (name) is queried in the address table and displayed;

I am not proficient in sql, so I figured out a general logic

First of all, we must get For the values ​​of the three fields of province and city in the user table, our general query method is

SELECT province,city,district FROM `user` WHERE id =1;

. This is relatively simple. The result obtained is the value of one row and three columns, as shown in the figure:

This is a normal way of writing. Then use this result as the query condition to query the name in the address table.

Of course the idea seems good, but the result of this query is divided into It is three-segment, which is not conducive to our subsequent queries. We can't retrieve the results and then process and query again. This is too troublesome. At this time, we need to use two SQL functions, CONCAT_WS and CONCAT. The functions of the two functions It is to splice the results into a string. The specific usage can be found on Baidu. I won’t introduce it here:)

So our query statement can be written asSELECT CONCAT_WS(',',province ,city,district) AS ids FROM `user` WHERE id =1;The result obtained is as shown in the figure:

With this result we can basically query When it comes to the address information, we also did the corresponding splicing when querying the address information, so the final sql is like this

SELECT GROUP_CONCAT(`name`) FROM address WHERE id IN (SELECT CONCAT_WS(',',province,city,district) FROM `user` WHERE id =1);

The ideal result is as shown in the figure:

But in fact, this statement cannot get the results we want. The reason is that the result of SELECT CONCAT_WS(',',province,city,district) FROM `user` WHERE id =1 is A string, but the string cannot be used as the condition of In in the query condition, so the query result is not what we want.

There is no problem with our idea, so how can we use it? How can we use this string as a query condition and finally get the results we want? According to information on the Internet, the content of the query condition in can only be numeric, so it does not support string queries, so here we I also need a function, instr. To be honest, this is the first time I know this function. After all, I really only know a little bit about adding, deleting, modifying and checking mysql, haha. Then let’s try the effect of this function, the final sql The result after execution for

SELECT
  GROUP_CONCAT(`name`)
FROM
  address
WHERE INSTR(
    (SELECT
      CONCAT_WS(',', province, city, district)
    FROM
      `user`
    WHERE id = 1),
    id
  ) ;

is also what we want. Of course, the above statement only realizes the result of finding the address. The specifics can be modified according to business needs. The functions of mysql have to be said to be really powerful. I still need to learn more in the future;

So, the final sql is:

SELECT
  GROUP_CONCAT(`name`)
FROM
  address
WHERE INSTR(
    CONCAT(
      ',',
      (SELECT
        CONCAT_WS(',', province, city, district)
      FROM
        `user`
      WHERE id = 1),
      ','
    ),
    CONCAT(',', id, ',')
  ) ;

The above is the detailed content of How to implement multi-column splicing query in mysql query results. For more information, please follow other related articles on the PHP Chinese website!