代码之家 › 专栏 › 技术社区 › Alex

按日期排序,而分组与另一列匹配

sql-order-by group-by sqlite sql

Alex · 技术社区 · 6 年前

我有这个问题

SELECT *, COUNT(app.id) AS totalApps FROM users JOIN app ON app.id = users.id
  GROUP BY app.id ORDER BY app.time DESC LIMIT ?

它应该从“用户”中获取由相关表中的另一列(时间)排序的所有结果(应用程序表中的ID引用了用户表中的ID)。

我的问题是分组是在按日期排序之前完成的,所以我得到了非常老的结果。但是我需要分组来获得不同的用户,因为每个用户可以有多个“应用程序”…有没有不同的方法来实现这一点?

表用户:

id TEXT PRIMARY KEY

表应用程序:

id TEXT
time DATETIME
FOREIGN KEY(id) REFERENCES users(id)

在我的select查询中,我想得到一个用户列表,按app.time列排序。但是因为一个用户可以关联多个应用程序记录,所以我可以得到重复的用户,这就是我使用group by的原因。但那订单就搞砸了

5 回复 | 直到 6 年前

MikeT 6 年前

基础问题是select是一个聚合查询,因为它包含一个group by子句:

有两种简单的select语句-aggregate和非聚合查询。简单的select语句是聚合查询如果它包含group by子句或一个或多个聚合结果集中的函数。

SQL As Understood By SQLite - SELECT

因此,该列对该组的值将是该组的列的任意值(我怀疑,首先根据扫描/搜索,因此是较低的值):-

如果select语句是没有group by的聚合查询子句,然后计算结果集中的每个聚合表达式一次跨越整个数据集。中的每个非聚合表达式对任意选定的数据集。每一行使用相同的任意选择行非聚合表达式。或者,如果数据集包含零行,则每个非聚合表达式都是根据包含完全是空值。

因此,简而言之,当列值是聚合查询时,不能依赖于不属于组/聚合的列值。

因此必须使用聚合表达式来检索所需的值,例如max(app.time)。但是,您不能按这个值排序(不确定为什么按这个值排序在效率方面可能是固有的)

然而

您可以使用查询构建CTE,然后在不涉及聚合的情况下进行排序。

考虑一下以下问题,我认为这类似于你的问题:

DROP TABLE IF EXISTS users;
DROP TABLE If EXISTS app;

CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username TEXT);
INSERT INTO users (username) VALUES ('a'),('b'),('c'),('d');

CREATE TABLE app (the_id INTEGER PRIMARY KEY, id INTEGER, appname TEXT, time TEXT);
INSERT INTO app (id,appname,time) VALUES
    (4,'app9',721),(4,'app10',7654),(4,'app11',11),
        (3,'app1',1000),(3,'app2',7),
        (2,'app3',10),(2,'app4',101),(2,'app5',1),
        (1,'app6',15),(1,'app7',7),(1,'app8',212),
        (4,'app9',721),(4,'app10',7654),(4,'app11',11),
        (3,'app1',1000),(3,'app2',7),
        (2,'app3',10),(2,'app4',101),(2,'app5',1),
        (1,'app6',15),(1,'app7',7),(1,'app8',212)
    ;
    SELECT * FROM users;
    SELECT * FROM app;

    SELECT username 
      ,count(app.id) 
      , max(app.time) AS latest_time
        , min(app.time) AS earliest_time
    FROM users JOIN app ON users.id = app.id 
    GROUP BY users.id
    ORDER BY max(app.time)
    ;

结果是:

其中,虽然每个组的最新时间已被提取,但最终结果没有按您的想法进行排序。

将其封装到CTE中可以修复该问题,例如:-

WITH cte1 AS 
(
    SELECT username 
        ,count(app.id) 
        , max(app.time) AS latest_time
        , min(app.time) AS earliest_time
    FROM users JOIN app ON users.id = app.id 
    GROUP BY users.id
)
SELECT * FROM cte1 ORDER BY cast(latest_time AS INTEGER) DESC;

现在:

注意,为了方便起见,使用了简单整数而不是实时整数。

Eugen Rieck 6 年前

因为你需要每个小组的最新日期,你可以 MAX 它们:

SELECT
  *,
  COUNT(app.id) AS totalApps,
  MAX(app.time) AS latestDate
FROM users
  JOIN app ON app.id = users.id
GROUP BY app.id
ORDER BY latestDate DESC
LIMIT ?

Lukasz Szozda 6 年前

你可以用窗户 COUNT :

SELECT *, COUNT(app.id) OVER(PARTITION BY app.id) AS totalApps 
FROM users 
JOIN app 
  ON app.id = users.id
ORDER BY app.time DESC
LIMIT ?

Julius 6 年前

也许你可以用?

SELECT DISTINCT

在这里阅读更多: https://www.w3schools.com/sql/sql_distinct.asp

Bhargav Rao rlgjr 6 年前

尝试按ID和时间分组,然后按时间排序。

select ... 
group by app.id desc, app.time

我假设ID在应用程序表中是唯一的。如何分配ID?也许你有足够的 按ID排序描述