34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TBA(COL1 VARCHAR(10),COL2 MONEY)
INSERT TBA
SELECT '现金' , 1000 UNION ALL
SELECT '银行' , 1000 UNION ALL
SELECT '支票' , 1000
--DROP TABLE TBB
CREATE TABLE TBB(COL1 VARCHAR(10) ,COL2 VARCHAR(10),COL3 MONEY)
INSERT TBB
SELECT '2009.03', '现金' , 1000 UNION ALL
SELECT '2009.03', '银行' , 1000 UNION ALL
SELECT '2009.04', '现金' , 1000
--SELECT * FROM TBA
SELECT T.COL1,T1.* FROM TBA AS T1,
(SELECT DISTINCT COL1,COL3 FROM TBB)AS T
WHERE T.COL3=T1.COL2
declare @A table(Type varchar(10),Amount int)
insert into @A select '现金',1000
insert into @A select '银行',1000
insert into @A select '支票',1000
declare @B table(Date varchar(10),Type varchar(10),Amount int)
insert into @B select '2009.03','现金',1000
insert into @B select '2009.03','银行',1000
insert into @B select '2009.04','现金',1000
insert into @B(Date,Type,Amount)
select
t.Date,A.Type,A.Amount
from
@A A,(select distinct Date from @B) t
where
not exists(select 1 from @B where Date=t.Date and Type=A.Type)
select * from @B order by Date,Type
/*
Date Type Amount
---------- ---------- -----------
2009.03 现金 1000
2009.03 银行 1000
2009.03 支票 1000
2009.04 现金 1000
2009.04 银行 1000
2009.04 支票 1000
*/
insert into B(日期,类型,金额)
select
t.日期,A.类型,A.金额
from
A,(select distinct 日期 from B) t
where
not exists(select 1 from B where 日期=t.日期 and 类型=A.类型)