MYSQL有两个ORDER BY或嵌套顺序

Question

I'm stack with an (i suppose) ORDER BY problem on my web aplication. None other questions/answers seems to be same that mine or i can't apply to my problem. I'll try to explain myself as best as possible with a simple case that would resolve also my problem.

Imagine 3 tables, "Customers" "Address" and "CustomersAddress.

**Customers**
id,  name,      surname
1,  "John",    "Willis"
2,  "Sarah",   "Davies"
3,  "Mark",    "Jones"
4,  "Linda",   "Ede"

**Address**
id_dir, address
1,      "some adress"
2,      "other address"
3,      "holliday address"
4,      "my other house address"
5,      "Flat in the beach address"

**CustomersAdress**
id_ca, id_customer, id_address
1,         1,       4
2,         2,       4
3,         3,       1
4,         1,       2
5,         2,       2
6,         4,       5
7,         4,       3

It's clear that CustomersAdress is a table that relates the customers with the addresses they own.

With a simple INNER JOIN I can get all the relations as follow:

SELECT id_customer, surname, name, id_dir, address
FROM CustomersAddress
INNER JOIN Customers ON Customers.id=CustomerAddress.id_customer
INNER JOIN Address ON Address.id_dir=CustomerAddress.id_address

With these I can order with ORDER BY , for example, as follow

ORDER BY surname

Or even

ORDER BY surname, id_dir / ORDER BY surname, id_dir

Using the "ORDER BY id_dir , surname" example i would get:

idcustomer,  surname,   name,     id_dir,    address
3,           "Jones",   "Mark",   1,         "some address"
2,           "Davies",  "Sarah",  2,         "other address"
1,           "Willis",  "John",   2,         "other address"
4,           "Ede",     "Linda"   3,         "holliday address"
2,           "Davies",  "Sarah",  4,         "my other house address"
1,           "Willis",  "John",   4,         "my other house address"
4,           "Ede",     "Linda",  5,         "Flat in the beach address"

Until here nothing new. BUT what i'm trying to do is a nested order (don't know how to named) Where order would be the surname related with the address and the customers that owns that address.

Ergo, look first for the first surname (Davies) with the first address, then look for the customer with same address (independently of the surname), and get them (Willis), after that continue with next customer surname or address if there are more from the same customer. And so on.

My desire result:

idcustomer,  surname,   name,   id_dir,    address
2,           "Davies",  "Sarah",  2,       "other address"
1,           "Willis",  "John",   2,       "other address"
2,           "Davies",  "Sarah",  4,       "my other house address"
1,           "Willis",  "John",   4,       "my other house address"
4,           "Ede",     "Linda",  3,       "holliday address"
4,           "Ede",     "Linda",  5,       "Flat in the beach address"
3,           "Jones",   "Mark",   1,       "some address"

Is this even possible using a mysql query?, or a unique/better approach would be to make the example query, save it on an array and order it from there using php? How can i get it in both cases.

I appreciate any help or suggestions!

Answer 1

you could use mysql's FIELD()

SELECT id_customer, surname, name, id_dir, address
FROM CustomersAddress
INNER JOIN Customers ON Customers.id=CustomersAddress.id_customer
INNER JOIN Address ON Address.id_dir=CustomersAddress.id_address
ORDER BY FIELD(id_dir, 2,4,3,5,1), surname;

SQLFiddle example - http://sqlfiddle.com/#!2/3ea9c/18

this would require either a subquery or a prequery to get the order.