报表sql模板

最新推荐文章于 2024-10-09 09:27:21 发布

最新推荐文章于 2024-10-09 09:27:21 发布 · 186 阅读

文章标签：

SELECT *
    FROM   (
   SELECT 0 as orderby,

           tea.team_id,
           sor.articles_name,
           tea.team_name,
           req.cost_center,
           req.infonum,
           req.infototal
   FROM      ga_articles_info req,
          ga_articlesbuy_info buy,
          ga_articles_sort sor,
          ga_employee_info emp,
          ga_team_info tea
   WHERE   1=1
           and req.process_date=buy.articelesbuy_id
              and req.articles_sort_id=sor.sort_id
              and emp.employee_id=req.employee_id
              and emp.team_id=tea.team_id
              and req.state='1'
              and req.process_time BETWEEN to_date(?,'yyyy-mm-dd')
     and to_date(?,'yyyy-mm-dd')

   UNION   ALL

   SELECT 1 as orderby       ,

            tea.team_id        ,
              '',
              '',
              req.cost_center,
            to_char(sum(infonum))      ,
            to_char(sum(infototal),'9999999.99')

   FROM      ga_articles_info req,
          ga_articlesbuy_info buy,
          ga_articles_sort sor,
          ga_employee_info emp,
          ga_team_info tea
   WHERE   1=1
     and req.process_date=buy.articelesbuy_id
              and req.articles_sort_id=sor.sort_id
              and emp.employee_id=req.employee_id
              and emp.team_id=tea.team_id
              and req.state='1'
              and req.process_time BETWEEN to_date(?,'yyyy-mm-dd')
     and to_date(?,'yyyy-mm-dd')
   GROUP BY req.cost_center,tea.team_id

   UNION   ALL
   SELECT 2 as orderby       ,
            ''               ,
            '',
              ''               ,
              ''               ,
            to_char(sum(infonum))      ,
            to_char(sum(infototal),'9999999.99')

   FROM      ga_articles_info req,
          ga_articlesbuy_info buy,
          ga_articles_sort sor,
          ga_employee_info emp,
          ga_team_info tea
   WHERE   1=1
     and req.process_date=buy.articelesbuy_id
              and req.articles_sort_id=sor.sort_id
              and emp.employee_id=req.employee_id
              and emp.team_id=tea.team_id
              and req.state='1'
              and req.process_time BETWEEN to_date(?,'yyyy-mm-dd')
     and to_date(?,'yyyy-mm-dd')
    )UNIONTABLE ORDER BY cost_center,team_id,articles_name,orderby

} ['2007/07/02', '2007/07/31', '2007/07/02', '2007/07/31', '2007/07/02', '2007/07/31']！！

用orderby小计来标记是小计还是合计还是表中的记录。