mysql - 多种条件下的独特值(value)

Question

我请求根据几种条件提取唯一数据，但现在陷入困境。谁能建议如何解决这个问题？

表格将如下所示:

Date      |ID | TicketNumber  | Category   | Subcategory|TCount1| TCount2|TCount3

01.04.2017| 1 | 11111         | OldMachine | DDD        | 1     |  2     | 3
01.04.2017| 1 | 13451         | OldMachine | DDD        | 1     |  2     | 3
01.04.2017| 1 | 14343         | OldMachine | DDD        | 1     |  2     | 3
01.04.2017| 2 | 22222         | New Machine| FFD        | 2     |  5     | 8
02.04.2017| 2 | 22222         | New Machine| FFD        | 9     |  7     | 9 

我应该得到:

For 01.04.2017 for ID=1

01.04.2017| 1 | 11111         | OldMachine | DDD        | 1     |  2     | 3
01.04.2017| 1 | 13451         | OldMachine | DDD        | 1     |  2     | 3
01.04.2017| 1 | 14343         | OldMachine | DDD        | 1     |  2     | 3

<小时/>

For 02.04.2017 for ID=2

02.04.2017| 2 | 22222         | New Machine| FFD        | 9     |  7     | 9 

我需要计算每个 ID 的总 TCounts，但是:

1. 如果有一个 TicketNumber 重复，我需要获取最后一个并仅计算该编号。
2. 每个 ID 包含某个日期的所有 TicketNumber 的总和。
3. 某些 ID 的类别和子类别可能会在一天内发生变化。

有人有什么想法吗？

谢谢

我已经尝试过:

select t1.date, t1.id, t1.ticketnumber
from schema.table t1
inner join 
(select max(schema.table.date) as maxdate, schema.table.id,schema.table.ticketnumber,          
 from schema.table 
 GROUP BY schema.table.id,schema.table.ticketnumber) t2
 on t2.id = t1.id 
 and t2.maxdate = t1.date;

但这会返回包含所有票号的所有 ID(不是唯一的)。

Answer 1

如果有一个 TicketNumber 重复，我需要获取最后一个并仅计算该编号。

select TicketNumber, count(TicketNumber) as ticketcount
from your_table a
inner join (select max(id) as maxid from your_table) b where a.id = b.id) 
group by TicketNumber

每个 ID 包含某个日期的所有 TicketNumbers 的总和。

select outer.Date, outer.ID, inner.ticketcount, sum(inner.ticketcount)
from your_table outer
inner join ( select TicketNumber, count(TicketNumber) as ticketcount
             from your_table a
             inner join (select max(id) as maxid from your_table) b 
             where a.id = b.id) 
             group by TicketNumber ) inner
  on inner.TicketNumber = outer.TicketNumber
group by outer.Date, outer.ID, inner.ticketcount

某些 ID 的类别和子类别可能会在一天内发生变化。

select yt.Date
       , yt.ID, 
       , yt.TicketNumber
       , yt.Category
       , yt.SubCategory
       , t2.ticketcount as TCount1
       , t2.sumticket as TCount2
       , count(yt.ID) as TCount3
from your_table yt
inner join (select Date, ticketcount, sum(ticketcount) as sumticket
      from your_table outer
      inner join ( select TicketNumber, count(TicketNumber) as ticketcount
                   from your_table a
                   inner join (select max(id) as maxid from your_table) b 
                   on a.id = b.id 
                   group by TicketNumber ) inner
       on inner.TicketNumber = outer.TicketNumber
       group by Date, ticketcount) t2
  on yt.Date = t2.Date
  group by yt.Date
          , yt.ID, 
          , yt.TicketNumber
          , yt.Category
          , yt.SubCategory
          , t2.ticketcount
          , t2.sumticket

假设您的 TCount1 表示您的案例编号 1，Tcount2 表示您的案例编号 2，Tcount3 表示列表中的案例 3。