mysql查询修改

Question

您好，我已经编写了此查询

SELECT cl_brands.name AS Brand,DATE_FORMAT(cl_doctor_call.date_entered,'%b%y')AS MonthYear,

SUM(FIND_IN_SET(CONCAT('^',cl_brands.id,'^'),cl_doctor_call_cstm.brand_discussed_c))AS No_Times_Brand_Discussed
FROM cl_doctor_call LEFT JOIN cl_doctor_call_cstm ON
cl_doctor_call.id=cl_doctor_call_cstm.id_c 
LEFT JOIN cl_brands ON
CONCAT('^',cl_brands.id,'^') LIKE CONCAT(cl_doctor_call_cstm.brand_discussed_c, '%' )
WHERE (cl_doctor_call.date_entered)
BETWEEN CURDATE()-INTERVAL 3 MONTH AND CURDATE()
GROUP BY cl_brands.name,MonthYear
ORDER BY No_Times_Brand_Discussed DESC limit 1

此查询为我们提供了讨论的最高品牌以及品牌名称和月份年份。

例如：

Brand   Monthyear    No_Times_Brand_Discussed
   x       Nov11              5

现在我想显示品牌在过去 2 个月内讨论的“同一品牌”详细信息（这意味着我的查询应该返回过去 2 个月内相同品牌详细信息的结果） 所以我的结果会是这样的。

 Brand   Monthyear    No_Times_Brand_Discussed

  x       Nov11              5
  x       Oct11              0
  x       Sep11              1

告诉我如何修改上面的查询，以便它给出这个结果。

Answer 1

如果我没看错的话，基本上，您只需要获取讨论最多的品牌按日期排序的前 3 个结果即可。因此，您只需以相同的方式查询原始表，但包含一个“in”子句，将您限制为讨论最多的品牌。我会使用 ID 而不是名称字段，这样您就知道它是唯一的。我假设字段名称是“brand_id”，但您的表中可能有所不同。

select  cl_brands.name AS Brand,DATE_FORMAT(cl_doctor_call.date_entered,'%b%y')AS MonthYear,
SUM(FIND_IN_SET(CONCAT('^',cl_brands.id,'^'),cl_doctor_call_cstm.brand_discussed_c))AS No_Times_Brand_Discussed
from cl_doctor_call LEFT JOIN cl_doctor_call_cstm ON
cl_doctor_call.id=cl_doctor_call_cstm.id_c 
LEFT JOIN cl_brands ON
CONCAT('^',cl_brands.id,'^') LIKE CONCAT(cl_doctor_call_cstm.brand_discussed_c, '%' )
where brand_id in 
(
   SELECT c1.brand_id
   FROM cl_doctor_call LEFT JOIN cl_doctor_call_cstm ON
   cl_doctor_call.id=cl_doctor_call_cstm.id_c 
   LEFT JOIN cl_brands ON
   CONCAT('^',cl_brands.id,'^') LIKE CONCAT(cl_doctor_call_cstm.brand_discussed_c, '%' )
   WHERE (cl_doctor_call.date_entered)
   BETWEEN CURDATE()-INTERVAL 3 MONTH AND CURDATE()
   GROUP BY cl_brands.name,MonthY`enter code here`ear
) 
order by date_entered
limit 3