跪求SQL报表代码，必有重谢

qq_31551825 2017-09-18 02:42:46

截图中的
本期应收
SELECT FAmount FROM t_RP_Contact WHERE FType=5
本期实收
SELECT FAmount FROM t_RP_Contact WHERE FType=3

初级学习菜鸟，求代码，必重谢！！！第一次发帖，求大神救命！！！

...全文

692 23 打赏收藏转发到动态举报

写回复

用AI写文章

23 条回复

切换为时间正序

请发表友善的回复…

发表回复

小野马1209 2017-09-21

打赏
举报

给你参考下我之前提问的一个帖，你参考下就知道怎么实现了

--创建表1 项目主数据
CREATE TABLE Table1(ID VARCHAR (50)) 
INSERT INTO Table1 VALUES('A')  
--创建表2  销售发票
CREATE TABLE Table2(ID VARCHAR (50),
                 TYPE VARCHAR (50),
                 Billdate int,
                 Month int,
                 BillNo VARCHAR(50) primary key,
                 Amount numeric(19, 9))
INSERT INTO Table2 VALUES('A','销售发票',20170601,201701,'Voice201706001',3000) 
INSERT INTO Table2 VALUES('A','销售发票',20170731,201707,'Voice201706002',70000)  
--创建表3  收款单
CREATE TABLE Table3(ID VARCHAR (50),
                 TYPE VARCHAR (50),
                 Billdate int,
                  Month int,
                 BillNo VARCHAR(50) primary key,
                 Amount numeric(19, 9))
INSERT INTO Table3 VALUES('A','收款单',20170601,201706,'Bank201706001',3000) 
INSERT INTO Table3 VALUES('A','收款单',20170831,201708,'Bank201708002',10000)  

------------------------------------------------------------------------
--创建表4收款汇票
CREATE TABLE Table4(ID VARCHAR (50),
                 TYPE VARCHAR (50),
                 Billdate int,
                  Month int,
                 BillNo VARCHAR(50) primary key,
                 Amount numeric(19, 9))
INSERT INTO Table4 VALUES('A','收款汇票',20170901,201709,'HP201709001',65000) 
 
----------------------------------
SELECT * FROM Table1 
SELECT * FROM Table2 
SELECT * FROM Table3 
SELECT * FROM Table4 

-----------
Select A.ID,'销售发票' as Type,B.Billdate,B.BillNo,B.Month,B.Amount From Table1 A Left Join Table2 AS B On A.Id=B.Id
Union ALL
Select A.ID,'收款单' as Type,C.Billdate,C.BillNo,C.Month,C.Amount From Table1 A Left Join Table3 AS  C On C.ID=A.ID
Union ALL
Select A.ID,'收款汇票' as Type,D.Billdate,D.BillNo,D.Month,D.Amount From Table1 A Left Join Table4 AS D On D.ID=A.ID
order by Billdate

解决方案

;WITH ctea AS  (  --union 到一起
SELECT A.ID,'销售发票' as Type,B.Billdate,B.BillNo,B.Month,B.Amount From Table1 A Left Join Table2 AS B On A.Id=B.Id
Union ALL
Select A.ID,'收款单' as Type,C.Billdate,C.BillNo,C.Month,C.Amount From Table1 A Left Join Table3 AS  C On C.ID=A.ID
Union ALL
Select A.ID,'收款汇票' as Type,D.Billdate,D.BillNo,D.Month,D.Amount From Table1 A Left Join Table4 AS D On D.ID=A.ID
),
cteb AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY Billdate) AS NO FROM ctea  --加一个排序列
),
ctec AS (	
SELECT *,case cteb.type when '销售发票' then cteb.Amount
 else -cteb.Amount 
 end as tempamount FROM cteb WHERE cteb.NO=1		--递归每次读取一条，并进行加减法
UNION ALL
SELECT  cteb.* ,
        CONVERT(numeric(19, 9),ctec.tempamount + CASE WHEN 
                                                 cteb.Type = '销售发票' THEN cteb.Amount
                                                 ELSE -cteb.Amount
                                                 END)
FROM    cteb
      inner  JOIN ctec ON ctec.NO + 1 = cteb.NO
)
SELECT * FROM ctec

h3wuzw 2017-09-21

打赏
举报

可以考虑放在不同的表中，进一个表，出一个表，基础期初一个表

阳泉酒家小当家 2017-09-19

打赏
举报

另外，也可以考虑单独写多段sql分别把数据求出来，然后在程序端，去取这些数据，在相应的单元格里显示出来就行。用纯sql来计算，不是很方便

阳泉酒家小当家 2017-09-19

打赏
举报

多写几段sql，然后把结果union all拼接起来试试

听雨停了 2017-09-18

打赏
举报

引用楼主 qq_31551825 的回复:

截图中的
本期应收
SELECT FAmount FROM t_RP_Contact WHERE FType=5
本期实收
SELECT FAmount FROM t_RP_Contact WHERE FType=3

初级学习菜鸟，求代码，必重谢！！！第一次发帖，求大神救命！！！



----测试数据

IF OBJECT_ID('Test_DB..t_rp_contact') IS NOT NULL

DROP TABLE t_rp_contact

CREATE TABLE t_rp_contact(

Fcustomer VARCHAR(50),

FDate DATETIME,

Fexplanation VARCHAR(50),

FType VARCHAR(5),

FAmount FLOAT	

)

INSERT INTO t_rp_contact

SELECT '张三','2017/7/7',null,'1',-800 union all

SELECT '张三','2017/7/8','其他','3',1000 union all

SELECT '张三','2017/7/9',null,'5',200 union all

SELECT '张三','2017/7/10',null,'3',550 union all

SELECT '张三','2017/8/13',null,'5',150 union all

SELECT '张三','2017/8/14',null,'3',200

----测试数据结束



--创建一个表存每月计算出来的余额，方便下个月跑报表时使用

IF OBJECT_ID('Test_DB..t_rp_Balance') IS NOT NULL

DROP TABLE t_rp_Balance

CREATE TABLE t_rp_Balance(

Fcustomer VARCHAR(50),

FDate DATETIME,

Fexplanation VARCHAR(50),

FType VARCHAR(5),

FAmount FLOAT,

Balance FLOAT

)

--把六月份的余额用这种格式插入这个表中

INSERT INTO t_rp_Balance

SELECT  '张三' as Fcustomer,'2017-06-01' as FDate,'上月余额' AS Fexplanation, '1' as FType,0 AS FAmount, cast(300 AS FLOAT) AS Balance

--SELECT * FROM t_rp_Balance



--执行存储过程就可以跑出结果

--EXEC GetReport '张三','2017-08-01'



CREATE PROC GetReport

@Fcustomer VARCHAR(50),

@FDate DATETIME

AS 

BEGIN

	WITH cte AS (

		--把同一天日期中的3显示为1

	SELECT Fcustomer, FDate,Fexplanation,case FType WHEN '3' THEN '1' ELSE Ftype END AS FType, SUM(FAmount) OVER( PARTITION BY Fcustomer, FDate,FType ) AS FAmount 

	FROM  t_rp_contact  

	WHERE fcustomer=@Fcustomer 

	AND dateadd(d,-day(Fdate)+1,Fdate)=@FDate

	),

	cte2 AS (			

	--SELECT  @Fcustomer as Fcustomer,@Fdate as FDate,'上月余额' AS Fexplanation, '1' as FType,0 AS FAmount, cast(300 AS FLOAT) AS Balance

	SELECT TOP 1 * FROM t_rp_Balance WHERE Fdate=DATEADD(MONTH,-1,@FDate)

	UNION all

	SELECT DISTINCT Fcustomer, FDate,Fexplanation, FType,sum(Famount) OVER( PARTITION BY Fcustomer, FDate, FType) AS FAmount,

	0 AS Balance		

	FROM cte

	)

	,

	cte3 AS (

	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS 'ID',* FROM cte2

	)

	,

	cte4 AS (

	SELECT TOP 1 ID, Fcustomer,FDate,Fexplanation,FType,FAmount,Balance  FROM cte3

	UNION ALL

	SELECT a.ID,a.Fcustomer,a.FDate,a.Fexplanation,a.Ftype,a.FAmount,

	cast(CASE a.Ftype when '1' THEN b.Balance+a.FAmount WHEN '5' then b.Balance-a.FAmount END AS FLOAT) AS Balance

		FROM cte3 a 

		JOIN cte4 b ON a.ID=b.ID+1

	),

	cte5 AS (

	SELECT * FROM cte4 PIVOT (MAX(Famount) FOR Ftype IN ([1],[5])) a

	)

	--将查询的结果插入零时表

	SELECT Fcustomer,convert(varchar(100),FDate,23) AS Fdate,Fexplanation,[1] AS [本月应收],[5] as [本月实收],Balance as [本月余额] 

	INTO #tab

	FROM cte5

	UNION ALL

	SELECT DISTINCT @Fcustomer,@Fdate,'总计',sum(ISNULL([1],0)) over( partition by Fcustomer),

	sum(ISNULL([5],0))over( partition by Fcustomer), a.Balance

	from cte5,(SELECT TOP 1 Balance FROM cte5 ORDER BY Fdate DESC ) a

	

	SELECT * FROM #tab

	

	--把这个月的计算出的余额存入一个表中，方便计算下个月报表时使用

	INSERT INTO t_rp_Balance

	SELECT TOP 1 @Fcustomer as Fcustomer,@Fdate as FDate,'上月余额' AS Fexplanation, '1' as FType,0 AS FAmount, cast([本月余额] as float) AS Balance

	FROM #tab

	WHERE fexplanation='总计'

	

	--删除查询的零时表

	DROP table #tab	

END

八点半搞到现在，终于给你弄出你要的报表了

二月十六版主 2017-09-18

打赏
举报

引用 12 楼 z10843087 的回复:

写个置顶帖，，，说明以下，最近MYSQL的问题太多了，发帖说明数据库是什么

很少有人看，大版早就写了，而且就是置顶的……

RINK_1 2017-09-18

打赏
举报


IF OBJECT_ID(N'TEMPDB.DBO.#T_RP_CONTACT') IS NOT NULL
DROP TABLE #T_RP_CONTACT
CREATE TABLE #T_RP_CONTACT
(FCUSTOMER NVARCHAR(20),
 FDATE DATE,
 FEXPLANATION NVARCHAR(100),
 FTYPE INT,
 FAMOUNT DECIMAL(12,4))
 
INSERT INTO #T_RP_CONTACT
SELECT '张三','2017-06-09','',3,300 UNION ALL
SELECT '李四','2017-06-19','',3,500 UNION ALL
SELECT '张三','2017-07-07','',1,-800 UNION ALL
SELECT '张三','2017-07-08','其他',3,1000 UNION ALL
SELECT '张三','2017-07-09','',5,200 UNION ALL
SELECT '李四','2017-07-15','',3,1200 UNION ALL
SELECT '张三','2017-07-10','',3,550 UNION ALL
SELECT '张三','2017-08-13','',5,150 UNION ALL
SELECT '张三','2017-08-14','',3,200

DECLARE @MONTH VARCHAR(10)
DECLARE @NAME NVARCHAR(10)

SET @MONTH='2017-07'
SET @NAME='张三'

;WITH CTE1
AS
(SELECT SUM(CASE WHEN FTYPE IN (1,3) THEN FAMOUNT 
            WHEN FTYPE=5 THEN -1*FAMOUNT END) AS PRE_BALANCE 
  FROM #T_RP_CONTACT
  WHERE FDATE<CAST(@MONTH+'-01' AS DATE)
  AND FCUSTOMER=@NAME),

CTE2
AS
(select A.FCUSTOMER,A.FDATE,A.FEXPLANATION,
        CASE WHEN A.FTYPE IN (1,3) THEN A.FAMOUNT ELSE NULL END AS AR,
        CASE WHEN A.FTYPE=5 THEN A.FAMOUNT ELSE NULL END AS RR,
        PRE_BALANCE+SUB_TOTAL AS BALANCE,PRE_BALANCE,
        1 AS LAST_OR_PRESENT
from #T_RP_CONTACT A
JOIN CTE1 B ON 1=1
OUTER APPLY 
(SELECT SUM(CASE WHEN FTYPE IN (1,3) THEN FAMOUNT 
                 WHEN FTYPE=5 THEN -1*FAMOUNT END) AS SUB_TOTAL
 FROM #T_RP_CONTACT
 WHERE LEFT(FDATE,7)=@MONTH AND FDATE<=A.FDATE AND FCUSTOMER=A.FCUSTOMER) AS C
WHERE LEFT(FDATE,7)=@MONTH AND A.FCUSTOMER=@NAME
UNION ALL
SELECT '',NULL,'上月余额',NULL,NULL,PRE_BALANCE,PRE_BALANCE,0 FROM CTE1)


--SELECT * FROM CTE2

SELECT CASE WHEN GROUPING(FCUSTOMER)=1 THEN @NAME ELSE FCUSTOMER END AS FCUSTOMER,
       FDATE,
       CASE WHEN GROUPING(FCUSTOMER)=1 THEN '总计' ELSE MAX(FEXPLANATION) END AS FEXPLANATION,
       SUM(AR) AS AR,
       SUM(RR) AS RR,
       CASE WHEN GROUPING(FCUSTOMER)=0 THEN ISNULL(MAX(BALANCE),0)
       WHEN GROUPING(FCUSTOMER)=1 THEN ISNULL(SUM(AR),0)-ISNULL(SUM(RR),0)+ISNULL(MAX(PRE_BALANCE),0) 
       END AS BALANCE
FROM CTE2
GROUP BY FCUSTOMER,FDATE WITH ROLLUP
HAVING GROUPING(FCUSTOMER)=GROUPING(FDATE)
ORDER BY MAX(LAST_OR_PRESENT),GROUPING(FCUSTOMER)

qq_31551825 2017-09-18

打赏
举报

引用 12 楼 z10843087 的回复:

[quote=引用 7 楼 sinat_28984567 的回复:]
根据楼主说的f、d、e相减的过程，写了这个例子，楼主可以看一下，从第二行开始每行的f都是上一层的f+d-e得到的

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([rq] DATETIME,[f] int,[d] int,[e] int)

Insert #T

select '2017-08-1',1,1,1 union all

select '2017-08-2',0,2,2 union all

select '2017-08-3',0,3,3

Go

--测试数据结束

;WITH ctea AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY rq) AS rn FROM #T

),cteb AS (

SELECT * FROM ctea WHERE rn=1

UNION ALL

SELECT  ctea.[rq] ,

        cteb.f + cteb.d - cteb.e AS f ,

        ctea.d ,

        ctea.e ,

        ctea.rn

FROM    ctea

        JOIN cteb ON cteb.rn + 1 = ctea.rn

)

SELECT * FROM cteb

写个置顶帖，，，说明以下，最近MYSQL的问题太多了，发帖说明数据库是什么[/quote]

数据库：SQL 2008 R2

听雨停了 2017-09-18

打赏
举报

引用 13 楼 qq_31551825的回复:

我把需求整理了一下，不知道各位大神能否明白我的意思，初级菜鸟请教各位大神，一定重谢。这个论坛第一次发帖。

晚上到家帮你弄弄看能不能弄出来，现在地铁上不方便

qq_31551825 2017-09-18

打赏
举报

引用 13 楼 qq_31551825 的回复:

我把需求整理了一下，不知道各位大神能否明白我的意思，初级菜鸟请教各位大神，一定重谢。这个论坛第一次发帖。

sql2008 r2

qq_31551825 2017-09-18

打赏
举报

我把需求整理了一下，不知道各位大神能否明白我的意思，初级菜鸟请教各位大神，一定重谢。这个论坛第一次发帖。

OwenZeng_DBA 2017-09-18

打赏
举报

引用 7 楼 sinat_28984567 的回复:

根据楼主说的f、d、e相减的过程，写了这个例子，楼主可以看一下，从第二行开始每行的f都是上一层的f+d-e得到的

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([rq] DATETIME,[f] int,[d] int,[e] int)
Insert #T
select '2017-08-1',1,1,1 union all
select '2017-08-2',0,2,2 union all
select '2017-08-3',0,3,3
Go
--测试数据结束
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY rq) AS rn FROM #T
),cteb AS (
SELECT * FROM ctea WHERE rn=1
UNION ALL
SELECT  ctea.[rq] ,
        cteb.f + cteb.d - cteb.e AS f ,
        ctea.d ,
        ctea.e ,
        ctea.rn
FROM    ctea
        JOIN cteb ON cteb.rn + 1 = ctea.rn
)
SELECT * FROM cteb

写个置顶帖，，，说明以下，最近MYSQL的问题太多了，发帖说明数据库是什么

qq_31551825 2017-09-18

打赏
举报

引用 10 楼 qq_37170555 的回复:

[quote=引用 9 楼 qq_31551825 的回复:]
我的需求就是要得到那张截图报表

那你能提供点测试数据不？比如说有什么表，表跟表之间有什么关系的。或者说我直接建一个你上面截图的那种格式的表，根据前两列给你算出来最后一列的数据？
下次回复的时候引用一下，不然我都不知道你回复了我[/quote]

听雨停了 2017-09-18

打赏
举报

引用 9 楼 qq_31551825 的回复:

我的需求就是要得到那张截图报表

那你能提供点测试数据不？比如说有什么表，表跟表之间有什么关系的。或者说我直接建一个你上面截图的那种格式的表，根据前两列给你算出来最后一列的数据？下次回复的时候引用一下，不然我都不知道你回复了我

qq_31551825 2017-09-18

打赏
举报

我的需求就是要得到那张截图报表

听雨停了 2017-09-18

打赏
举报

引用 5 楼 qq_31551825 的回复:

你们看一下截图，我用截图中数据说下f4=f3+d4-e4, f5=f4+d5-e5 循环下去的。F3是这样循环的结果，做为下月的期初数。

你这么一说我看了半天算是明白了，可是你的需求是什么呢，你要求什么出来呢

二月十六版主 2017-09-18

打赏
举报

根据楼主说的f、d、e相减的过程，写了这个例子，楼主可以看一下，从第二行开始每行的f都是上一层的f+d-e得到的

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([rq] DATETIME,[f] int,[d] int,[e] int)

Insert #T

select '2017-08-1',1,1,1 union all

select '2017-08-2',0,2,2 union all

select '2017-08-3',0,3,3

Go

--测试数据结束

;WITH ctea AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY rq) AS rn FROM #T

),cteb AS (

SELECT * FROM ctea WHERE rn=1

UNION ALL

SELECT  ctea.[rq] ,

        cteb.f + cteb.d - cteb.e AS f ,

        ctea.d ,

        ctea.e ,

        ctea.rn

FROM    ctea

        JOIN cteb ON cteb.rn + 1 = ctea.rn

)

SELECT * FROM cteb

二月十六版主 2017-09-18

打赏
举报

建议楼主列出表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

qq_31551825 2017-09-18