我再问这个question https://stackoverflow.com/questions/10161153/sqlite-exists-keyword-how-to-query-the-highest-average以简化和扩展的方式。
考虑这些 sql 语句:
create table foo (id INT, score INT);
insert into foo values (106, 4);
insert into foo values (107, 3);
insert into foo values (106, 5);
insert into foo values (107, 5);
select T1.id, avg(T1.score) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
使用sqlite,select
语句返回:
id avg1
---------- ----------
106 4.5
107 4.0
mysql 返回:
+------+--------+
| id | avg1 |
+------+--------+
| 106 | 4.5000 |
+------+--------+
据我所知,mysql的结果是正确的,而sqlite的结果是不正确的。我尝试投射到real
使用 sqlite 如下所示,但它仍然返回两条记录:
select T1.id, cast(avg(cast(T1.score as real)) as real) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, cast(avg(cast(T2.score as real)) as real) avg2
from foo T2
group by T2.id
having avg2 > avg1);
为什么sqlite返回两条记录?
快速更新:
我针对最新的 sqlite 版本(3.7.11)运行了该语句,仍然得到两条记录。
另一个更新:
我发送了一封电子邮件至[电子邮件受保护] /cdn-cgi/l/email-protection关于这个问题。
我自己一直在使用 VDBE 并发现了一些有趣的东西。我分割了每个循环的执行跟踪not exists
(每个平均组一个)。
为了获得三个平均组,我使用了以下语句:
create table foo (id VARCHAR(1), score INT);
insert into foo values ('c', 1.5);
insert into foo values ('b', 5.0);
insert into foo values ('a', 4.0);
insert into foo values ('a', 5.0);
PRAGMA vdbe_listing = 1;
PRAGMA vdbe_trace=ON;
select avg(score) avg1
from foo
group by id
having not exists (
select avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
我们清楚地看到,不知何故应该是什么r:4.5
已经成为i:5
:
我现在正在尝试找出原因。
最终编辑:
所以我已经玩够了 sqlite 源代码。我现在对这头野兽有了更多的了解,尽管我会让原开发商 http://sqlite.org/support.html整理一下,因为他似乎已经在这样做了:
http://www.sqlite.org/src/info/430bb59d79 http://www.sqlite.org/src/info/430bb59d79
有趣的是,至少对我来说,似乎较新的版本(有时在我使用的版本之后)支持插入在上述提交中添加的测试用例中使用的多个记录:
CREATE TABLE t34(x,y);
INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);