MySQL - 如果重复或引用在另一列中不存在，则按一列排序 [英] MySQL - ORDER BY one column if duplicate or reference doesn't exist in another

问题描述

我有一个(简化)如下所示的表格:

I have a table that (simplified) looks like this:

+----+--------------+-----------+----------------+----------------+--------+---------+
| ID | First name   | Last name | Street Address | Postal Address | Country | Spouse |
+----+--------------+-----------+----------------+----------------+---------+--------+
| 1  | Nancy        | Fuller    | Street 1       | City 1         | USA     | 4      |
| 2  | Andrew       | Davolio   | Way 2          | Town 2         | USA     | 0      |
| 3  | Janet        | Leverling | Blvd 3         | Village 3      | USA     | 0      |
| 4  | Steven       | Buchanan  | Street 1       | City 1         | USA     | 1      |
| 5  | Anne         | Dodsworth | Street 1       | City 1         | USA     | 0      |
+----+--------------+-----------+----------------+----------------+---------+--------+

如您所见，Nancy 和 Steven 已婚(配偶列包含配偶的 ID，如果有)并住在一起.南希的妹妹安妮也和他们住在一起(不要费心评论不便之处).

As you can see, Nancy and Steven are married (the spouse column contains the ID of spouse, if any) and live together. Anne, Nancy's sister, also live with them (don't bother commenting the inconvenience).

现在我想打印这个数据库.我想按姓氏订购，但在打印页面上我有两个选择:

Now I want to print this database. I want to order by last name, BUT on the print page I have two options:

• 按地址分组
• 按配偶分组

所以有四种情况:

无分组
它们只是按姓氏排序:

No grouping
They are simply ordered by last name:

布坎南，史蒂文

达沃里奥，安德鲁

多兹沃斯，安妮

富勒，南希

莱弗林，珍妮特

按地址分组当到达多个地址相同的第一个人时，将全部打印出来(仍然按组中的姓氏排序):

Grouped by address When the first person of several with the same address is reached, all of them are printed (still sorted by last name in the group):

布坎南，史蒂文
Dodsworth，安妮
富勒，南希

达沃里奥，安德鲁

莱弗林，珍妮特

按配偶分组当到达一对夫妇中的第一个人时，两者都会打印:

Grouped by spouse When the first person in a couple is reached, both are printed:

布坎南，史蒂文
富勒，南希

多兹沃斯，安妮

达沃里奥，安德鲁

莱弗林，珍妮特

按地址分组和配偶综合以上，配偶优先(所以南希出现在安妮之前，因为她是史蒂夫的配偶).

Grouped by address and spouse Combination of the above, spouse is prioritized (so Nancy appears before Anne since she's Steve's spouse).

布坎南，史蒂文
富勒，南希
Dodsworth，安妮

达沃里奥，安德鲁

莱弗林，珍妮特

而且我真的希望将它们分组，如果可能的话，而不是一个一个依次排序.如上所示，我希望未分组的人之间有空间，分组的人之间没有空间.这是否可以仅使用 MySQL，或者我是否也必须使用 PHP?

And I really want them grouped, if that is possible, not just ordered after each other. As shown above I want space between not grouped persons, and no space between grouped persons. Is this possible using only MySQL, or do I have to use PHP as well?

(我使用的是 PHP 5.5.16 和 MySQL 5.5.39，PDO 对象)

推荐答案

在此查询中，我将 cast 语句放在 select 部分，以便您可以查看数据的布局方式.如果需要，您可以将它们迁移到 ORDER BY，并且只提取名字和姓氏.

in this query I put the cast statements in the select part so that you can see how the data is getting laid out. you can migrate them to the ORDER BY if you want and only pull out the firstname, lastname.

SELECT DISTINCT 
    CASE WHEN p1.id IS NOT NULL THEN 1 ELSE 0 END, 
    CASE WHEN p2.id IS NOT NULL THEN 1 ELSE 0 END,
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p1 ON p1.spouse = p.id
LEFT JOIN people p2 ON p2.streetaddress = p.streetaddress 
                   AND p.postaladdress = p2.postaladdress 
                   AND p2.id <> p.id
ORDER BY 1 DESC, 2 DESC, 3 ASC;

CASE:按地址排序

SELECT DISTINCT 
    CASE WHEN p2.id IS NOT NULL THEN 1 ELSE 0 END, 
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p2 
    ON p2.streetaddress = p.streetaddress 
   AND p.postaladdress = p2.postaladdress 
   AND p2.id <> p.id
ORDER BY 1 DESC, 2 ASC;

案例:配偶订购

SELECT DISTINCT 
    CASE WHEN p1.id IS NOT NULL THEN 1 ELSE 0 END, 
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p1 ON p1.spouse = p.id
ORDER BY 1 DESC, 2 ASC;

案例:无所事事

SELECT
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p;

小提琴演示

注意:如果您在排序时保持选择不变，那么您可以轻松地在应用程序层中添加空格……例如，如果它拉出一个 1，那么这意味着该订单对于配偶、地址等是正确的.如果它是 0 那么它是假的.所以你可以检查值，如果它是 0 开始添加空格

NOTE: if you keep the select as is with ordering then you can easily add spaces in the application layer... for instance if it pulls out a 1 then that means that the order is true for a spouse, address etc. if its a 0 then its false. so you can check for the value and if its a 0 start adding spaces

按名称升序进行排序，但在 MySQL 中无法使用配偶或相同地址的另一个名称覆盖它.如果有配偶，您可以使用 CASE 语句检查 php 并更改那里的顺序.

to do an order by name ascending but to override it with another name that is a spouse or same address is not possible in MySQL. you can use the CASE statement to check in php if there is a spouse or not and change the ordering there.

这篇关于MySQL - 如果重复或引用在另一列中不存在，则按一列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！