SQLite 条件 ORDER BY 中的 DESC

发布于 2024-11-04 07:40:37 字数 243 浏览 0 评论 0原文

我需要选择按以下逻辑排序的记录，但是当 DESC 处于条件中时，SQLite 会引发错误。

ORDER BY
CASE 
  WHEN parentGUID IS NULL THEN datePosted DESC
  ELSE datePosted
END

这是为了实现类似于 Facebook 的排序 =- 原始帖子（始终具有空的parentGUID）按日期降序排列，并回复按日期升序排列的原始帖子。

原文

I need to select records ordered by the following logic, however SQLite raises an error when DESC is in the conditional.

ORDER BY
CASE 
  WHEN parentGUID IS NULL THEN datePosted DESC
  ELSE datePosted
END

This is to achieve Facebook like ordering =- original posts (which always have null parentGUIDs) in order descending by date and replies to original posts ordered by date ascending.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

亽野灬性zι浪 2024-11-11 07:40:37

如果我理解正确，您需要加入到包含父帖子日期的表中。如果可用，应该这样做：

DECLARE @X TABLE
(
ID  INT NOT NULL IDENTITY PRIMARY KEY,
parentID INT,
datePosted DATE NOT NULL
)
INSERT INTO @X (parentID, datePosted) VALUES
    (NULL, '2010-01-01'),
    (NULL, '2010-01-02'),
    (1,    '2010-01-03'),
    (1,    '2010-01-04'),
    (1,    '2010-01-05'),
    (2,    '2010-01-06')

SELECT
    Post.parentID, Post.datePosted
FROM @X AS Post
    LEFT JOIN @X AS Parent ON Post.parentID = Parent.ID
ORDER BY
    -- Order by post date, or rather the parent's post date if one exists
    COALESCE(Parent.datePosted, Post.datePosted)
    -- Order by reply date
    Post.datePosted

这给出了这样的输出：

parentID datePosted
-------- ----------
NULL     2010-01-02
2        2010-01-06
NULL     2010-01-01
1        2010-01-03
1        2010-01-04
1        2010-01-05

请注意，如果回复可以轮流回复，这将中断；你需要更强大的东西。在 MS SQL 中，我会使用 CTE，但我对 Sqlite 不太熟悉。

If I understand you right, you'll need to join to the table that has the date of the parent post. If that's available, something like this should do:

DECLARE @X TABLE
(
ID  INT NOT NULL IDENTITY PRIMARY KEY,
parentID INT,
datePosted DATE NOT NULL
)
INSERT INTO @X (parentID, datePosted) VALUES
    (NULL, '2010-01-01'),
    (NULL, '2010-01-02'),
    (1,    '2010-01-03'),
    (1,    '2010-01-04'),
    (1,    '2010-01-05'),
    (2,    '2010-01-06')

SELECT
    Post.parentID, Post.datePosted
FROM @X AS Post
    LEFT JOIN @X AS Parent ON Post.parentID = Parent.ID
ORDER BY
    -- Order by post date, or rather the parent's post date if one exists
    COALESCE(Parent.datePosted, Post.datePosted)
    -- Order by reply date
    Post.datePosted

This gives this output:

parentID datePosted
-------- ----------
NULL     2010-01-02
2        2010-01-06
NULL     2010-01-01
1        2010-01-03
1        2010-01-04
1        2010-01-05

Note that this will break if replies can have replies in turn; you'd need something more robust. In MS SQL, I'd use a CTE, but I'm not very familiar with Sqlite.

回复收藏 0 原文

天暗了我发光 2024-11-11 07:40:37

经过你的纠正，我想我现在明白了。这是一些示例数据

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|2   |null      |2010-01-02|
|3   |1         |2010-01-03|
|4   |1         |2010-01-04|
|5   |1         |2010-01-05|
|6   |2         |2010-01-06|

，我猜您想要这个输出：

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|5   |1         |2010-01-05|
|4   |1         |2010-01-04|
|3   |1         |2010-01-03|
|2   |null      |2010-01-02|
|6   |2         |2010-01-06|

在单个查询中订购这非常困难（而且可能很慢）

With your correction, I think I now understand. Here's some sample data

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|2   |null      |2010-01-02|
|3   |1         |2010-01-03|
|4   |1         |2010-01-04|
|5   |1         |2010-01-05|
|6   |2         |2010-01-06|

And I'm guessing you want this output:

|GUID|parentGUID|datePosted|
+----+----------+----------+
|1   |null      |2010-01-01|
|5   |1         |2010-01-05|
|4   |1         |2010-01-04|
|3   |1         |2010-01-03|
|2   |null      |2010-01-02|
|6   |2         |2010-01-06|

That's quite hard (and probably slow) to order in a single query

回复收藏 0 原文

~没有更多了~