表A
ID bianh number suliang time1
1051 2 60035006 4 2010-11-8 18:50:40
1052 1 60021478 1 2010-11-8 18:50:48
1053 3 60021475 2 2010-11-8 18:50:04
1054 3 60025065 4 2010-11-8 18:50:12
1055 2 60029019 4 2010-11-8 18:51:27
1056 2 60025026 8 2010-11-8 18:51:46表B
ID bianh number suliang time2
1051 3 60035006 3 2010-11-8 18:51:40
1052 3 60021478 4 2010-11-8 18:51:48
1053 2 60021475 1 2010-11-8 18:52:04
1054 1 60025065 6 2010-11-8 18:52:12
1055 3 60029019 4 2010-11-8 18:52:27
1056 2 60029019 5 2010-11-8 18:52:46我要从中查出表 表A 和 表B中bianh和suliang相同记录中的Time1时间最大那条数据的suliang加上表B中Time1大于表A.time1的suliang的总和 的和
要怎么查??求解。
ID bianh number suliang time1
1051 2 60035006 4 2010-11-8 18:50:40
1052 1 60021478 1 2010-11-8 18:50:48
1053 3 60021475 2 2010-11-8 18:50:04
1054 3 60025065 4 2010-11-8 18:50:12
1055 2 60029019 4 2010-11-8 18:51:27
1056 2 60025026 8 2010-11-8 18:51:46表B
ID bianh number suliang time2
1051 3 60035006 3 2010-11-8 18:51:40
1052 3 60021478 4 2010-11-8 18:51:48
1053 2 60021475 1 2010-11-8 18:52:04
1054 1 60025065 6 2010-11-8 18:52:12
1055 3 60029019 4 2010-11-8 18:52:27
1056 2 60029019 5 2010-11-8 18:52:46我要从中查出表 表A 和 表B中bianh和suliang相同记录中的Time1时间最大那条数据的suliang加上表B中Time1大于表A.time1的suliang的总和 的和
要怎么查??求解。
1056 2 60025026 8 2010-11-8 18:51:46表B
ID bianh number suliang time2
1054 2 60025026 6 2010-11-8 18:52:12
1057 1 61032084 2 2010-11-8 18:52:12
1055 2 60025026 4 2010-11-8 18:52:27
1056 2 60025026 5 2010-11-8 18:52:46
查出来就是8+6+4+5=23查询条件就是是表A和表B的bianh和number值相同,同时表B的Time2大于表1的Time1的suliang
符合条件的相加 我用sql语句来表达一下吧。。
select (max(表A.suliang)+ Sum(表B.suliang) ) AS SL FROM 表A,表B Where 表A.number=表B.number and 表A.bianh=表B.bianh and 表B.time2>max(表A.time1)大概就是这么个意思
create table tbA(ID int,bianh int,number nvarchar(10),suliang int,time1 datetime)
insert into tbA select 1051,2,'60035006',4,'2010-11-8 18:50:40'
insert into tbA select 1052,1,'60021478',1,'2010-11-8 18:50:48'
insert into tbA select 1053,3,'60021475',2,'2010-11-8 18:50:04'
insert into tbA select 1054,3,'60025065',4,'2010-11-8 18:50:12'
insert into tbA select 1055,2,'60029019',4,'2010-11-8 18:51:27'
insert into tbA select 1056,2,'60025026',8,'2010-11-8 18:51:46'
create table tbB(ID int,bianh int,number nvarchar(10),suliang int,time2 datetime)
insert into tbB select 1051,3,'60035006',3,'2010-11-8 18:51:40'
insert into tbB select 1052,3,'60021478',4,'2010-11-8 18:51:48'
insert into tbB select 1053,2,'60021475',1,'2010-11-8 18:52:04'
insert into tbB select 1054,1,'60025065',6,'2010-11-8 18:52:12'
insert into tbB select 1055,3,'60029019',4,'2010-11-8 18:52:27'
insert into tbB select 1056,2,'60029019',5,'2010-11-8 18:52:46'
go
select a.bianh,a.number,max(a.suliang)+sum(isnull(b.suliang,0))as sl
from tbA a left join tbB b on a.bianh=b.bianh and a.number=b.number and b.time2>a.time1
group by a.bianh,a.number
go
drop table tbA,tbB
/*
bianh number sl
----------- ---------- -----------
3 60021475 2
1 60021478 1
2 60025026 8
3 60025065 4
2 60029019 9
2 60035006 4(6 行受影响)*/
create table tbA(ID int,bianh int,number nvarchar(10),suliang int,time1 datetime)
insert into tbA select 1056,2,'60025026',8,'2010-11-8 18:51:46'
create table tbB(ID int,bianh int,number nvarchar(10),suliang int,time2 datetime)
insert into tbB select 1054,2,'60025026',6,'2010-11-8 18:52:12'
insert into tbB select 1057,1,'61032084',2,'2010-11-8 18:52:12'
insert into tbB select 1055,2,'60025026',4,'2010-11-8 18:52:27'
insert into tbB select 1056,2,'60025026',5,'2010-11-8 18:52:46'
go
select a.bianh,a.number,max(a.suliang)+sum(isnull(b.suliang,0))as sl
from tbA a left join tbB b on a.bianh=b.bianh and a.number=b.number and b.time2>a.time1
group by a.bianh,a.number
go
drop table tbA,tbB
/*
bianh number sl
----------- ---------- -----------
2 60025026 23(1 行受影响)
*/
FROM tba a JOIN tbB b
ON a.bianh=b.bianh
AND a.number=b.number
AND datediff(second,a.time1,b.time2)>0
你写的好专业啊。、、、
不过我开始打错了。。
那个MAX我要改成LAST
但是我执行的时候报下面错误。。'Last' 不是可以识别的 内置函数名称。
你的这句话:
查询条件就是是表A和表B的bianh和number值相同,
怎么理解?
结合刚才说的,能理解成在表A中,以相同的bianh和number中time1最大的行中的suliang,与表B中bianh和number与表A中bian和number相同的但time2大于表A那个记录的time1的值求和.
是吗?
ID bianh number suliang time1
75 1 16011252 12 2010-10-4 11:11:36
77 1 16011253 12 2010-09-4 11:11:55
676 2 16011516 7 2010-11-1 16:48:56
92 1 16011554 1 2010-10-3 11:22:07
57 1 16011555 2 2010-10-4 11:03:59
47 1 16011563 27 2010-08-1 11:00:56
91 2 16011563 7 2010-11-5 11:21:08
34 1 16011565 34 2010-11-1 10:31:26
679 2 16011565 18 2010-11-0 16:50:03
681 2 16011569 14 2010-11-1 16:50:26
59 1 16011764 8 2010-11-1 11:04:26
93 2 16011764 24 2010-11-8 11:22:37
90 1 16011775 13 2010-11-8 11:20:49
934 1 16011775 32 2010-11-8 18:04:11
表B
ID bianh number suliang time1
6548 1 16011013 24 2010-11-19 1:18:01
6549 1 16011252 24 2010-11-19 1:18:01
110 2 16011252 12 2010-11-14 19:16:23
1770 1 16011252 12 2010-11-11 8:06:45
2985 1 16011565 12 2010-11-2 12:17:49
2908 1 16011253 11 2010-11-2 12:17:47
1769 1 16011555 4 2010-11-11 8:06:45
111 2 16011253 12 2010-11-14 19:16:23
6554 1 16011253 24 2010-11-19 1:18:04
6552 1 16011254 24 2010-11-19 1:18:01
112 2 16011254 12 2010-11-14 19:16:23
2790 1 16011254 11 2010-11-2 12:17:42
3057 1 16011555 24 2010-11-2 12:17:52
97 2 16011516 24 2010-1-14 19:00:30
1445 1 16011516 24 2010-11-11 5:50:18