求sql语句
查询表BP中name有记录，则把相应的数据合并到AP中，
字段zongji值为以表BP'riqi'值开始的表AP中多字段累加而成
举两个例子：
1、查询表BP数据 li 存在，则取其‘riqi’值203 计算 a.203+a.204+BP.sl为zongji值
2、查询表BP数据 ma 存在，则取其‘riqi’值202 计算 a.202+a.203+a.204+BP.sl为zongji值
表 AP 数据如下：
name 201 202 203 204
------------------------------------------------------------
wu 55 21 8 3.9
li 80 32.7 70.5 92
zha 23 61 空值 5.7
ma 91 56.1 7.03 97
ta 33 空值 34 91
表 BP 数据如下：
name sl riqi
------------------------------------
li -30 203
wu -150 203
ma -43 202
zha -50 201
希望得到如下数据：
name 201 202 203 204 riqi sl zongji
--------------------------------------------------------------------------------------------
wu 55 21 8 3.9 203 -150 -138.1 /*(来源为8+3.9-150)
li 80 32.7 70.5 92 203 -30 132.5
ma 91 56.1 7.03 97 202 -43 117.13
ta 33 空值 34 91
zha 23 61 空值 5.7 201 -50 39.7
------解决思路----------------------
左连接就可以了吧，看着没啥难度啊
------解决思路----------------------
IF OBJECT_ID('tempdb.dbo.#AP') IS NOT NULL
DROP TABLE #AP
CREATE TABLE #AP (name varchar(16),[201] float,[202] float,[203] float,[204] float)
INSERT INTO #AP
SELECT 'wu',55,21,8,3.9 UNION ALL
SELECT 'li',80,32.7,70.5,92 UNION ALL
SELECT 'zha',23,61,NULL,5.7 UNION ALL
SELECT 'ma',91,56.1,7.03,97 UNION ALL
SELECT 'ta',33,NULL,34,91
IF OBJECT_ID('tempdb.dbo.#BP') IS NOT NULL
DROP TABLE #BP
CREATE TABLE #BP (name varchar(16),sl int,riqi int )
INSERT INTO #BP
SELECT 'li',-30,203 UNION ALL
SELECT 'wu',-150,203 UNION ALL
SELECT 'ma',-43,202 UNION ALL
SELECT 'zha',-50,201
SELECT *,
[sl] + [zongji] [zongji]
FROM [#AP] A
LEFT JOIN [#BP] B
ON [B].[name] = [A].[name]
CROSS APPLY
(SELECT SUM(nNum) zongji
FROM [#AP]
UNPIVOT
(nNum FOR riqi IN ([201] ,[202] ,[203] ,[204]) ) AS PVT
WHERE name = b.[name] AND riqi >= b.[riqi]) C
------解决思路----------------------
select AP.NAME,
[201],
[202],
[203],
[204],
riqi,
sl,
case when BP.name='li'
then [203]+[204]+sl
when BP.name='wu'
then [203]+[204]+sl
when BP.name='ma'
then[202]+[203]+[204]+sl
when BP.name='zha'
then[201]+[202]+isnull([203],0)+[204]+sl
else null end as zongji
from AP left join BP
on AP.name=BP.name
结果
NAME 201 202 203 204 riqi sl zongji
wu 55.00 21.00 8.00 3.90 203 -150.00 -138.10
li 80.00 32.70 70.50 92.00 203 -30.00 132.50
zha 23.00 61.00 NULL 5.70 201 -50.00 39.70
ma 91.00 56.10 7.03 97.00 202 -43.00 117.13
ta 33.00 NULL 34.00 91.00 NULL NULL NULL