查询一张表中只有本月数据求半年的报表的sql

最新推荐文章于 2024-09-26 21:04:50 发布

selfImprove_x

最新推荐文章于 2024-09-26 21:04:50 发布

阅读量535

点赞数

分类专栏：笔记文章标签：一张表中求半年的sql sql

本文链接：https://blog.csdn.net/u012608167/article/details/44343299

版权

笔记专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文介绍如何利用SQL语句从一张表中仅选取本月数据，并进一步计算生成过去半年的报表，适用于数据库管理和数据分析场景。

摘要由CSDN通过智能技术生成

SELECT glzb_name,recdate,product_id,b_flag,SUM(amt) AS sg_amt INTO #temp FROM product..of_match_glzb 
  WHERE b_flag=0 GROUP BY recdate,product_id,glzb_name,b_flag
  SELECT glzb_name,recdate,product_id,b_flag,SUM(amt) AS sh_amt INTO #temp2 FROM product..of_match_glzb 
  WHERE b_flag=1 GROUP BY recdate,product_id,glzb_name,b_flag
  SELECT  a.glzb_name,a.recdate,a.product_id,a.sg_amt,b.sh_amt INTO  #temp3 FROM #temp a JOIN #temp2 b ON  
  a.recdate=b.recdate AND a.product_id=b.product_id AND a.glzb_name=b.glzb_name 
  
  SELECT glzb_name AS 分公司,product_id AS 产品代码,
  SUM(CASE WHEN recdate =@bdate THEN data ELSE 0 END ) AS '查询本月',
  SUM(CASE WHEN recdate =@qygy THEN data ELSE 0 END ) AS '前一个月',
  SUM(CASE WHEN recdate =@qlgy THEN data ELSE 0 END ) AS '前两个月',
  SUM(CASE WHEN recdate =@qsgy THEN data ELSE 0 END ) AS '前三个月',
  SUM(CASE WHEN recdate =@qsgy4 THEN data ELSE 0 END ) AS '前四个月',
  SUM(CASE WHEN recdate =@edate THEN data ELSE 0 END ) AS '前五个月'
  FROM
  (
  SELECT glzb_name,product_id,recdate, ROUND(sg_amt-sh_amt,2) AS data 
  FROM #temp3 WHERE  recdate BETWEEN @edate AND @bdate ) as tep WHERE product_id=@procode
  GROUP BY  glzb_name,product_id
  
  DROP TABLE #temp
  DROP TABLE #temp2
  DROP TABLE #temp3