34,591
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a table(bm varchar(20))
INSERT @a SELECT '1'
UNION ALL SELECT '2'
UNION ALL SELECT '3'
UNION ALL SELECT '+1+2'
DECLARE @b table(sj varchar(20))
INSERT @b SELECT '2008-1-1'
declare @c table(zh varchar(20),sj varchar(20),sjj int)
INSERT @c SELECT '1' ,'2008-1-1' ,100
UNION ALL SELECT '1' ,'2008-1-2', 200
UNION ALL SELECT '2' ,'2008-1-1', 200
UNION ALL SELECT '2' ,'2008-1-2', 300
UNION ALL SELECT '3' ,'2008-1-1', 400
SELECT bm,aa.sj,sjj=isnull(sjj,(SELECT sum(sjj) FROM @c WHERE sj=aa.sj AND charindex('+'+zh+'+',bm+'+')>0))
from
(SELECT * FROM @a a CROSS JOIN @b b)aa
LEFT JOIN
@c cc
ON bm=zh AND aa.sj=cc.sj
--result
/*bm sj sjj
-------------------- -------------------- -----------
1 2008-1-1 100
2 2008-1-1 200
3 2008-1-1 400
+1+2 2008-1-1 300
(所影响的行数为 4 行)
*/
……
union all
select '+1+2',时间,sum(数据) as 数据 where 编码 in(1,2) group by 时间
select A.编码,B.时间,sum(C.数据) as 数据 from A
left outer join B on 1=1
left outer join C on A.编码 like '%'+C.站号+'%' and C.时间=B.时间 group by A.编码,B.时间
--TRY::
CREATE TABLE XX(A VARCHAR(300))
INSERT INTO XX
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '+1+2'
CREATE TABLE YY(B DATETIME)
INSERT INTO YY VALUES('2008-01-01')
CREATE TABLE ZZ(C VARCHAR(20),B DATETIME,DATA INT)
INSERT INTO ZZ
SELECT '1','2008-01-01',100 UNION ALL
SELECT '1','2008-01-02',200 UNION ALL
SELECT '2','2008-01-01',200 UNION ALL
SELECT '2','2008-01-02',300 UNION ALL
SELECT '3','2008-01-01',400
CREATE VIEW V_X
AS
SELECT xxtable.TT,YY.B,SUM(ZZ.DATA)MM FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) TT,CAST(replace((CASE WHEN LEFT(A,1)<>'+' THEN '<r>'+A ELSE
stuff(a,1,1,'<r>') END+'</r>'),'+','</r><r>')AS XML) col FROM XX ) xxtable
CROSS APPLY
(SELECT V=t.col.value('.','VARCHAR(10)') FROM xxtable.col.nodes('//r') AS t(col)
) b,YY,ZZ WHERE B.V=ZZ.C AND YY.B=ZZ.B
GROUP BY xxtable.TT,YY.B
SELECT * FROM V_X