首页 文章

在SQLite GROUP BY查询的SELECT列表中包含额外的列是否安全？

提问于
浏览 次
2

我有一个名为“message”的简单SQLite表：

sequence INTEGER PRIMARY KEY
type TEXT
content TEXT

我想获取每种类型的最后一条消息的内容（由其序列决定） . 令我惊讶的是，以下简单查询有效：

SELECT MAX(sequence), type, content
FROM message
GROUP BY type

很惊讶，因为我知道MSSQL或Postgres会拒绝在SELECT列表中包含一个不属于GROUP BY子句或聚合函数的列，我必须进行连接，如下所示：

SELECT m.sequence, m.type, m.content
FROM
(
    SELECT MAX(sequence) as sequence, type
    FROM message
    GROUP BY type
) g
JOIN message m
ON g.sequence = m.message_sequence

我的问题是：在SQLite中使用第一个更简单的查询形式是否安全？直觉上它选择与“MAX（序列）”值匹配的“内容”值是有道理的，但文档似乎根本没有谈到这一点 . 当然，如果序列不是唯一的，那么结果将是未定义的 . 但是，如果序列是唯一的，就像我的情况一样，这是保证还是仅仅是一个可以改变的幸运实现细节？

sql sqlite group-by specifications

3 回答

  • 0

    如果额外的列在功能上依赖于您分组的列，则可以使用这些查询"safely,"，即不会产生不明确的结果：

    SELECT c.parent_id, COUNT(*), p.any_column
FROM child_table c 
JOIN parent_table p USING (parent_id)
GROUP BY c.parent_id;

    上面的例子可以在SQLite中使用，并产生一个明确的结果，因为 p.any_column 无法为每个组提供多个值 . 但是，此查询严格违反SQL标准，大多数品牌的RDBMS都会引发错误 .

    但是，编写一个产生模糊结果的查询太容易了 . 为每个组命名一个具有多个值的列时，无法控制结果集中返回的值 .

    在实践中，MySQL返回第一行中相对于物理存储的值，SQLite返回最后一行的值 . 但它最好不要依赖这种行为 .

    关于你的例子， content 应该"intuitively"具有 sequence 为MAX的行的值 . 但这真的很直观吗？考虑以下其他情况：

    SELECT MAX(sequence), MIN(sequence), type, content
FROM message
GROUP BY type

    那么哪一行现在提供 content 的值？ sequence 为MAX的行，或 sequence 为MIN的行？

    如果您使用非唯一列（例如 date ），并且 date 有多个具有相同MAX值的行，但 content 的值不同，该怎么办？

    SELECT MAX(date), type, content
FROM message
GROUP BY type

    那么像 AVG() 或 SUM() 等其他聚合函数呢？可能是聚合的值对应于表中没有单独的行 . 那么现在哪一行应该为 content 提供值？

    SELECT AVG(sequence), type, content
FROM message
GROUP BY type
    回复于
  • 6

    我不知道任何“直观地”解决此类问题的数据库，您希望根据特定列的聚合结果获取组的相关行值 . 对于SQLite，我认为你最好坚持你的第二个查询 .

    既然你提到了PostgreSQL，值得注意的是它支持一些以DISTINCT ON形式实现的非标准语法：

    select distinct on (type) sequence, type, content
from message
order by sequence desc

    （可能会有一些错误，因为我面前没有psql提示符，但这是它的要点 . ）

    见http://www.postgresql.org/docs/8.4/interactive/queries-select-lists.html

    回复于
  • 1

    我敢打赌它只是对序列字段使用随机值 . 例如，MySQL文档明确说明了这一点 .

    回复于

相关问题