我正在尝试在下面的表上编写一个sql查询。
╔════╦══════════╦═══════╗======╗======╗
║ ID ║ NAME ║ CLASS ║PARENT║ DOB ║
╠════╬══════════╬═══════╣======║======║
║ 1 ║ DAVID ║ SPIN ║ ║1 ║
║ 2 ║ AROON ║ BIKE ║ 1 ║1 ║
║ 3 ║ LEO ║ YOGA ║ ║2 ║
║ 4 ║ LIN ║ CYC ║ 1 ║2 ║
║ 5 ║ STEFA ║ YOGA ║ ║3 ║
║ 6 ║ GLORIA ║ RUNN ║ 1 ║3 ║
╚════╩══════════╩═══════╝======╝======╝
并且,此表的输出应如下所示
╔════╦════════╦═══════╗======╗======╗
║ ID ║ NAME ║ CLASS ║PARENT║ DOB ║
╠════╬════════╬═══════╣======║======║
║ 1 ║ DAVID ║ SPIN ║ ║1 ║
║ 2 ║ AROON ║ BIKE ║ 1 ║1 ║
║ 4 ║ LIN ║ CYC ║ 1 ║2 ║
║ 6 ║ GLORIA║ RUNN ║ 1 ║3 ║
║ 3 ║ LEO ║ YOGA ║ ║2 ║
║ 5 ║ STEFAN║ YOGA ║ ║3 ║
╚════╩════════╩═══════╝======╝======╝
So this is the explanation of the output
First parent David as his DOB is 1,
--David three childrens sorted based on DOB
Then LEO as his DOB is 2
-- Leo do not have children[if he did, would be here as sorted on DOB]
Then Stefan as his DOB is 3
-- Stefan do not have children [if he did, would be here as sorted on DOB]
那么我试过什么?
SELECT * FROM user group by ID, PARENT ;
在SQL上面,语句返回父子组中的项目,但不保持任何顺序,当我添加ORDER BY
时,SQL
似乎不再尊重GROUP BY。
然后我尝试连接并以两个完全不同的表结束,其中一个包含所有父项,另一个包含所有子项。 UNION ALL
,两个查询返回了预期的数据集,但不按预期的顺序返回。
有什么想法吗?
更新
Output should be
Pick entry [based on min time ].
--use that id and find all of its children and placed them in sorted order
repeat for every row in the table
注意:
--parents are sorted based on DOB
--child's are also sorted based on DOB
--DOB are valid timestamp
--PARENT, ID field both are UUID and define as CHAR, PARENT reference to ID
SQL 小提琴
在 SO 上类似
更新 1
查询波纹管
WITH RECURSIVE
top AS (
SELECT * FROM (SELECT * FROM user WHERE PARENT is null ORDER BY dob LIMIT 1)
UNION
SELECT user.NAME, user.PARENT, user.ID, user.CLASS, user.DOB FROM user, top WHERE user.PARENT=top.ID
ORDER BY user.dob
) SELECT * FROM top;
返回以下输出:
╔════╦════════╦═══════╗======╗======╗
║ ID ║ NAME ║ CLASS ║PARENT║ DOB ║
╠════╬════════╬═══════╣======║======║
║ 1 ║ DAVID ║ SPIN ║ ║1 ║
║ 2 ║ AROON ║ BIKE ║ 1 ║1 ║
║ 4 ║ LIN ║ CYC ║ 1 ║2 ║
║ 5 ║ GLORIA║ RUNN ║ 1 ║3 ║
╚════╩════════╩═══════╝======╝======╝
输出对第一亲本有利。但是,仍然无法弄清楚,我如何按排序顺序遍历其余的父母和他们的孩子。
查询
SELECT u1.*
FROM `user` u1
LEFT JOIN `user` u2
ON u1.PARENT = u2.ID
ORDER BY CASE WHEN u1.PARENT IS NULL THEN u1.DOB ELSE u2.DOB END
|| CASE WHEN u1.PARENT IS NULL THEN '' ELSE u1.DOB END;
解释
- 别名
u1
包含所有用户详细信息 - 别名
u2
包含父级的详细信息(如果适用)。(使用LEFT JOIN
,以便在u1
用户没有父用户时,这些详细信息都将null
。 - 如果用户没有父级,请单独使用其 DOB 进行排序。
- 如果用户有父项,则获取用户的父项的 DOB 并连接(追加)用户(子项)的 DOB。
结果
用于ORDER BY
的构造值(在SELECT
中实际上并不需要)看起来像这里最右边的列:
╔════╦════════╦═══════╗======╗======╦════════╗
║ ID ║ NAME ║ CLASS ║PARENT║ DOB ║ORDER BY║
╠════╬════════╬═══════╣======║======╬════════║
║ 1 ║ DAVID ║ SPIN ║ ║1 ║ 1 ║
║ 2 ║ AROON ║ BIKE ║ 1 ║1 ║ 11 ║
║ 4 ║ LIN ║ CYC ║ 1 ║2 ║ 12 ║
║ 6 ║ GLORIA║ RUNN ║ 1 ║3 ║ 13 ║
║ 3 ║ LEO ║ YOGA ║ ║2 ║ 2 ║
║ 5 ║ STEFAN║ YOGA ║ ║3 ║ 3 ║
╚════╩════════╩═══════╝======╝======╩════════╝
演示
请参阅 SQL 小提琴演示。
这是一个我认为在逻辑上正确的ORDER BY
:
ORDER BY COALESCE(PARENT, DOB) ASC,
CASE WHEN PARENT IS NULL THEN 0 ELSE DOB END
当然,此答案假设您实际上可以在查询中使用PARENT
和DOB
列。 通常不应SELECT
不是聚合或在 GROUP BY
子句中指定的列。
如果PARENT
和DOB
定义为varchar
,则可以尝试将它们转换为数值类型:
CAST(PARENT as integer)
您可能希望更改表设计,以便这些 UUID 是数字类型。