SQL语句问题

[Jcon_]

本人对SQL语句不是很熟悉，现在有一需求是修复bug，就是下面的SQL语句查询出来的数据是不正确。由于写得比较长，里面的逻辑不是很懂，请大神指导指导！！

<sql-query name="getGameKeepRateStats">
		select cre_day datetime, 
       nvl(yx_rs, 0) gameNum, 
       nvl(yx_lc_rs_1, 0) morroDayNum , 
       nvl(yx_lc_rs_3, 0) threeDayNum,
       nvl(yx_lc_rs_7, 0) sevenDayNum 
  from (select cre_day,
               count(account) yx_rs,
               count(case
                       when exists
                        (select 'x'
                               from acm_order_lottery tmp
                              where tmp.account = t1.account
                                and tmp.create_time >=
                                    to_date(t1.cre_day, 'yyyymmdd') - 1
                                and tmp.create_time <
                                    to_date(t1.cre_day, 'yyyymmdd')
                                and not exists
                              (select 'x'
                                       from acm_order_lottery tmp2
                                      where tmp.account = tmp2.account
                                        and tmp2.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 1)
                                and not exists
                              (select 'x'
                                       from acm_order_lottery_his tmp3
                                      where tmp.account = tmp3.account
                                        and tmp3.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 1)) then
                        account
                     end) yx_lc_rs_1,
               count(case
                       when exists
                        (select 'x'
                               from acm_order_lottery tmp
                              where tmp.account = t1.account
                                and tmp.create_time >=
                                    to_date(t1.cre_day, 'yyyymmdd') - 2
                                and tmp.create_time <
                                    to_date(t1.cre_day, 'yyyymmdd') - 1
                                and not exists
                              (select 'x'
                                       from acm_order_lottery tmp2
                                      where tmp.account = tmp2.account
                                        and tmp2.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 2)
                                and not exists
                              (select 'x'
                                       from acm_order_lottery_his tmp3
                                      where tmp.account = tmp3.account
                                        and tmp3.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 2)) then
                        account
                     end) yx_lc_rs_3,
               count(case
                       when exists
                        (select 'x'
                               from acm_order_lottery tmp
                              where tmp.account = t1.account
                                and tmp.create_time >=
                                    to_date(t1.cre_day, 'yyyymmdd') - 6
                                and tmp.create_time <
                                    to_date(t1.cre_day, 'yyyymmdd') - 5
                                and not exists
                              (select 'x'
                                       from acm_order_lottery tmp2
                                      where tmp.account = tmp2.account
                                        and tmp2.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 6)
                                and not exists
                              (select 'x'
                                       from acm_order_lottery_his tmp3
                                      where tmp.account = tmp3.account
                                        and tmp3.create_time <
                                            to_date(t1.cre_day, 'yyyymmdd') - 6)) then
                        account
                     end) yx_lc_rs_7
          from (select to_char(create_time, 'yyyymmdd') cre_day, account
                  from acm_order_lottery aot
                 where create_time >= :startDate
                   and create_time < :endDate
                   and status in (1, 2, 3, 4, 5, 7)
                   and error_code = '000000'
                 group by to_char(create_time, 'yyyymmdd'), account) t1
         group by cre_day)
 order by cre_day

[卖水果的net]

你的问题是什么？ 给出表结构，测试数据，和你的预期结果。

[宋哥]

先把语句简化，逐步增加，边增加，边查看查询结果，这样才能找出问题，一下子写那么多，出了问题不好找。