几种条件下的唯一值
几种条件下的唯一值
提问于 2017-04-05 11:20:16
我有要求在每几个条件下提取唯一的数据，现在我被塞了下来。有人能建议如何解决这个问题吗？
表将如下所示：
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 | 3For 02.04.2017 for ID=2
02.04.2017| 2 | 22222 | New Machine| FFD | 9 | 7 | 9 我需要计算每个ID的总TCounts，但是：
- 如果一个TicketNumbers重复的话，我需要取最后一个，并且只计算那个。
- 每个ID包含每个日期的所有TicketNumbers之和。
- 类别和子类别可以在一天内更改某些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 (而不是uniques)。
回答 1
Stack Overflow用户
发布于 2017-04-05 11:54:42
如果一个TicketNumbers重复的话，我需要取最后一个，并且只计算那个。
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、您的案例编号2的Tcount2和您的案例3的Tcount3。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43229684
复制相关文章
相似问题
腾讯云开发者
