SQLite查询在3.4.2版中成功运行，但在3.7.11版中出现错误--使用聚合函数
SQLite查询在3.4.2版中成功运行，但在3.7.11版中出现错误--使用聚合函数
提问于 2012-07-30 13:41:33
我有一个疑问：
SELECT MIN(stat_md5) AS stat_md5
FROM
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host ))
GROUP BY stat_md5;此查询在SQLite版本3.4.2中运行良好，但当我升级到版本3.7.11时，它将出现以下错误：
SQL error: aggregate functions are not allowed in the GROUP BY clause如果我从查询中删除了MIN()、GROUP BY或AS，那么它在3.7.11版本中可以正常工作。
这意味着以下查询变体运行良好：
SELECT MIN(stat_md5) AS stat_md5
FROM
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host ))
SELECT MIN(stat_md5)
FROM
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host ))
GROUP BY stat_md5;
SELECT stat_md5
FROM
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host ))
GROUP BY stat_md5;请有人解释一下它为什么会在3.7.11版本中出现错误并解决这个问题。
UPDTATE:
这是最初的查询。
SELECT
stat_day, obs_day, stat_week, obs_week,
NULL AS stat_cputotal, stat_memfree, stat_md5,
stat_month, obs_month, stat_type, stat_memused, stat_10min, obs_10min,
NULL AS stat_cpusystem, stat_epoch, obs_epoch, stat_30day, obs_30day,
stat_idx, obs_idx, NULL AS stat_cpuidle, stat_memtotal, stat_qtr, obs_qtr,
NULL AS stat_cpuuser, stat_hour, obs_hour, obs_10min, stat_10min,
obs_30day,stat_30day, obs_day, stat_day, obs_epoch, stat_epoch, obs_hour,
stat_hour, obs_idx, stat_idx, obs_month, stat_month, obs_qtr, stat_qtr,
obs_week, stat_week, serial_num, NULL AS dputil_cpuid, NULL AS wms_pk
FROM (
(
SELECT stat_qtr, obs_qtr, stat_qtr AS cus_qtr, stat_qtr AS crash_qtr,
stat_qtr AS evt_qtr, stat_30day, obs_30day, stat_30day AS cus_30day,
stat_30day AS crash_30day, stat_30day AS evt_30day, stat_month,
obs_month, stat_month AS cus_month, stat_month AS crash_month,
stat_month AS evt_month, stat_week, obs_week, stat_week AS cus_week,
stat_week AS crash_week, stat_week AS evt_week, stat_day, obs_day,
stat_day AS cus_day, stat_day AS crash_day, stat_day AS evt_day,
stat_hour, obs_hour, stat_hour AS cus_hour, stat_hour AS crash_hour,
stat_hour AS evt_hour, stat_10min, obs_10min, stat_10min AS cus_10min,
stat_10min AS crash_10min, stat_10min AS evt_10min, stat_epoch,
obs_epoch, stat_epoch AS cus_epoch, stat_epoch AS crash_epoch,
stat_epoch AS evt_epoch, stat_idx, obs_idx, stat_idx AS cus_idx,
stat_idx AS crash_idx, stat_idx AS evt_idx, obs_10min, obs_30day,
obs_day, obs_epoch, obs_hour, obs_idx, obs_month, obs_qtr, obs_week,
serial_num, stat_md5, MIN(stat_memfree) AS stat_memfree, MIN(stat_md5)
AS stat_md5, MIN(stat_type) AS stat_type, MIN(stat_memused) AS
stat_memused, MIN(stat_memtotal) AS stat_memtotal
FROM
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host ))
GROUP BY
stat_qtr, stat_30day, stat_month, stat_week,
stat_day, stat_hour, stat_10min, stat_epoch,
stat_idx, obs_10min,obs_30day, obs_day, obs_epoch,
obs_hour, obs_idx, obs_month, obs_qtr, obs_week,
serial_num, stat_md5
))回答 2
Stack Overflow用户
回答已采纳
发布于 2012-07-30 14:37:57
，stat_md5，MIN(stat_memfree)为stat_memfree，MIN(stat_md5)为stat_md5，
stat_md5在选择列表上有两次:一次是单独的，一次是MIN()的别名。3.4.2必须使用第一个，3.7.11必须使用第二个。
按聚合字段分组是没有意义的，因此错误是正确的。
只需将第二次的别名更改为类似于AS min_stat_md5的名称，它就会正常工作。在SELECT上有两个名称相同的字段在任何情况下都是个坏主意。
Stack Overflow用户
发布于 2012-07-30 18:35:14
在我看来，这个内联视图中的括号很时髦：
(select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) 此外，为了查询的清晰性和可读性，最好为内联视图指定别名：
select Books4Kids.Price
from
(
select authors.name, titles.price
from authors inner join titles on authors.authorid = titles.authorid
where titles.category = 'Childrens Literature'
) as Books4Kids页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11722789
复制相关文章
相似问题
腾讯云开发者
