表一,名称表
FLowID Name
-----------------------------------------------------------
1 小杨
2 小王
-----------------------------------------------------------
表2 活动表
ID OrderDate Address (这里的ID就是表一中的FLowID)
-----------------------------------------------------------
1 2008-10-20 FJ
2 2009-10-11 ZJ
-----------------------------------------------------------
关联以后
FlowID Name OrderDate Address
1 小杨 2008-10-20 FJ
1 小杨 2009-10-11 ZJ
-----------------------------------------------------------
而实际我要的结果只是
FlowID Name OrderDate Address
1 小杨 2009-10-11 ZJ
-----------------------------------------------------------
试过用select top 1 * from 关联表 order by OrderDate desc 这样会显示一条,但是如果数据多了怎么处理?
比如FLowID=2.3.4.5.6.......呢。到时候关联的结果只是上面一些数据显示较早日期,下面一些数据显示较晚日期
不知道各位高手有什么好的解决办法~~~
FLowID Name
-----------------------------------------------------------
1 小杨
2 小王
-----------------------------------------------------------
表2 活动表
ID OrderDate Address (这里的ID就是表一中的FLowID)
-----------------------------------------------------------
1 2008-10-20 FJ
2 2009-10-11 ZJ
-----------------------------------------------------------
关联以后
FlowID Name OrderDate Address
1 小杨 2008-10-20 FJ
1 小杨 2009-10-11 ZJ
-----------------------------------------------------------
而实际我要的结果只是
FlowID Name OrderDate Address
1 小杨 2009-10-11 ZJ
-----------------------------------------------------------
试过用select top 1 * from 关联表 order by OrderDate desc 这样会显示一条,但是如果数据多了怎么处理?
比如FLowID=2.3.4.5.6.......呢。到时候关联的结果只是上面一些数据显示较早日期,下面一些数据显示较晚日期
不知道各位高手有什么好的解决办法~~~
a.*,b.OrderDate,b.Address
from
a
join
(select * from b t where not exists(select 1 from b where Name=t.Name and OrderDate <t.OrderDate ))b
on
a.FlowID=b.FlowID
FROM A,B WHERE A.FOLWID=B.ID AND NOT EXISTS(SELECT 1 FROM B WHERE .........
)这数据看不出来该怎么弄
A.FLowID,
A.NAME,
B.OrderDate,
B.Address
FROM
A,B
WHERE A.FOLWID=B.ID
AND NOT EXISTS(SELECT 1 FROM B WHERE NAME=B.NAME AND DATE<T.DATE)
a.*,
b.OrderDate,
b.Address
from
名称表 a
left join
活动表 b
on
a.FlowID=b.ID
and
not exists(select 1 from 活动表 where id=b.id and OrderDate<b.OrderDate)
活动表的id是不是错了,应该是两个1吧
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-16 14:11:14
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([FLowID] int,[Name] varchar(4))
insert [a]
select 1,'小杨' union all
select 2,'小王'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[OrderDate] datetime,[Address] varchar(2))
insert [b]
select 1,'2008-10-20','FJ' union all
select 1,'2009-10-11','ZJ'
--------------开始查询--------------------------
select
a.*,b.OrderDate,b.Address
from
a
join
(select * from b t where not exists(select 1 from b where id=t.id and OrderDate <t.OrderDate ))b
on
a.FlowID=b.id
----------------结果----------------------------
/* FLowID Name OrderDate Address
----------- ---- ----------------------- -------
1 小杨 2008-10-20 00:00:00.000 FJ(1 行受影响)
*/
inner join 活动表 b
on a.flowID=b.ID order by b.orderDate
FlowID Name OrderDate Address
1 小杨 2008-10-20 FJ
1 小杨 2009-10-11 ZJ
2 小王 2008-10-21 FJ
2 小王 2009-10-20 ZJ
3 小李 2009-10-23 FJ显示结果:
FlowID Name OrderDate Address
1 小杨 2008-10-20 FJ
2 小王 2009-10-20 ZJ
3 小李 2009-10-23 FJ
---------------------------------------------
这个要怎么实现
取每个人最新的时间,这样就不会出现重复值
OrderDate=(SELECT MAX(OrderDate ) FROM VIEWNAME WHERE FLOWID=T.FLOWID )
*
from
vManageName t
where
not exists(select 1 from vManageName where Name=t.Name and OrderDate<t.OrderDate)
select 名称表.*,max(orderdate) from 名称表 inner join 活动表 on 名称表.FLowID=活动表.id
group by 名称表.id,名称表.name