sql求助:近15天,每个操作员操作的商品数

数据都在一张表中:

table(id,operator,dealtime......)
id:自增长主键
opeartor:操作员代号
dealtime:操作时间
自己些了个只能查单个操作员最近15天的数据。我最好全部一次性查出。给思路也行。
select t2.time,IFNULL(t1.count,0) from (select date(lc.dealtime)value,count(lc.id)count from table lc
where operator='gg'
and date_sub(now(), INTERVAL 15 DAY) <= date(lc.dealtime) group by value)t1
right join (
select date(date_sub(now(), INTERVAL 14 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 13 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 12 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 11 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 10 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 9 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 8 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 6 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 7 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 5 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 4 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 3 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 2 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 1 DAY)) time from DUAL UNION
select date(date_sub(now(), INTERVAL 0 DAY)) time from DUAL
)t2 on t2.time=t1.value

2个回答

select * from table where DATEDIFF(DAY,dealtime,GETDATE())<=15

先查询所有15天内的数据,然后根据操作员分组求和

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!