mysql查询修改
mysql查询修改
提问于 2012-01-31 16:31:46
嗨，我写了这个查询
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告诉我怎样才能修改上面的查询，让它给我这个结果。
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-01-31 21:03:50
如果我没看错的话，本质上，你只需要得到讨论最多的品牌按日期排序的前3个结果。因此，您只需以相同的方式查询您的原始表，但包含一个"in“子句，该子句将您限制为讨论最多的品牌。我使用ID而不是Name字段，这样您就知道它是唯一的。我假设字段名为"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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9076524
复制相关文章
相似问题
腾讯云开发者
