如何在mysql中进一步过滤GROUP BY记录？ [英] How to further filter GROUP BY record in mysql?

问题描述

请检查图片我想查询id43（和另一个nic_id最大query_id行）的完整行，但它只给我最大query_id，我尝试了不同的查询例如：

 选择Query_id，nic_id，date，subject，followup_no，MAX（Query_id）作为queryid FROM`sales_queries` GROUP BY `nic_id` HAVING MAX（`Query_id`）
 
 SELECT`Query_id`，`nic_id`，`date`，`subject`，`followup_no`，MAX（Query_id）作为queryid FROM`sales_queries` WHERE GROUP BY`nic_id` HAVING MAX（`Query_id`）
 
 SELECT`Query_id`，`nic_id`，`date`，`subject`，`followup_no`，MAX（Query_id）as mxqueryid FROM`sales_queries `WHERE`Query_id` =（SELECT MAX（`Query_id`）FROM`sales_queries`）GROUP BY`nic_id` 
  

当客户访问再次跟进增加（+1）与他不同的主题相同的nic_id。

我想要完整的43行，包含主题Registration4，日期等。

不是，它应该是Registration4日期：2015-09-11

<解决方案类似于这个问题，您可以使用子查询并加入以获取您之后的结果。

pre $ SELECT`Query_id`，`nic_id`，`date`，`subject`，``
FROM`sales_queries` AS sq
INNER JOIN（
SELECT MAX（`Query_id`）AS mId
FROM`sales_queries`
GROUP BY`nic_id`
）AS subsq ON subsq.mId = sq.Query_id


Please check the images i want complete ROW of query-id "43"(and with another nic_id max query_id row) but it give me only max query_id, I tried different queries for-example:

SELECT `Query_id`, `nic_id`, `date`, `subject`, `followup_no`, MAX(Query_id) as queryid FROM `sales_queries` GROUP BY `nic_id` HAVING MAX(`Query_id`)

SELECT `Query_id`, `nic_id`, `date`, `subject`, `followup_no`, MAX(Query_id) as queryid FROM `sales_queries` WHERE  GROUP BY `nic_id` HAVING MAX(`Query_id`)

SELECT  `Query_id`, `nic_id`, `date`, `subject`, `followup_no`, MAX(Query_id) as mxqueryid    FROM `sales_queries`WHERE `Query_id` = (SELECT MAX(`Query_id`) FROM `sales_queries`) GROUP BY `nic_id` 

when customer visit again followup increase(+1) with his same nic_id with different subject.

i want complete 43 row with subject "Registration4", date etc

NOT THIS, it should be Registration4 date:2015-09-11

解决方案

Similarly to the answer to this question, you can use a subquery and join in order to fetch the results you are after. Something similar to the following should work:

SELECT `Query_id`, `nic_id`, `date`, `subject`, `followup_no`
FROM `sales_queries` AS sq
INNER JOIN (
    SELECT MAX(`Query_id`) AS mId
    FROM `sales_queries`
    GROUP BY `nic_id`
) AS subsq ON subsq.mId = sq.Query_id

这篇关于如何在mysql中进一步过滤GROUP BY记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！