请CSDN师傅帮忙 万分感谢
视图A (预算)
MK002(年),MK003(科目),MK004(部门编号),KM006(预算金额)
2013 660112 3036 10175.88
2013 660122 3036 1466.20
视图B
MK002(年),MK005(月),MK003(科目),MK004(部门编号),TB007(发生金额),MK006(预算金额),TOTAL(预算总金额)
2013 03 660111 3036 27.00 .00 .00
2013 04 660111 3036 313.00 .00 .00
2013 05 660111 3036 144.20 .00 .00
2013 01 660112 3036 1166.44 .00 .00
2013 01 660112 3036 1166.44 1044.24 1044.24
求用查询语句实现查询出如下数据:
如果 视图B的MK002=视图A的MK002,视图B的MK004=视图A的MK004,视图B.TB007>0,但同时视图B的科目MK003不在视图A的科目MK003里(就是这年这个部门这个科目没有预算,但发生了费用)的数据和 视图B字段TOTAL>0的数据查询出来
表B
MK002(年),MK005(月),MK003(科目),MK004(部门编号),TB007(发生金额),MK006(预算金额),TOTAL(预算总金额)
2013 03 660111 3036 27.00 .00 0
2013 04 660111 3036 313.00 .00 0
2013 05 660111 3036 144.20 .00 0
2013 01 660112 3036 1166.44 1044.24 1044.24
万分感谢
[解决办法]
create table #tableA
(MK002 varchar(6), MK003 varchar(6), MK004 varchar(6), KM006 decimal(9,2))
insert into #tableA
select '2013', '660112', '3036', 10175.88 union all
select '2013', '660122', '3036', 1466.20
create table #tableB
(MK002 varchar(6), MK005 varchar(6), MK003 varchar(6), MK004 varchar(6), TB007 decimal(9,2), MK006 decimal(9,2), TOTAL decimal(9,2))
insert into #tableB
select '2013', '03', '660111', '3036', 27.00, .00, .00 union all
select '2013', '04', '660111', '3036', 313.00, .00, .00 union all
select '2013', '05', '660111', '3036', 144.20, .00, .00 union all
select '2013', '01', '660112', '3036', 1166.44, .00, .00 union all
select '2013', '01', '660112', '3036', 1166.44, 1044.24, 1044.24
SELECT * FROM #tableB B
WHERE (NOT EXISTS
(
SELECT 1
FROM #tableA a
WHERE a.MK002 = b.MK002
AND a.MK004 = b.MK004
AND a.MK003 = b.MK003
)
and b.TB007 > 0)
OR b.TOTAL > 0
/*
201303660111303627.000.000.00
2013046601113036313.000.000.00
2013056601113036144.200.000.00
20130166011230361166.441044.241044.24
*/
