mysql - 多个内部联接添加额外的值来计数

Question

我正在尝试将其他表的结果包含在 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

实现与上述相同的其他查询是可以的。

Answer 1

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

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;