u010603019
若邪
采纳率83.3%
2015-10-20 06:55 阅读 2.5k

sqlserver查询本周每天数据

5

不是查询总数,我需要查询两个字段,使用convert函数按时间查询再分组,就得将两个字段放入分组条件中,就错乱了。
假设有表table,字段有时间精确到秒,ID,消费金额和消费地点,现在要查询一段时间每天的消费金额和地点(时间可随意指定)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    u010603019 若邪 2015-10-20 08:23

    围绕convert函数进行时间的筛选,还是可以的但是效率不高,
    select c.autoid,c.callerid,c.doorid,c.cardday,c.eventtype,d.doorid as max_doorId,d.eventType as max_eventSort,d.cardday as max_cardDay
    from(select * from cr_cardevent a
    WHERE EXISTS (SELECT 1
    FROM(
    SELECT convert(varchar(10),cardday,121) as td, MIN(cardday)min_time,callerid FROM cr_cardevent c,Com_EmpCard card
    Where cardday between '2014-09-01' and '2014-09-03' and c.cardid=card.cardid and card.cardid in
    (select fingure.cardid from Finger_Data fingure) GROUP BY convert(varchar(10),cardday,121), callerid)b
    WHERE a.cardday=b.min_time AND a.callerid=b.callerid ))c ,
    (select *
    from cr_cardevent a WHERE EXISTS (SELECT 1
    FROM(SELECT convert(varchar(10),cardday,121) as td, MAX(cardday)max_time,callerid FROM cr_cardevent c,Com_EmpCard card
    where cardday between '2014-09-01' and '2014-09-03' and c.cardid=card.cardid and card.cardid in
    (select fingure.cardid from Finger_Data fingure) GROUP BY convert(varchar(10),cardday,121), callerid)b
    WHERE a.cardday=b.max_time AND a.callerid=b.callerid))d,
    cr_caller_basicinfo e
    where c.callerid = d.callerid and c.callerid = e.callerid and e.callertype=1
    and convert(varchar(10),c.cardday,121)=convert(varchar(10),d.cardday,121)
    需求是一段时间内每天每个用户最大最小的两笔数据

    点赞 评论 复制链接分享

相关推荐