SQL

LimYonna 发布于 08/22 11:45
阅读 102
收藏 0


如何把同一天的已付金额、未付金额并且把每一天的总条数计算出来弄成一行数据,

加载中
0
LimYonna
LimYonna

自己写的贴,做出来了,还是结一下帖

--测试数据
use Tempdb
go
if not object_id(N'Tempdb..#tab') is null
    drop table #tab
Go
Create table #tab([EA10] Date,[SP15] int,[pay] nvarchar(22))
Insert #tab
select '2019-08-12',220,N'未付' union all
select '2019-08-21',180,N'已付' union all
select '2019-08-21',250,N'未付' union all
select '2013-03-25',130,N'已付' union all
select '2019-08-07',55,N'已付' union all
select '2019-08-12',220,N'未付'
Go
Select * from #tab
--测试数据结束
 
SELECT EA10,
       MAX(CASE pay WHEN '未付' THEN SP15 ELSE 0 END) 未付,
       MAX(CASE pay WHEN '已付' THEN SP15 ELSE 0 END) 已付,
       sum(cnt) AS cnt
FROM   (
    SELECT EA10,pay,SUM(SP15) SP15,COUNT(1) AS cnt FROM #tab
    GROUP BY EA10,pay
) a
GROUP BY EA10;
 
 
EA10       未付          已付          cnt
---------- ----------- ----------- -----------
2013-03-25 0           130         1
2019-08-07 0           55          1
2019-08-12 440         0           2
2019-08-21 250         180         2

0
前端大师傅
前端大师傅

这个非常简单,group by 天数,然后再case when 一下pay 然后sum就可以了

SELECT
	DATE_FORMAT( pt.ea10, '%Y-%m-%d' ) `日期(按天)EA10`,
	sum( CASE WHEN pay = '未付' THEN pt.SIP15 ELSE 0 END ) 未付,
	sum( CASE WHEN pay = '已付' THEN pt.SIP15 ELSE 0 END ) 已付,
	sum( CASE WHEN pay = '已付' THEN 1 ELSE 0 END ) cnt 
FROM
	tableName pt 
GROUP BY
	`日期(按天)EA10`

如果结合你上个问题就是这样:

SELECT
	DATE_FORMAT( pt.ea10, '%Y-%m-%d' ) `日期(按天)EA10`,
	sum( CASE WHEN pay = '未付' THEN pt.SIP15 ELSE 0 END ) 未付,
	sum( CASE WHEN pay = '已付' THEN pt.SIP15 ELSE 0 END ) 已付,
	sum( CASE WHEN pay = '已付' THEN 1 ELSE 0 END ) cnt 
FROM
	(
	SELECT
		ea.ea10,
	CASE
			
			WHEN sip05 IS NOT NULL THEN
			'已付' ELSE '未付' 
		END pay,
	s.SIP15 
FROM
	t_eazero ea
	LEFT JOIN t_sip s ON s.sip05 = ea.EA00 
	) pt 
GROUP BY
	`日期(按天)EA10`

 

返回顶部
顶部