mysql - SQL减少内部查询的列数

Question

我有一个疑问:

select  
    count(*), paymentOptionId 
from 
    payments 
where 
    id in (select min(reportDate), id 
           from payments 
           where userId in (select distinct userId 
                            from payments 
                            where paymentOptionId in (46,47,48,49,50,51,52,53,54,55,56)) 
           group by userId)
group by 
    paymentOptionId;

问题所在是“select min(reportDate), id”，这个查询必须返回1列结果，但是当我需要对min进行分组时，我不知道如何做到这一点。

数据集看起来像

+----+--------+--------+-----------+---------------------+--------+----------+-----------------+
| id | userId | amount | userLevel | reportDate          | buffId | bankQuot | paymentOptionId |
+----+--------+--------+-----------+---------------------+--------+----------+-----------------+
|  9 |  12012 |      5 |         5 | 2014-02-10 23:07:57 |   NULL |     NULL |               2 |
| 10 |  12191 |      5 |         6 | 2014-02-10 23:52:12 |   NULL |     NULL |               2 |
| 11 |  12295 |      5 |         6 | 2014-02-11 00:12:04 |   NULL |     NULL |               2 |
| 12 |  12295 |      5 |         6 | 2014-02-11 00:12:42 |   NULL |     NULL |               2 |
| 13 |  12256 |      5 |         6 | 2014-02-11 00:26:25 |   NULL |     NULL |               2 |
| 14 |  12256 |      5 |         6 | 2014-02-11 00:26:35 |   NULL |     NULL |               2 |
| 16 |  12510 |      5 |         5 | 2014-02-11 00:42:58 |   NULL |     NULL |               2 |
| 17 |  12510 |      5 |         5 | 2014-02-11 00:43:08 |   NULL |     NULL |               2 |
| 18 |  12510 |     18 |         5 | 2014-02-11 00:45:16 |   NULL |     NULL |               3 |
| 19 |  12510 |      5 |         6 | 2014-02-11 01:00:10 |   NULL |     NULL |               2 |
+----+--------+--------+-----------+---------------------+--------+----------+-----------------+

Answer 1

select count(*), paymentOptionId
    from 
        (select userId, min(reportdate), paymentOptionId 
            from payments as t1 
            group by userId, paymentOptionId) as t2 
    group by paymentOptionId

Fiddle

它首先获取每个用户、每种类型的最短报告日期(因此第一个条目)(因此对于具有两种类型的用户有两条记录)，然后按类型(又名 paymentOptionId)对它们进行分组。

顺便说一句，您当然可以剪切 from 子句中 select 中选择的属性，它们只是在那里您可以复制粘贴并查看结果它是一步一步给予的。