假设我有 2 张桌子:
课程
| id | name | is_sort_by_borrower |
|----|--------------|---------------------|
| 1 | Comp Science | 1 |
| 2 | Biz Admin | 0 |
| 3 | Physics | 1 |
图书
| id | title | borrower | last_borrow_date | course_id |
| --- | ------ | -------- | ---------------- | --------- |
| 1 | Book A | 523 | 11/12/19 12:33 | 1 |
| 2 | Book B | 423 | 11/15/19 12:33 | 1 |
| 3 | Book C | 401 | 10/22/19 12:33 | 1 |
有一个列is_sort_by_borrower
在课程,我想这样的结果
| course | book_title | borrower | last_borrow_date | is_sort_by_borrower |
|--------------|------------|----------|------------------|---------------------|
| Comp Science | Book A | 523 | 11/12/19 12:33 | 1 |
| Comp Science | Book B | 423 | 11/15/19 12:33 | 1 |
| Comp Science | Book C | 401 | 10/22/19 12:33 | 1 |
| Comp Science | Book D | 377 | 11/19/19 12:33 | 1 |
| Biz Admin | Book E | 122 | 11/20/19 12:33 | 0 |
| Biz Admin | Book F | 419 | 11/12/19 12:33 | 0 |
| Biz Admin | Book G | 65 | 10/18/19 12:33 | 0 |
| Physics | Book H | 446 | 8/31/19 12:33 | 1 |
| Physics | Book I | 398 | 11/2/19 12:33 | 1 |
| Physics | Book J | 376 | 9/30/19 12:33 | 1 |
| Physics | Book K | 249 | 10/11/19 12:33 | 1 |
是否可以将结果排序为 some by borrower
, some by last_borrow_date
?
是的,在 SELECT 中创建另一列并使用 CASE 用您想要的值填充它,然后按它排序。您还可以在 WHERE 子句中使用 CASE 语句,具体取决于您是否希望排序列显示结果。
参考:https : //dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
在你的例子中:
SELECT
c.id, c.name, c.is_sort_by_borrower,
b.id, b.title, b.borrower, b.last_borrow_date
FROM
courses c
INNER JOIN
books b on b.course_id = c.id
ORDER BY
CASE
WHEN is_sort_by_borrower=1 THEN borrower
WHEN is_sort_by_borrower=0 THEN last_borrow_date
END ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句