mysql group by 语句中的where条件？

Question

我有以下查询。我只需要选择分组依据中最旧的行(按日期列)。保持我的条件(地点和分组依据)。

SELECT 
table.*
from table
where views > $views AND sales>23
group by 
name

更清楚一点，就像从第一个表传递一样

+--------------------+-----------+--------------------+-----------+
| name               | views     | DATE               |   sales   |
+--------------------+-----------+--------------------+-----------+
| sue1               |       494 | 2014-06-23 12:08:29|   26      |
| sue2               |       494 | 2014-06-25 12:08:29|   34      |
| sue3               |       494 | 2014-06-27 12:08:29|   45      |
| sue4               |       520 | 2014-06-26 12:08:29|   56      |
| sue5               |       570 | 2014-06-24 12:08:29|   20      |
| sue5               |       570 | 2014-06-24 12:08:29|   28      |
+--------------------+-----------+--------------------+-----------+

到第二个

+--------------------+-----------+--------------------+-----------+
|  name              | views     | DATE               |  sales    |
+--------------------+-----------+--------------------+-----------+
| sue3               |       494 | 2014-06-27 12:08:29|    45     |
| sue4               |       520 | 2014-06-26 12:08:29|    56     |
| sue5               |       570 | 2014-06-24 12:08:29|    28     |
+--------------------+-----------+--------------------+-----------+

marko.c

我尝试过这样进行子选择

SELECT 
table.*
from table
where views > $views AND sales>23
and date in (select max(date) from table where views > $views AND sales>23)
group by 
name

但它不起作用。有人吗？

Answer 1

您正在子查询中选择最大的总体日期，但您实际想要的是每个名称的最小(最旧)日期。

SELECT 
    t1.*
from table t1
join (
    select 
        t2.name,
        min(t2.date) min_date
    from table t2
    where t2.views > $views AND t2.sales > 23
    group by t2.name
) t2 on t2.name = t1.name and t2.min_date = t1.date
where t1.views > $views AND t1.sales > 23