create table ta(a varchar(2),c varchar(2),m int)
insert into ta select
'AA','CC',30 union select --AA,CC,50代表的都是数据
'BB','CC',15 ---BB,CC,20代表的都是数据 create table tb (a varchar(2),c varchar(2), d varchar(3), f varchar(3),n int) ---现在没有数据(等待插入)
create table tc(a varchar(2),c varchar(2),d varchar(3),f varchar(3),s int)
insert into tc select
'AA','CC','DD1','FF1',10 union select
'AA','CC','DD2','FF2',25 union select
'AA','CC','DD3','FF3',15 union select
'BB','CC','DD1','FF1',10 union select
'BB','CC','DD2','FF2',10
go
insert tb
select a,c,d,f,case when ((select sum(m) from ta where a=a.a and c = a.c)-
isnull((select sum(s) from tc where a=a.a and c = a.c and d<=a.d and f<=a.f),0)) > 0
then s else
case when s +( (select sum(m) from ta where a=a.a and c = a.c) -
isnull((select sum(s) from tc where a=a.a and c = a.c and d<=a.d and f<=a.f),0)) > 0
then s +( (select sum(m) from ta where a=a.a and c = a.c) -
isnull((select sum(s) from tc where a=a.a and c = a.c and d<=a.d and f<=a.f),0))
else 0 end
end
from tc aselect * from tb
/*a c d f n
---- ---- ---- ---- -----------
AA CC DD1 FF1 10
AA CC DD2 FF2 20
AA CC DD3 FF3 0
BB CC DD1 FF1 10
BB CC DD2 FF2 5(所影响的行数为 5 行)
*/drop table ta,tb,tc
解决方案 »
- 求教:触发器根据另一个表某个字段变化触发~~~~~~~~
- 本人巨笨,谁能给我彻底讲明白SQL Server的“Windows验证”的使用方法?看了十多本书没明白,它们讲法基本都是一样的。
- 遍历多个数据库,获取存储过程名等于某个值的列表
- 如何在Qurey Analyzer中查询通过ODBC联接的Access数据表,
- 修改已添加的链接服务器,急!!!!急!!在线等待!!
- 高手们,给解决些问题吧
- 统计记录数,对高手应该不难,来看看吧
- 初始化数据库的问题,(表之间的关系及用户问题)
- 在一个表中选出最新的100条记录,再从这100条中选出10条
- sp_changeobjectowner能改掉一个存储过程的所有者,怎样才能一次性改掉所有存储过程的所有者?急!
- 求购二手的 邹建的 《中文版SQL Server2000开发与管理应用实例 》,you
- 数据表行列转换问题
1.就是在程序中根据一个字段取出其他字段的的顺序
就像根据'AA','CC'可以取出'AA','CC','DD1','FF1',10
'AA','CC','DD2','FF2',25
'AA','CC','DD3','FF3',15
这三组数据
就是楼上写的那个比较的顺序也已因该是这样的.对吗?
2.我是莱鸟
select sum(s) from tc where a=a.a and c = a.c and d<=a.d and f<=a.f
什么意思?不是很理解..
顶楼上的..看来实现没有什么问题了...谢谢了准备给分..我这边因为是现场服务器.,没法自己测试.
还麻烦楼上多多看看..
insert into ta select
'AA','CC',30 union select --AA,CC,50代表的都是数据
'BB','CC',10 ---BB,CC,20代表的都是数据 create table tb (a varchar(2),c varchar(2), d varchar(3), f varchar(3),n int) ---现在没有数据(等待插入)
create table tc(a varchar(2),d varchar(3),f varchar(3),s int)
insert into tc select
'AA','DD1','FF1',8 union select
'AA','DD2','FF2',16 union select
'AA','DD3','FF3',15 union select
'BB','DD1','FF1',8 union select
'BB','DD2','FF2',8
goselect * from ta
select * from tc
insert tb
select distinct a.a,b.c,a.d,a.f,case when ((select sum(m) from ta where a=a.a)-
isnull((select sum(s) from tc where a=a.a and d<=a.d and f<=a.f),0)) > 0
then s else
case when s +((select sum(m) from ta where a=a.a) -
isnull((select sum(s) from tc where a=a.a and d<=a.d and f<=a.f),0)) > 0
then s +((select sum(m) from ta where a=a.a) -
isnull((select sum(s) from tc where a=a.a and d<=a.d and f<=a.f),0))
else 0 end
end
from tc a,ta b
select * from ta
select * from tc
select * from tb
drop table ta,tb,tc