合并子选择

发布于 2024-09-10 05:38:06 字数 548 浏览 5 评论 0原文

我必须将为 mysql5 编写的查询重写为 mysql4.0,该查询不支持子选择 我有一个这样的查询:

select a,
       c,
       (select count(*) from A) as e 
          from (select b, sum(e) as a 
                  from B 
                 where h = "foo" 
              group by b) TABLEB,
       (select sum(d),
               count(*) as c 
          from C 
         where d = "bar") TABLEC

我尝试在一个请求中合并 TABLEA 和 TABLE B,但 sum() 结果不正确( sum(e )值变为 sum(e) 乘以 TABLEC 的行数)

所有分组值变为实际值的倍数(取决于行数)。

是否可以将此查询转换为 mysql 4.0 的一个查询,或者我必须将其拆分为 3 个查询?

I have to rewrite a query written for mysql5 to mysql4.0 which doesn't support sub select I have a query like this :

select a,
       c,
       (select count(*) from A) as e 
          from (select b, sum(e) as a 
                  from B 
                 where h = "foo" 
              group by b) TABLEB,
       (select sum(d),
               count(*) as c 
          from C 
         where d = "bar") TABLEC

I try to merge TABLEA and TABLE B in one request but sum() results are not correct ( sum(e) values become sum(e) multiplied by the number of rows of TABLEC)

All the grouped values become a multiple of the real values (depending on rows number).

Is is possible to transform this query into only one query for mysql 4.0 or will I have to split it into 3 query ?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

豆芽 2024-09-17 05:38:06

您到底为什么要迁移到 MySQL 4.0?这是古老的历史,缓慢、有问题且不安全。如果您使用的托管服务仍然运行 MySQL 4.0,请切换到其他托管服务。

但无论如何,即使您确实使用 MySQL 5.0,我也看不出您为何将这些查询合并为一个查询。每个表的结果与其他表没有关系。

只需运行三个查询:

select b, sum(e) as esum from B where h = 'foo' group by b;

select sum(d) as dsum, count(*) as c from C where d = 'bar';

select count(*) as acount from A;

ps:在 SQL 中对字符串文字使用单引号。

Why in the world are you migrating to MySQL 4.0? It's ancient history, slow, buggy, and insecure. If you're using a hosting service that still runs MySQL 4.0, switch to a different hosting service.

But regardless of that, I see no reason why you combined these queries into one, even if you do use MySQL 5.0. The results from each table have no relationship to the others.

Just run three queries:

select b, sum(e) as esum from B where h = 'foo' group by b;

select sum(d) as dsum, count(*) as c from C where d = 'bar';

select count(*) as acount from A;

p.s.: Use single-quotes for string literals in SQL.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文