MySql 使用相同的查询随机检索不同的值？

Question

此查询有时会在某些行中随机给我空值，有时却不会。当我将内部联接更改为左联接时，这一切就开始发生了。

CREATE temporary TABLE IF NOT EXISTS surveys_temp AS 
  (SELECT SN.id_rep, 
          Coalesce(( Sum(CASE 
                           WHEN SN.score < 7 THEN -100 
                           WHEN SN.score >= 7 
                                AND score < 9 THEN 0 
                           WHEN SN.score >= 9 THEN 100 
                         end) / Count(score) ), 0) AS NRS, 
          SW.wtr 
   FROM   surveys SN 
          INNER JOIN (SELECT id_rep, 
                             Coalesce(( Sum(CASE 
                                              WHEN score < 7 THEN -100 
                                              WHEN score >= 7 
                                                   AND score < 9 THEN 0 
                                              WHEN score >= 9 THEN 100 
                                            end) / Count(score) ), 0) AS WTR 
                      FROM   surveys 
                      WHERE  survey_type = 'WTR' 
                      GROUP  BY id_rep) SW 
                  ON SW.id_rep = SN.id_rep 
   WHERE  SN.survey_type = 'NRS' 
   GROUP  BY SN.id_rep); 

CREATE temporary TABLE IF NOT EXISTS orders_temp AS 
  (SELECT id_rep, 
          Sum(Cast(ordernumber AS DECIMAL(2, 0))) AS Orders 
   FROM   orders 
   GROUP  BY id_rep); 

CREATE temporary TABLE IF NOT EXISTS chats_temp AS 
  (SELECT id_rep, 
          Time_format(Sec_to_time(Cast(Cast(Sum(response_time * -1)/ Count( 
                                  id_session) AS 
                                  DECIMAL(5, 2 
                                              )) AS CHAR(6 
                                  ))), '%H : %i : %s')AS response_time 
   FROM   chats 
   WHERE  chat_type = 1 
   GROUP  BY id_rep 
   ORDER  BY id_rep); 

SELECT R.rep_name, 
       Count(DISTINCT R.id_session)                AS Chats, 
       O.orders, 
       Concat(Cast((o.orders/Count(DISTINCT r.id_session)) * 100 AS DECIMAL(5, 2 
              )), '%' 
       )                                           AS CONVERSION, 
       Coalesce(Cast(s.nrs AS DECIMAL(5, 2)), '0') AS NRS, 
       Coalesce(Cast(s.wtr AS DECIMAL(5, 2)), '0') AS WTR, 
       C.response_time 
FROM   reps R 
       LEFT JOIN surveys_temp AS S 
              ON S.id_rep = R.id_rep 
       LEFT JOIN orders_temp AS O 
              ON O.id_rep = R.id_rep 
       LEFT JOIN chats_temp AS C 
              ON c.id_rep = R.id_rep 
WHERE  R.rep_country IN( 'D.R', 'U.S' ) 
GROUP  BY R.rep_name 
ORDER  BY R.rep_name; 

Answer 1

这里的一个问题是您违反了 Single Value Rule因为您仅按 R.Rep_Name 分组，但选择其他字段而不通过聚合函数引入它们，例如O.Orders 和 C.response_time 列(以及通过 Coalesce 和 Concat 派生的列)

由于这种违规行为，很可能并非所有非分组、非聚合列的值在每个 Rep_Name 组中都具有相同的值，并且结果是不确定的。 More here

编辑(来自下面的评论)

data looks a little better, but some rep_names are repeating

这似乎证实了非聚合列不是唯一的。所以:

• 列无法在 rep_name 组的上下文中显示，
• 或者你有比你想象的更多的离散群体
• 或者您需要使用 Count(O.Orders)、AVG(C.response_time) 等聚合来限定非不同列，然后更改列标题(例如“TotalOrders”、“AverageResponseTime”等)