求大神帮我改个sql

[Zel_Zhu]

这个是原sql

WITH y AS ( SELECT DISTINCT YEAR ( edc_Date ) AS 年 FROM EveryDayCollection WHERE edc_State = 1 ),
m AS ( SELECT TOP 12 ROW_NUMBER () OVER ( ORDER BY ( SELECT NULL )) AS 月 FROM master.dbo.spt_values ),
t AS ( SELECT * FROM y JOIN m ON 1 = 1 ),
tdata AS (
	SELECT YEAR
		( edc_Date ) AS 年,
		MONTH ( edc_Date ) AS 月,
		ROUND( SUM ( isnull( edc_Actualmoney, 0 ) ) * 1.000, 1 ) AS 实收,
		ROUND( SUM ( isnull( edc_Shouldmoney, 0 ) ) * 1.000, 1 ) AS 应收 
	FROM
		EveryDayCollection
		LEFT JOIN ContractAboutHouse ON EveryDayCollection.edc_RCID = ContractAboutHouse.cah_RRID
		left join  RentContract on  ContractAboutHouse.cah_RRID = RentContract.rc_ID
		LEFT JOIN HouseProperty ON ContractAboutHouse.cah_HPID= HouseProperty.hp_ID 
	WHERE
		edc_State = 1 
		and RentContract.rc_HouseOrShop=20
	GROUP BY
		YEAR ( edc_Date ),
		MONTH ( edc_Date ),
		HouseProperty.hp_IShardcover
	) SELECT
	t.年,
	t.[月],
	CONVERT ( VARCHAR, t.[年] ) + '年' + CONVERT ( VARCHAR, t.[月] ) + '月' AS nianyue,
	tdata.[实收],
	 tdata.[应收]
FROM
	t
	LEFT JOIN tdata ON t.年 = tdata.年 
	AND t.月 = tdata.月

这个查询结果是这样的
然后现在要根据HouseProperty.hp_IShardcover这个区分是否是精装房,然后下面变成精装房实精装房应收非精装房实收非精装房应收
谢谢大神

[ダ雨夹雪リ]

--我只取你用到的这四个字段做测试
--建表
CREATE TABLE #T
(
edc_Date   DATE,			--日期
hp_IShardcover BIT,		--是否精装修
edc_Actualmoney INT,	--实收
edc_Shouldmoney INT  --应收
)

--测试数据
INSERT INTO #T VALUES('2019-01-10',1,1000,1100),('2019-01-11',1,500,500),('2019-02-01',0,100,200)
,('2018-02-01',0,100,200)

--查询
SELECT A.年,B.月,CONVERT(VARCHAR(4),A.年)+'年'+CONVERT(VARCHAR(2),B.月)+'月' AS 年月,
SUM(CASE WHEN C.hp_IShardcover=1 THEN C.edc_Actualmoney ELSE NULL END) AS 精装实收,
SUM(CASE WHEN C.hp_IShardcover=1 THEN C.edc_Shouldmoney ELSE NULL END) AS 精装应收,
SUM(CASE WHEN C.hp_IShardcover=0 THEN C.edc_Actualmoney ELSE NULL END )AS 非精装实收,
SUM(CASE WHEN C.hp_IShardcover=0 THEN C.edc_Shouldmoney ELSE NULL END) AS 非精装应收
 FROM  
(SELECT DISTINCT YEAR(edc_Date)AS 年  FROM #T) A CROSS JOIN
(SELECT number AS 月 FROM master.dbo.spt_values WHERE type='P'AND number BETWEEN 1AND 12)B
LEFT JOIN 
(
SELECT YEAR(edc_Date) AS 年,MONTH(edc_Date) AS 月,hp_IShardcover,edc_Actualmoney,edc_Shouldmoney FROM #T 
) C ON C.年 = A.年 AND B.月=C.月
GROUP BY A.年,B.月 

[Zel_Zhu]

引用 1 楼 雨夹雪 的回复:

--我只取你用到的这四个字段做测试
--建表
CREATE TABLE #T
(
edc_Date   DATE,			--日期
hp_IShardcover BIT,		--是否精装修
edc_Actualmoney INT,	--实收
edc_Shouldmoney INT  --应收
)

--测试数据
INSERT INTO #T VALUES('2019-01-10',1,1000,1100),('2019-01-11',1,500,500),('2019-02-01',0,100,200)
,('2018-02-01',0,100,200)

--查询
SELECT A.年,B.月,CONVERT(VARCHAR(4),A.年)+'年'+CONVERT(VARCHAR(2),B.月)+'月' AS 年月,
SUM(CASE WHEN C.hp_IShardcover=1 THEN C.edc_Actualmoney ELSE NULL END) AS 精装实收,
SUM(CASE WHEN C.hp_IShardcover=1 THEN C.edc_Shouldmoney ELSE NULL END) AS 精装应收,
SUM(CASE WHEN C.hp_IShardcover=0 THEN C.edc_Actualmoney ELSE NULL END )AS 非精装实收,
SUM(CASE WHEN C.hp_IShardcover=0 THEN C.edc_Shouldmoney ELSE NULL END) AS 非精装应收
 FROM  
(SELECT DISTINCT YEAR(edc_Date)AS 年  FROM #T) A CROSS JOIN
(SELECT number AS 月 FROM master.dbo.spt_values WHERE type='P'AND number BETWEEN 1AND 12)B
LEFT JOIN 
(
SELECT YEAR(edc_Date) AS 年,MONTH(edc_Date) AS 月,hp_IShardcover,edc_Actualmoney,edc_Shouldmoney FROM #T 
) C ON C.年 = A.年 AND B.月=C.月
GROUP BY A.年,B.月 

谢谢大神 就是要这种效果