--测试--测试数据 create table a(name varchar(10),date varchar(10),val1 int) insert a select 'aaa','2004-4-2',20 union all select 'bbb','2004-4-3',30create table b(name varchar(10),date varchar(10),val2 int) insert b select 'aaa','2004-4-1',20 union all select 'aaa','2004-4-2',10 union all select 'bbb','2004-4-3',40 go--查询 select name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,a.val1,b.val2 from a full join b on a.name=b.name and a.date=b.date go--删除测试 drop table a,b/*--测试结果 name date val1 val2 ---------- ---------- ----------- ----------- aaa 2004-4-1 NULL 20 aaa 2004-4-2 20 10 bbb 2004-4-3 30 40(所影响的行数为 3 行) --*/
thanks!! it works!!!再问一下,如何将 c表中的所有NULL,替换为0 ? name date val1 val2 ---------- ---------- ----------- ----------- aaa 2004-4-1 NULL 20 aaa 2004-4-2 20 10 bbb 2004-4-3 30 40
select name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,val1=isnull(a.val1,0) ,val2=isnull(b.val2,0) from a full join b on a.name=b.name and a.date=b.date
--查询 select name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,val1=isnull(a.val1,0) ,val2=isnull(b.val2,0) ,val3=isnull((select sum(val1) from a aa where date<=a.date),0) -isnull((select sum(val2) from b bb where date<=b.date),0) from a full join b on a.name=b.name and a.date=b.date
--测试--测试数据 create table a(name varchar(10),date varchar(10),val1 int) insert a select 'aaa','2004-4-2',20 union all select 'bbb','2004-4-3',30create table b(name varchar(10),date varchar(10),val2 int) insert b select 'aaa','2004-4-1',20 union all select 'aaa','2004-4-2',10 union all select 'bbb','2004-4-3',40 go--查询 select name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,val1=isnull(a.val1,0) ,val2=isnull(b.val2,0) ,val3=isnull((select sum(val1) from a aa where date<=a.date),0) -isnull((select sum(val2) from b bb where date<=b.date),0) from a full join b on a.name=b.name and a.date=b.date go--删除测试 drop table a,b/*--测试结果 name date val1 val2 val3 ---------- ---------- ----------- ----------- ----------- aaa 2004-4-1 0 20 -20 aaa 2004-4-2 20 10 -10 bbb 2004-4-3 30 40 -20(所影响的行数为 3 行) --*/
select name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,val1=isnull(a.val1,0) ,val2=isnull(b.val2,0) ,val3=isnull((select sum(val1) from a aa where name=a.name and date<=a.date),0) -isnull((select sum(val2) from b bb where name=b.name and date<=b.date),0) from a full join b on a.name=b.name and a.date=b.date
其实,主要目的是计算费用。table a : 记录增加费用 table b : 记录使用费用 table c : 记录用户的费用明细。要求: 1.根据表a,b.可以计算出表c 2.每天表a,b的数据变化能反映到表c中。上面的是我的解决思路,可能不太简介。请问,有更好的办法吗?谢谢的热情帮助!!!
--测试--错误!!!create table a(name varchar(10),date varchar(10),val1 int) insert a select 'aaa','2004-4-2',20 union all select 'bbb','2004-4-3',30 union all select 'bbb','2004-4-4',30 《============= | create table b(name varchar(10),date varchar(10),val2 int) | insert b select 'aaa','2004-4-1',20 | union all select 'aaa','2004-4-2',10 | union all select 'bbb','2004-4-3',40 2004-4-4 数据为0 | union all select 'bbb','2004-4-5',30 <===========================| goselect name=isnull(a.name,b.name) ,date=isnull(a.date,b.date) ,val1=isnull(a.val1,0) ,val2=isnull(b.val2,0) ,val3=isnull((select sum(val1) from a aa where name=a.name and date<=a.date),0) -isnull((select sum(val2) from b bb where name=b.name and date<=b.date),0) from a full join b on a.name=b.name and a.date=b.date ============================================== name date val1 val2 val3 ---------- ---------- ----------- ----------- ----------- aaa 2004-4-1 0 20 -20 aaa 2004-4-2 20 10 -10 bbb 2004-4-3 30 40 -10 bbb 2004-4-5 0 30 -70 bbb 2004-4-4 30 0 60
create table a(name varchar(10),date varchar(10),val1 int)
insert a select 'aaa','2004-4-2',20
union all select 'bbb','2004-4-3',30create table b(name varchar(10),date varchar(10),val2 int)
insert b select 'aaa','2004-4-1',20
union all select 'aaa','2004-4-2',10
union all select 'bbb','2004-4-3',40
go--查询
select name=isnull(a.name,b.name)
,date=isnull(a.date,b.date)
,a.val1,b.val2
from a full join b on a.name=b.name and a.date=b.date
go--删除测试
drop table a,b/*--测试结果
name date val1 val2
---------- ---------- ----------- -----------
aaa 2004-4-1 NULL 20
aaa 2004-4-2 20 10
bbb 2004-4-3 30 40(所影响的行数为 3 行)
--*/
it works!!!再问一下,如何将 c表中的所有NULL,替换为0 ?
name date val1 val2
---------- ---------- ----------- -----------
aaa 2004-4-1 NULL 20
aaa 2004-4-2 20 10
bbb 2004-4-3 30 40
---------- ---------- ----------- -----------
aaa 2004-4-1 0 (<==NULL) 20
aaa 2004-4-2 20 10
bbb 2004-4-3 30 40===========================
,date=isnull(a.date,b.date)
,val1=isnull(a.val1,0)
,val2=isnull(b.val2,0)
from a full join b on a.name=b.name and a.date=b.date
val3 = val1 - val2.
并且val3会作为节余影响以后的数据。
请问,如何通过SQL语句实现?name date val1 val2 val3 = val1 - val2
---------- ---------- ----------- ----------- ----------------------
aaa 2004-4-1 0 20 0-20 = -20
aaa 2004-4-2 20 10 -20(节余) + 20 -10 = -10
bbb 2004-4-3 30 40 -10(节余) + 30 -40 = -20
select name=isnull(a.name,b.name)
,date=isnull(a.date,b.date)
,val1=isnull(a.val1,0)
,val2=isnull(b.val2,0)
,val3=isnull((select sum(val1) from a aa where date<=a.date),0)
-isnull((select sum(val2) from b bb where date<=b.date),0)
from a full join b on a.name=b.name and a.date=b.date
create table a(name varchar(10),date varchar(10),val1 int)
insert a select 'aaa','2004-4-2',20
union all select 'bbb','2004-4-3',30create table b(name varchar(10),date varchar(10),val2 int)
insert b select 'aaa','2004-4-1',20
union all select 'aaa','2004-4-2',10
union all select 'bbb','2004-4-3',40
go--查询
select name=isnull(a.name,b.name)
,date=isnull(a.date,b.date)
,val1=isnull(a.val1,0)
,val2=isnull(b.val2,0)
,val3=isnull((select sum(val1) from a aa where date<=a.date),0)
-isnull((select sum(val2) from b bb where date<=b.date),0)
from a full join b on a.name=b.name and a.date=b.date
go--删除测试
drop table a,b/*--测试结果
name date val1 val2 val3
---------- ---------- ----------- ----------- -----------
aaa 2004-4-1 0 20 -20
aaa 2004-4-2 20 10 -10
bbb 2004-4-3 30 40 -20(所影响的行数为 3 行)
--*/
条件给错了!
bbb不会继承aaa节余!!!
因改为如下。name date val1 val2 val3 = val1 - val2
---------- ---------- ----------- ----------- ----------------------
aaa 2004-4-1 0 20 0-20 = -20
aaa 2004-4-2 20 10 -20(节余) + 20 -10 = -10
bbb 2004-4-3 30 40 err!==》0(节余) + 30 -40 = -10
,date=isnull(a.date,b.date)
,val1=isnull(a.val1,0)
,val2=isnull(b.val2,0)
,val3=isnull((select sum(val1) from a aa where name=a.name and date<=a.date),0)
-isnull((select sum(val2) from b bb where name=b.name and date<=b.date),0)
from a full join b on a.name=b.name and a.date=b.date
要将2004-4-4数据表a和b,加入已经存在的表c中(不是从头算起)。
SQL语句如何实现?table a: name date val1
aaa 2004-4-4 20
bbb 2004-4-4 30table b: name date val2
aaa 2004-4-4 20
bbb 2004-4-4 10table_c(old)
name date val1 val2 val3 = val1 - val2
---------- ---------- ----------- ----------- ----------------------
aaa 2004-4-1 0 20 0-20 = -20
aaa 2004-4-2 20 10 -20(节余) + 20 -10 = -10
bbb 2004-4-3 30 40 err!==》0(节余) + 30 -40 = -10=============================================================================table_c(NEW)
name date val1 val2 val3 = val1 - val2
---------- ---------- ----------- ----------- ----------------------
aaa 2004-4-1 0 20 0-20 = -20
aaa 2004-4-2 20 10 -20(节余) + 20 -10 = -10
aaa 2004-4-4 20 20 -10(节余) + 20 -20 = -10bbb 2004-4-3 30 40 0(节余) + 30 -40 = -10
bbb 2004-4-4 30 10 -10(节余) + 30 -10 = 10
table b : 记录使用费用
table c : 记录用户的费用明细。要求:
1.根据表a,b.可以计算出表c
2.每天表a,b的数据变化能反映到表c中。上面的是我的解决思路,可能不太简介。请问,有更好的办法吗?谢谢的热情帮助!!!
insert a select 'aaa','2004-4-2',20
union all select 'bbb','2004-4-3',30
union all select 'bbb','2004-4-4',30 《=============
|
create table b(name varchar(10),date varchar(10),val2 int) |
insert b select 'aaa','2004-4-1',20 |
union all select 'aaa','2004-4-2',10 |
union all select 'bbb','2004-4-3',40 2004-4-4 数据为0 |
union all select 'bbb','2004-4-5',30 <===========================|
goselect name=isnull(a.name,b.name)
,date=isnull(a.date,b.date)
,val1=isnull(a.val1,0)
,val2=isnull(b.val2,0)
,val3=isnull((select sum(val1) from a aa where name=a.name and date<=a.date),0)
-isnull((select sum(val2) from b bb where name=b.name and date<=b.date),0)
from a full join b on a.name=b.name and a.date=b.date
==============================================
name date val1 val2 val3
---------- ---------- ----------- ----------- -----------
aaa 2004-4-1 0 20 -20
aaa 2004-4-2 20 10 -10
bbb 2004-4-3 30 40 -10
bbb 2004-4-5 0 30 -70
bbb 2004-4-4 30 0 60