Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "mysql query results how to achieve multi-column stitching query". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to achieve multi-column splicing query on mysql query results" can help you solve the problem.

There is a problem encountered before. There are two tables in the mysql database. One address table stores location information such as provinces, cities and autonomous regions, and another user table contains three fields corresponding to the three location information in the address table (strange table format), as shown in the figure:

Now we need to query the data in the user table, and according to the values of provinces and cities in the user table, query the corresponding location information (name) in the address table and display it.

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

First of all, we need to get the values of the three fields of the provinces, cities and regions of the user table. Our general query method is

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

This is relatively simple, and the result is a row and three columns of values, as shown in the figure:

This is the normal way to write, and 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 three segments, which is not conducive to our later query. You can't take out the results and then deal with them. This is too troublesome. At this time, two functions of sql, CONCAT_WS and CONCAT, are used to concatenate the results into a string. The specific usage can be Baidu. There are no more introductions here:)

Therefore, our query statement can be written as SELECT CONCAT_WS (',', province,city,district) AS ids FROM `user` WHERE id = 1. The result is shown below:

With this result, we can basically query the address information, and we also do the corresponding stitching 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 shown in the figure:

But in fact, this statement can not get the desired result, because the query 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 thinking again, so how can we use this string as a query condition and finally get the result we want? According to the information on the Internet, the content of the query condition in can only be numeric, so it does not support string query, so here we also need a function, instr. To tell you the truth, this is the first time I know this function, after all, mysql is really only a little added, deleted, changed and checked, . So let's try the effect of this function next, and the final sql is

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

The result after implementation is also what we want. Of course, the above statement only realizes the result of finding the address, and the specific can be modified according to business requirements. The function of mysql has to be said to be really powerful. We still need to learn more in the future.

So, the final sql is:

SELECT GROUP_CONCAT (`name`) FROM addressWHERE INSTR (CONCAT (',', (SELECT CONCAT_WS (', province, city, district) FROM `user`WHERE id = 1),','), CONCAT (',', id,',')). This is the end of the content on "how to implement multi-column stitching query for mysql query results". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.