How to query multiple cities in one field of mysql .

What do you mean.

For example, there is a name field in table A where 1-2-3-4 is stored

Then my understanding is that you want the query to show the result is.

Beijing-Shanghai-Shandong-Guangzhou ?

Want to replace 1-2-3-4 in the name field of table A with what I wrote?

I have to say that your data planning problem is particularly big. Moreover... In this way, multiplication occurs when multiple tables are queried.

Let me think about how to look it up.

You're screwed. If you just want to see the effect, there's a way, but you have to execute the statement a few more times, and you can only replace one character at a time.

First of all, you copy your A table, and we'll take C.

For example, 1 is Beijing and the other 234 I will choose the name at will.

update c set name=replace(name,'1','Beijing');

update c set name=replace(name,'2','Shanghai');

update c set name=replace(name,'3','Shandong');

And so on, and finally the C table will look like this.

All fields are replaced.

Personal suggestion: Take advantage of this time to sort out the relationship clearly.

Of course, you can also use select to view it, so that you don't need to modify it, and you can read the statement all at once.

select replace(name,'1-2-3-4','Beijing-Shanghai-Shandong-Guangzhou ? ') as from c

Beijing-Shanghai-Shandong-Guangzhou ?

declare @i int,@j int,@len intselect @str = name from aset @i=1

set @j=len(@str)

declare @num int

while @i<@j

beginset @num=charindex('-',@str,@i)

select * from b where id=substring(@str,@i,@num-@i)

set @i=@i+1

set @i=@num+1

endselect * from b where id=substring(@str,@i,@j-@num)

This is in the sqlserver environment, and you can get it by executing it directly.

1 Beijing. 2 Shanghai.

3 Guangzhou. 4 Suzhou".

results, I hope it helps you.

Is this the effect of finding out?

1 Beijing. 2 Shanghai.

3 Guangzhou. 4 Suzhou".

Check it 2 times first, split it into SQL parameters and then check it.

You can post the table data and describe the effect in detail.

Is this the effect of finding out? 1-Beijing, 2-Shanghai, 3-Guangzhou, 4-Suzhou.

Is this the effect of finding out? 1-Beijing, 2-Shanghai, 3-Guangzhou, 4-Suzhou.

select * from tablename where ziduan1 =1 or ziduan2 = 1 You can go to W3cschool and see these statements or something.

See the following MySQL experiments:

Generate the orderproduct table.

create

tableorderproduct(orderid

char(11)

primary

key,productid

varchar(255));

product table.

create

tableproduct(productid

varchar(10)

primary

key,productname

varchar(50));

Insert data into the order table.

insert

intoorderproduct

values

20161116001,'d0020'),(20161116035,'e0055'),(20161101048,'a0035'),(20161005321,'b0049'),(20160901515,'c0038'),(20160814525,'c0038,a0035,e0055'),(20160714510,'d0020,b0049');

Insert data into the product table.

insert

intoproduct

values

d0020','Lipton brand green tea'),'e0055','Vietnamese buns'),'a0035','Jewelry desk calendar'),'b0049','Hand cream'),'c0038','Sports kettle');

select

fromorderproduct;

select

fromproduct;

Returns the SQL statement of the first question.

select

min(asproductid,group_concat(

asproductname

fromorderproduct

a,product

b where

in('Sports kettle','Jewelry desk calendar','Vietnamese buns')andinstr(,>0

group-- the SQL statement that returns the second question.

select

min(asproductid,group_concat(

asproductname

fromorderproduct

a,product

b where

in('Lipton brand green tea','Hand cream')

andinstr(,>0

group;

in(v1,v2..vn), v1, v2,,,vn can be detected.

The in keyword determines whether the value of a field is in a specified set. If the value of the field is in the set, the query condition is met, and the record will be queried. If it's not in the collection, the query criteria aren't met.

The syntax rules are as follows: [ not ] in ( element 1, element 2, ...element n).

For example: select * from student where stu age in(11,12);

Add a count and you're good to go, and the statement is as follows.

select count(distinct age) from table