有如下表A,B
表A
fdate id sr sj
2012-08-01 010 500.00 50.00
2012-08-01 011 800.00 80.00
2012-08-01 012 970.00 97.00
2012-08-02 010 840.00 84.00
2012-08-04 011 870.00 87.00
2012-08-09 018 970.00 97.00
表B
fdate id cb
2012-08-01 010 300.00
2012-08-02 010 430.00
2012-08-09 010 150.00
2012-08-01 011 340.00
2012-08-01 012 500.00
2012-08-03 015 200.00
请问如何写语句使两个表综合 得到结果如下
fdate id sr sj cb
2012-08-01 010 500.00 50.00 300.00
2012-08-01 011 800.00 80.00 340.00
2012-08-01 012 970.00 97.00 500.00
2012-08-02 010 840.00 84.00 430.00
2012-08-03 015 0.00 0.00 200.00
2012-08-04 011 870.00 87.00 0.00
2012-08-09 010 00.00 00.00 150.00
2012-08-09 018 970.00 97.00 0.00
表A
fdate id sr sj
2012-08-01 010 500.00 50.00
2012-08-01 011 800.00 80.00
2012-08-01 012 970.00 97.00
2012-08-02 010 840.00 84.00
2012-08-04 011 870.00 87.00
2012-08-09 018 970.00 97.00
表B
fdate id cb
2012-08-01 010 300.00
2012-08-02 010 430.00
2012-08-09 010 150.00
2012-08-01 011 340.00
2012-08-01 012 500.00
2012-08-03 015 200.00
请问如何写语句使两个表综合 得到结果如下
fdate id sr sj cb
2012-08-01 010 500.00 50.00 300.00
2012-08-01 011 800.00 80.00 340.00
2012-08-01 012 970.00 97.00 500.00
2012-08-02 010 840.00 84.00 430.00
2012-08-03 015 0.00 0.00 200.00
2012-08-04 011 870.00 87.00 0.00
2012-08-09 010 00.00 00.00 150.00
2012-08-09 018 970.00 97.00 0.00
解决方案 »
- 数据库dataset效率问题--help
- 如何解决数据库中同一事务的阻塞?
- 事务中嵌套执行存储过程的问题,一直没搞明白
- 【SQL2008】无法打开明确指定的数据库?出现基础结构错误?
- SQL查询问题,又纠结了两天了,麻烦各位帮看一下,谢谢!
- 这个sql语句是一本书上的例子,可我却执行不了,报"从字符串转换为 smalldatetime 数据类型时发生语法错误。".什么原因?
- (在线等)怎么样取得一个时间范围内的数据?
- 向各位请教一个问题
- sql數據如何自動備份到客戶端?
- 如何加密foxpro的数据库
- 急!!!!!bcp工具,无法导入远程数据库?
- 如果有一张表每天的数据量都是几千万,导致查询很慢,应该怎么处理?
GO
CREATE TABLE testA
(
fdate DATETIME,
id VARCHAR(10),
sr DECIMAL(6,2),
sj DECIMAL(6,2)
)
INSERT INTO testA
SELECT '2012-08-01', '010', 500.00, 50.00
UNION ALL
SELECT '2012-08-01', '011' ,800.00 ,80.00
UNION ALL
SELECT '2012-08-01', '012' ,970.00 ,97.00
UNION ALL
SELECT '2012-08-02', '010' ,840.00, 84.00
UNION ALL
SELECT '2012-08-04', '011' ,870.00 ,87.00
UNION ALL
SELECT '2012-08-09', '018' ,970.00 ,97.00
CREATE TABLE testB
(
fdate DATETIME,
id VARCHAR(10),
cb DECIMAL(6,2)
)
INSERT INTO testB
SELECT '2012-08-01', '010', 300.00
UNION ALL
SELECT '2012-08-02','010' ,430.00
UNION ALL
SELECT '2012-08-09' ,'010' ,150.00
UNION ALL
SELECT '2012-08-01','011' ,340.00
UNION ALL
SELECT '2012-08-01', '012' ,500.00
UNION ALL
SELECT '2012-08-03','015', 200.00
SELECT COALESCE(a.fdate,b.fdate) AS fdate,COALESCE(a.id,b.id) AS id,ISNULL(a.sr,0.00) AS sr,ISNULL(a.sj,0.00) AS sj,ISNULL(b.cb,0.00) AS cb
FROM testA A FULL JOIN testB B ON a.fdate=b.fdate AND a.id=b.id
--> 测试数据:@表A
declare @表A table([fdate] datetime,[id] varchar(3),[sr] numeric(5,2),[sj] numeric(4,2))
insert @表A
select '2012-08-01','010',500.00,50.00 union all
select '2012-08-01','011',800.00,80.00 union all
select '2012-08-01','012',970.00,97.00 union all
select '2012-08-02','010',840.00,84.00 union all
select '2012-08-04','011',870.00,87.00 union all
select '2012-08-09','018',970.00,97.00--> 测试数据:@表B
declare @表B table([fdate] datetime,[id] varchar(3),[cb] numeric(5,2))
insert @表B
select '2012-08-01','010',300.00 union all
select '2012-08-02','010',430.00 union all
select '2012-08-09','010',150.00 union all
select '2012-08-01','011',340.00 union all
select '2012-08-01','012',500.00 union all
select '2012-08-03','015',200.00select
convert(varchar(10),isnull(a.fdate,b.fdate),120) as fdate,
isnull(a.id,b.id) as id,
isnull(a.sr,0) as sr,
isnull(a.sj,0) as sj,
isnull(b.cb,0) as cb
from @表A a full join @表B b on a.fdate=b.fdate and a.id=b.id
order by 1,2
/*
fdate id sr sj cb
---------- ---- ----------- -------- -----------
2012-08-01 010 500.00 50.00 300.00
2012-08-01 011 800.00 80.00 340.00
2012-08-01 012 970.00 97.00 500.00
2012-08-02 010 840.00 84.00 430.00
2012-08-03 015 0.00 0.00 200.00
2012-08-04 011 870.00 87.00 0.00
2012-08-09 010 0.00 0.00 150.00
2012-08-09 018 970.00 97.00 0.00
*/