在MySQL的ORDER BY子句中使用Subquery

z31licg0 于 2023-06-28 发布在 Mysql

关注(0)|答案(2)|浏览(155)

我有几张table想拼在一起。
第一个脚本如下

SELECT 
    Project_Assignment.ID_Project,
     MIN(Project_Assignment.ID_Workstep) as orderme  // this field i removed for mysql field() function
FROM
    ProjectBoard.Project_Assignment
WHERE
    Project_Assignment.isDone = 0
GROUP BY Project_Assignment.ID_Project
ORDER BY MIN(Project_Assignment.ID_Workstep)

它又回来了

| ID_Project | orderme |
| 12         | 1       |
| 8          | 2       |
| 11         | 4       |
| 16         | 9       |

我的第二个问题是

SELECT 
    Workstep_Content.ID_Project,
    Workstep_Content.ID_Step,
    Workstep_Content.Content
FROM
    ProjectBoard.Workstep_Content

| ID_Project | ID_Step | Content    |
| 11         | 11      | one        |
| 11         | 12      |another one |
| 12         | 1       |  two       |
| 8          | 11      | this       |
| 16         | 1       | first      |
| 16         | 4       | second     |

现在我想按照第一个查询中ID_Project的顺序对第二个查询的结果进行排序。结果应该如下所示：

| ID_Project | ID_Step | Content    |
| 12         | 1       |  two       |
| 8          | 11      | this       |
| 11         | 11      | one        |
| 11         | 12      |another one |
| 16         | 1       | first      |
| 16         | 4       | second     |

我尝试在ORDER BY子句中使用MySQL FIELD函数，但它无法正常工作（ORDER BY FIELD(ID_Project,first_query)

mysql

来源：https://stackoverflow.com/questions/76567006/using-subquery-in-order-by-clause-in-mysql

2条答案

按热度按时间

py49o6xq1#

与子查询连接，然后使用ORDER by orderme

SELECT 
    w.ID_Project,
    w.ID_Step,
    w.Content
FROM
    ProjectBoard.Workstep_Content AS w
JOIN (
    SELECT 
        Project_Assignment.ID_Project,
         MIN(Project_Assignment.ID_Workstep) as orderme  // this field i removed for mysql field() function
    FROM
        ProjectBoard.Project_Assignment
    WHERE
        Project_Assignment.isDone = 0
    GROUP BY Project_Assignment.ID_Project
) AS o ON w.ID_Project = o.ID_Project
ORDER BY o.orderme

赞(0）回复(0）举报 2023-06-28

ymdaylpp2#

我会怎么做...

SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
INNER JOIN
(
    SELECT 
        ID_Project,
        MIN(ID_Workstep) as orderme
    FROM
        ProjectBoard.Project_Assignment
    WHERE
        isDone = 0
    GROUP BY
        ID_Project
)
    AS pa
        ON pa.ID_Project = wc.ID_Project
ORDER BY
    pa.orderme,
    wc.ID_Step

别人怎么做...

SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
INNER JOIN
    ProjectBoard.Project_Assignment AS pa
        ON pa.ID_Project = wc.ID_Project
WHERE
    pa.isDone = 0
GROUP BY
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
ORDER BY
    MIN(pa.ID_Workstep),
    wc.ID_Step

无政府主义者是如何做到的...

SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
ORDER BY
    (
        SELECT
            MIN(ID_Workstep)
        FROM
            ProjectBoard.Project_Assignment
        WHERE
                ID_Project = wc.ID_Project
            AND isDone     = 0
    ),
    wc.ID_Step

赞(0）回复(0）举报 2023-06-28

我来回答

在MySQL的ORDER BY子句中使用Subquery

2条答案

相关问题

热门标签

最新问答