mysql – 多个内连接为计数添加了额外的值

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 多个内连接为计数添加了额外的值，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2548字，纯文字阅读大概需要4分钟。

内容图文

我试图在下面使用多个连接的previous查询中包含其他表的结果：

SELECT mid                             as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT t.mid,
         SUM( mt = 'qt' )   as qty_count,
         SUM(CASE WHEN t_r.mt = 'qt' THEN rt END)  as qty_sum,
         STD(CASE WHEN t_r.mt = 'qt' THEN rt END)  as qty_stddev,
         SUM( t_r.mt = 'rel' ) as rel_count,
         SUM(CASE WHEN t_r.mt = 'rel' THEN rel END) as rel_sum,
         STD(CASE WHEN t_r.mt = 'rel' THEN rel END) as rel_stddev
  FROM t_r r
right join t_m t on t.mid = r.mid
right join m_k m on m.mid = t.mid
right join k_d k on m.kid = k.kid
  GROUP BY t.mid
 ) x;

使用上面的查询,当mt是qt时,qty_count为111,返回6而不是2. 2 *(表m_k中的111的计数)

当我删除这部分联接时,我得到了qtCOUNT和relCOUNT的所需总和

right join m_k m on m.mid = t.mid
right join k_d k on m.kid = k.kid

我做错了什么,我该如何解决？

数据：

m_k

mid  kid
--------
109  2
110  2
110  4
111  1
111  2
111  3

K_D

kid  k_desc
-----------
1    desc1
2    desc2
3    desc3
4    desc4

M_D

mid  col1   col2   col3   col4
-------------------------------
109  val_a  val_d  val_g  val_j
110  val_b  val_e  val_h  val_k
111  val_c  val_f  val_i  val_l

T_R

mid  rt  stamp                  mt
----------------------------------
111  3   2018-12-08 01:30:31   rel
111  4   2018-12-08 03:41:56   qt
111  3   2018-12-08 02:29:10   qt
110  1   2018-12-08 06:13:51   rel
110  5   2018-12-08 11:44:39   qt
109  1   2018-12-08 10:39:51   rel

其他与上述相同的查询都可以.

解决方法:

我通过移动解决了这个问题

right join m_k m on m.mid = t.mid
right join k_d k on m.kid = k.kid

在派生表x之外.最终查询如下所示：

SELECT mid                             as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT mid,
         SUM( mt = 'qt' )   as qty_count,
         SUM(CASE WHEN mt = 'qt' THEN rt END)  as qty_sum,
         STD(CASE WHEN mt = 'qt' THEN rt END)  as qty_stddev,
         SUM( mt = 'rel' ) as rel_count,
         SUM(CASE WHEN mt = 'rel' THEN rel END) as rel_sum,
         STD(CASE WHEN mt = 'rel' THEN rel END) as rel_stddev
  FROM t_r r
right join t_m t on t.mid = r.mid
  GROUP BY mid
 ) x
right join m_k m on m.mid = x.mid
right join k_d k on k.kid = m.kid
group by m.mid;

内容总结

以上是互联网集市为您收集整理的mysql – 多个内连接为计数添加了额外的值全部内容，希望文章能够帮你解决mysql – 多个内连接为计数添加了额外的值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端