小女子求大神相助，如何分组筛选！！ - 编程论坛

#2

mywisdom882022-07-05 17:24

只有本站会员才能查看附件，请登录

#3

mywisdom882022-07-05 17:26

-- 1.先把000000至060000的时间，把日期-1
select 名称,日期,时间,金额,流水号,
case when 时间 between'000000' and '060000' then cast(日期 as datetime)-1
else cast(日期 as datetime) end as 新日期
from 业务

只有本站会员才能查看附件，请登录

#4

mywisdom882022-07-05 17:27

-- 2.把符合的记录找出来
select 名称,日期,新日期,时间,金额,流水号 from (
    select 名称,日期,时间,金额,流水号,
       case when 时间 between'000000' and '060000' then cast(日期 as datetime)-1
            else cast(日期 as datetime) end as 新日期
    from 业务)t1
where (t1.时间 between'000000' and '060000') or (t1.时间 between'220000' and '235959')

只有本站会员才能查看附件，请登录

#5

mywisdom882022-07-05 17:30

-- 过滤3次
select t2.新日期,SUM(金额)金额 from (
  select 名称,日期,新日期,时间,金额,流水号 from (
    select 名称,日期,时间,金额,流水号,
       case when 时间 between'000000' and '060000' then cast(日期 as datetime)-1
            else cast(日期 as datetime) end as 新日期
    from 业务)t1
where (t1.时间 between'000000' and '060000') or (t1.时间 between'220000' and '235959')
)t2
group by 新日期 having COUNT(1)>=3

只有本站会员才能查看附件，请登录

#6

mywisdom882022-07-05 17:38

select t5.名称,t5.日期,t5.时间,t5.金额,t5.流水号
from
   (select 名称,日期,新日期,时间,金额,流水号 from (
    select 名称,日期,时间,金额,流水号,
       case when 时间 between'000000' and '060000' then cast(日期 as datetime)-1
            else cast(日期 as datetime) end as 新日期
    from 业务)t1
    where (t1.时间 between'000000' and '060000') or (t1.时间 between'220000' and '235959')
   )t5,
  (select t2.新日期,SUM(金额)金额 from (
    select 名称,日期,新日期,时间,金额,流水号 from (
     select 名称,日期,时间,金额,流水号,
       case when 时间 between'000000' and '060000' then cast(日期 as datetime)-1
            else cast(日期 as datetime) end as 新日期
    from 业务)t1
    where (t1.时间 between'000000' and '060000') or (t1.时间 between'220000' and '235959')
   )t2
   group by 新日期 having COUNT(1)>=3
   )t6
where t6.新日期=t5.新日期

只有本站会员才能查看附件，请登录

#7

mywisdom882022-07-05 17:41

6,7楼中的
SUM(金额)金额，可以不要
不知道，能不能简化哦。。。

#8

mywisdom882022-07-06 08:57

select t1.名称,t1.日期,t1.时间,t1.金额,t1.流水号
from
( select 名称,日期,时间,金额,流水号,
         新日期=(case when 时间 between '000000' and '060000' then CAST(日期 AS datetime)-1
                 else CAST(日期 as datetime) end)
  from 业务
  where (时间 between '000000' and '060000') or (时间 between '220000' and '235959')
)t1,
( select 新日期 from
  ( select 新日期=(case when 时间 between '000000' and '060000' then CAST(日期 AS datetime)-1
                   else CAST(日期 as datetime) end)
    from 业务
    where (时间 between '000000' and '060000') or (时间 between '220000' and '235959')
  )t3
  group by 新日期 having COUNT(1)>=3
)t2
where t1.新日期 = t2.新日期
order by t1.日期

只有本站会员才能查看附件，请登录