JonSmiths
JonSmiths
采纳率5.9%
2017-12-07 08:09

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

  • sql

数据都在一张表中:

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条回答

  • u013100483 flood004 4年前

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

    点赞 评论 复制链接分享
  • loushuai1234567890 向你丢个BUG 4年前

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

    点赞 评论 复制链接分享

相关推荐