duanhuang3074
2013-07-16 17:34 浏览 81
已采纳

MYSQL有两个ORDER BY或嵌套顺序

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!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答 默认 最新

  • 已采纳
    dongyishen5796 dongyishen5796 2013-07-16 19:01

    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.

    点赞 评论 复制链接分享
  • dongyi9298 dongyi9298 2013-07-16 17:53

    Try order by with desired order: ORDER BY table_one.field_A ASC table_two.field_E DESC

    点赞 评论 复制链接分享
  • doulue7522 doulue7522 2013-07-16 18:13

    You would simply order by address first:

    ORDER BY id_dir, surname
    
    点赞 评论 复制链接分享

相关推荐