mysql - 过滤重复的MySQL

我不确定如何最好地解释我的问题。
我有一张与此类似的桌子

+---------+------------+---------------+--+
| user_id | invoice_no |  invoiced_hrs |  |
+---------+------------+---------------+--+
|    1    |    1023    |    8.1234     |  |
|    2    |    1023    |    1.1234     |  |
|    1    |    1023    |    5.8765     |  |
|    4    |    1085    |    6.7654     |  |
|    4    |    1085    |    7.5435     |  |
|    6    |    1022    |    5.6789     |  |
+---------+------------+---------------+--+

我需要能够过滤查询，而不必将多个user_id与任何invoice_no关联。只要只有一个user_id与它关联，重复的发票号就可以。

我正在尝试做的是仅保留发票上大部分时间的用户行。

最佳答案

尝试这个：

select
    t.*
from your_table t
inner join (
    select
        invoice_no,
        user_id,
        sum(invoiced_hrs) invoiced_hrs
    from your_table
    group by invoice_no, user_id
) t1 on t.invoice_no = t1.invoice_no and t.user_id = t1.user_id
inner join (
    select
        invoice_no,
        max(invoiced_hrs) invoiced_hrs
    from (
        select
            invoice_no,
            user_id,
            sum(invoiced_hrs) invoiced_hrs
        from your_table
        group by invoice_no, user_id
    ) t group by invoice_no
) t2 on t1.invoice_no = t2.invoice_no and t1.invoiced_hrs = t2.invoiced_hrs;

产生：

+---------+------------+--------------+
| user_id | invoice_no | invoiced_hrs |
+---------+------------+--------------+
|       6 |       1022 |       5.6789 |
|       1 |       1023 |       8.1234 |
|       1 |       1023 |       5.8765 |
|       4 |       1085 |       6.7654 |
|       4 |       1085 |       7.5435 |
+---------+------------+--------------+
5 rows in set (0.04 sec)

它获得invoice_no的user_id，该ID在总invoiced_hrs中占最大比例。

请注意，如果他们做出了相等的贡献，它将返回两个用户。

关于mysql - 过滤重复的MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41858564/