表1名:rd
1 2
2 2
3 56
4 61 表2名:rds
0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00这两个表连接,我用语句:select * from rd left outer join rds on rd.id=rds.id 得出这样的结果
1 2 0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00而我要将仓库与批次相同的记录只显示一行(如果可以,取日期最大的那行)。请高手赐教!在线等待,谢谢!
1 2
2 2
3 56
4 61 表2名:rds
0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00这两个表连接,我用语句:select * from rd left outer join rds on rd.id=rds.id 得出这样的结果
1 2 0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00而我要将仓库与批次相同的记录只显示一行(如果可以,取日期最大的那行)。请高手赐教!在线等待,谢谢!
*
from
rd a
left join
rds b
on
a.id=b.id
and
b.[date]=(select max([date] from rds where 仓库=b.仓库 and 批次=t.批次)
*
from
rd a
left join
rds b
on
a.id=b.id
and
b.[date]=(select max([date] from rds where 仓库=b.仓库 and 批次=b.批次)
1 2
2 2
3 56
4 61 表2名:rds
存货编码 批次 ID,日期
0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00这两个表连接,我用语句:select * from rd left outer join rds on rd.id=rds.id 得出这样的结果
id,仓库,存货编码,批次, ID 日期
1 2 0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00而我要将仓库与批次相同的记录只显示一行(如果可以,取日期最大的那行)。请高手赐教!在线等待,谢谢!
select *
from rd t left outer join rds s on t.id=s.id
where not exists(select 1 from rds where id=s.id and 日期>s.日期 )
go
create table [ta] (id int,仓库 int)
insert into [ta]
select 1,2 union all
select 2,2 union all
select 3,56 union all
select 4,61
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (存货编码 nvarchar(24),批次 numeric(12,1),ID int,日期 datetime)
insert into [tb]
select '0601MBIN0008',20081111001.0,1,'2010-01-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,2,'2010-02-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,3,'2010-03-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,4,'2010-04-01 00:00:00'select *
from ta t inner join tb s on t.id=s.id
and not exists(select 1 from ta where 仓库=t.仓库 and ID>t.ID )
/*
id 仓库 存货编码 批次 ID 日期
----------- ----------- ------------------------ --------------------------------------- ----------- -----------------------
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00.000
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00.000
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00.000
*/
你的方法可以实现,非常感谢,帮了我一个大忙。
居然不看我写的 我是学习SF MM的
我要的结果就是关连出来的记录,有批次和仓库相同的就显示一行就可以。
应该是不能以ID为条件的
id,仓库,存货编码,批次, ID 日期
1 2 0601MBIN0008 20081111001.0 1 2010-01-01 00:00:00
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00
这个结果,如果批次和仓库相同的,就只显示一行拜托了。
go
create table [ta] (id int,仓库 int)
insert into [ta]
select 1,2 union all
select 2,2 union all
select 3,56 union all
select 4,61
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (存货编码 nvarchar(24),批次 numeric(12,1),ID int,日期 datetime)
insert into [tb]
select '0601MBIN0008',20081111001.0,1,'2010-01-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,2,'2010-02-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,3,'2010-03-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,4,'2010-04-01 00:00:00'select
*
from
ta a
join
tb b
on
a.id=b.id
and
a.id=(select max(id) from ta where 仓库=a.仓库)
order by 1/*id 仓库 存货编码 批次 ID 日期
----------- ----------- ------------------------ -------------- ----------- ------------------------------------------------------
2 2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00.000
3 56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00.000
4 61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00.000(所影响的行数为 3 行)
*/
go
create table [ta] (id int,仓库 int)
insert into [ta]
select 1,2 union all
select 2,2 union all
select 3,56 union all
select 4,61
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (存货编码 nvarchar(24),批次 numeric(12,1),ID int,日期 datetime)
insert into [tb]
select '0601MBIN0008',20081111001.0,1,'2010-01-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,2,'2010-02-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,3,'2010-03-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,4,'2010-04-01 00:00:00'select a.仓库,b.* into #
from ta a left outer join tb b on a.id=b.id
select * from # t
where not exists(select 1 from # where 仓库=t.仓库 and 批次=t.批次 and 日期>t.日期)
drop table #
/*
仓库 存货编码 批次 ID 日期
----------- ------------------------ --------------------------------------- ----------- -----------------------
2 0601MBIN0008 20081111001.0 2 2010-02-01 00:00:00.000
56 0601MBIN0008 20081111001.0 3 2010-03-01 00:00:00.000
61 0601MBIN0008 20081111001.0 4 2010-04-01 00:00:00.000
*/
(
select * from rd left outer join rds on rd.id=rds.id
)select * from w_a A where A.ID=(select max(id) from w_a B where A.仓库=b仓库 and A.批号=B.批号)
你在14楼回复的也不行。得不出结果是在过滤出来的结果,如果批次和仓库相同,就取日期最大的那一行。
go
create table [ta] (id int,仓库 int)
insert into [ta]
select 1,2 union all
select 2,2 union all
select 3,56 union all
select 4,61
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (存货编码 nvarchar(24),批次 numeric(12,1),ID int,日期 datetime)
insert into [tb]
select '0601MBIN0008',20081111001.0,1,'2010-01-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,2,'2010-02-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,3,'2010-03-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,4,'2010-04-01 00:00:00'
select * from (
select a.仓库,b.*
from ta a left outer join tb b on a.id=b.id)t
where not exists(select 1 from (select a.仓库,b.*
from ta a left outer join tb b on a.id=b.id)b
where b.仓库=t.仓库 and b.批次=t.批次 and b.日期>t.日期)
select * from (select a.仓库,b.*
from ta a left outer join tb b on a.id=b.id)
t
where not exists(select 1 from (select a.仓库,b.*
from ta a left outer join tb b on a.id=b.id)as t1
where t1.仓库=t.仓库 and t1.批次=t.批次 and t1.日期>t.日期)
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (id int,仓库 int)
insert into [ta]
select 1,2 union all
select 2,2 union all
select 3,56 union all
select 4,61
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (存货编码 nvarchar(24),批次 numeric(12,1),ID int,日期 datetime)
insert into [tb]
select '0601MBIN0008',20081111001.0,1,'2010-01-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,2,'2010-02-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,3,'2010-03-01 00:00:00' union all
select '0601MBIN0008',20081111001.0,4,'2010-04-01 00:00:00'
select id,仓库,存货编码,批次,日期 from(
select a.*,b.存货编码,b.批次,b.日期,MAX(日期) over(partition by 仓库,存货编码) as max_日期 from ta a left outer join [tb] b on a.id=b.id
) a where 日期=max_日期
(4 行受影响)(4 行受影响)
id 仓库 存货编码 批次 日期
----------- ----------- ------------------------ --------------------------------------- -----------------------
2 2 0601MBIN0008 20081111001.0 2010-02-01 00:00:00.000
3 56 0601MBIN0008 20081111001.0 2010-03-01 00:00:00.000
4 61 0601MBIN0008 20081111001.0 2010-04-01 00:00:00.000(3 行受影响)...
修改一下select id,仓库,存货编码,批次,日期 from(
select a.*,b.存货编码,b.批次,b.日期,MAX(日期) over(partition by 仓库,批次) as max_日期 from ta a left outer join [tb] b on a.id=b.id
) a where 日期=max_日期
在关键字 'over' 附近有语法错误。
max(id) as ID
,max(仓库) as 仓库
,存货编码
,批次
,max(日期) as 日期
from
(select * from ta
inner join tb on ta.id=tb.ID) t
group by
存货编码,批次返回:
4 61 0601MBIN0008 20081111001.0 2010-04-01 00:00:00.000
select
max(id) as ID
,max(仓库) as 仓库
,存货编码
,批次
,max(日期) as 日期
from
(select * from ta
inner join tb on ta.id=tb.ID) t
group by
存货编码,批次
返回结果:
61 0601MBIN0008 20081111001.0 2010-04-01 00:00:00.000