WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-02-21',12800 UNION ALL
SELECT '公司A',2600,'2015-03-21',0 UNION ALL
SELECT '公司A',3600,'2015-04-21',0
)
,c AS (
SELECT DISTINCT 名称 FROM view1
)
,t1 AS (
SELECT c.名称,
m.month,
v.应付金额,
ISNULL(v.已付金额,-1) 已付金额
FROM c
JOIN (SELECT number month
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 12
) m
ON 1=1
LEFT JOIN view1 v
ON v.名称 = c.名称
AND MONTH(v.应收日期) = m.month
)
,t2 AS (
SELECT * FROM t1 WHERE month = 1
UNION ALL
SELECT m2.名称,
m2.month,
CASE WHEN m1.已付金额 = 0 THEN
m1.应付金额
ELSE
m2.应付金额
END,
CASE WHEN m1.已付金额 = 0 THEN
0
ELSE
m2.已付金额
END
FROM t2 m1
JOIN t1 m2
ON m2.名称 = m1.名称
AND m2.month = m1.month+1
)
SELECT *
FROM (
SELECT 名称,
Convert(varchar(11),month)+'月' col,
应付金额
FROM t2
) t
PIVOT (
MAX(应付金额)
FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
[7月],[8月],[9月],[10月],[11月],[12月])
) p
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
公司A NULL 12800 2600 2600 2600 2600 2600 2600 2600 2600 2600 2600
SELECT '公司A',12800,'2014-02-21',12800 UNION ALL
SELECT '公司A',2600,'2015-03-21',0 UNION ALL
SELECT '公司A',3600,'2015-04-21',0
)
,c AS (
SELECT DISTINCT 名称 FROM view1
)
,t1 AS (
SELECT c.名称,
m.month,
v.应付金额,
ISNULL(v.已付金额,-1) 已付金额
FROM c
JOIN (SELECT number month
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 12
) m
ON 1=1
LEFT JOIN view1 v
ON v.名称 = c.名称
AND MONTH(v.应收日期) = m.month
)
,t2 AS (
SELECT * FROM t1 WHERE month = 1
UNION ALL
SELECT m2.名称,
m2.month,
CASE WHEN m1.已付金额 = 0 THEN
m1.应付金额
ELSE
m2.应付金额
END,
CASE WHEN m1.已付金额 = 0 THEN
0
ELSE
m2.已付金额
END
FROM t2 m1
JOIN t1 m2
ON m2.名称 = m1.名称
AND m2.month = m1.month+1
)
SELECT *
FROM (
SELECT 名称,
Convert(varchar(11),month)+'月' col,
应付金额
FROM t2
) t
PIVOT (
MAX(应付金额)
FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
[7月],[8月],[9月],[10月],[11月],[12月])
) p
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
公司A NULL 12800 2600 2600 2600 2600 2600 2600 2600 2600 2600 2600
解决方案 »
- SQL2008出错(判断表是否存在,然后删除再创建)
- 这个 统计怎么办?
- 如何把存储过程当作子查询
- group by 和distinct 怎么隐含有排序?怎样去掉这个顺序?
- 急!!!修改表中字段的数据类型,在线等,谢谢!!!
- 超级分页方法,经测试,千万级别数据,毫秒级返回!
- 为什么在access转sql后id的顺序全乱了?该怎么办??
- 表记录加锁问题,急急!!!,在线等待!!!
- 急求!!!
- 软件工程师们,你们开发软件的时候听什么音乐呀?
- sql 如何实现将某表某一列的所有值(int型)复制到另一表的某一行某一列的一个字段中
- server2008上面安装sql server2005,提示windows installer错误
谢谢,但为什么代码我转正后,出现以下报错呢?
消息 240,级别 16,状态 1,第 1 行
在递归查询 "t2" 的列 "PayCost" 中,定位点类型和递归部分的类型不匹配。
WITH c AS (
SELECT DISTINCT ClientName FROM PayPlanView
)
,t1 AS (
SELECT c.ClientName,
m.month,
v.PlanCost,
ISNULL(v.PayCost,-1) PayCost
FROM c
JOIN (SELECT number month
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 12
) m
ON 1=1
LEFT JOIN PayPlanView v
ON v.ClientName = c.ClientName
AND MONTH(v.PlanDate) = m.month
)
,t2 AS (
SELECT * FROM t1 WHERE month = 1
UNION ALL
SELECT m2.ClientName,
m2.month,
CASE WHEN m1.PayCost = 0 THEN
m1.PlanCost
ELSE
m2.PlanCost
END,
CASE WHEN m1.PayCost = 0 THEN
0
ELSE
m2.PayCost
END
FROM t2 m1
JOIN t1 m2
ON m2.ClientName = m1.ClientName
AND m2.month = m1.month+1
)
SELECT *
FROM (
SELECT ClientName,
Convert(varchar(11),month)+'月' col,
PlanCost
FROM t2
) t
PIVOT (
MAX(PlanCost)
FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
[7月],[8月],[9月],[10月],[11月],[12月])
) p
可以了,谢谢,但出现了一个问题,当已付是跨年的时候,前面本来应该是空白的,也部都成了实数,跨年的时候,不算后面的,只算今年内的当如下数据测试时,前面1~11月,都被变成了260000,而且12800也没有显示在12月份.:
WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-12-21',12800 UNION ALL
SELECT '公司A',260000,'2015-1-20',0
)
结果:
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
公司A 260000 260000 260000 260000 260000 260000 260000 260000 260000 260000 260000 260000
如何能够实现让前面的空白 ,如下:
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
公司A 12800