求SQL查询语句，分数立等可取

[swazn_yj]

表Items：
CREATE TABLE [dbo].[Items](
[id] [int] IDENTITY(1,1) NOT NULL,
[i_name] [varchar](200) NULL,
[i_manage] [varchar](100) NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]

表I_Track：
CREATE TABLE [dbo].[I_Track](
[id] [int] IDENTITY(1,1) NOT NULL,
[i_id] [int] NULL,
[addtime] [smalldatetime] NULL CONSTRAINT [DF_I_Track_addtime] DEFAULT (getdate()),
CONSTRAINT [PK_I_Track] PRIMARY KEY CLUSTERED
(
[id] ASC
)) ON [PRIMARY]


表Items里存的是项目名称,项目经理，
表I_Track里存的是项目跟踪情况，通过I_ID跟表Items的ID对应

现在想要一个SQL列出所有的项目名称、项目经理、最后跟踪时间

我写了一个，结果是有几条跟踪记录就会有几条记录被列出来，如果加上后面注释掉的条件，没有写过跟踪记录的项目又不会被列出来，请大家帮我改一下。
select a.id,a.i_name,a.i_calling,a.i_manage,a.addtime,b.addtime as uptime from Items a left join i_track b
on b.i_id=a.id order by uptime desc--where b.id=(select top 1 id from i_track where i_id=a.id order by addtime desc)

[nzperfect]

来点数据？看不太明白.

select a.* ,
(select max([addtime]) from [I_Track] where [i_id]=a.id) as Lasttime
from [Items] as a

[--小F--]

给出测试数据

[claro]

帮顶
下班

[-狙击手-]

select a.id,a.i_name,a.i_calling,a.i_manage,a.addtime,b.addtime as uptime 
from Items a left join i_track b 
on b.i_id=a.id 
and b.id=(select top 1 id from i_track where i_id=a.id order by addtime desc)

[xiequan2]

select i_name,i_manage,addtime from Items a left join I_Track b on a.id=b.i_id and not exists(select 1 from I_Track  where i_id=b.i_id and addtime>b.addtime)

[--小F--]

[Quote=引用 4 楼 happyflystone 的回复:]
SQL codeselect a.id,a.i_name,a.i_calling,a.i_manage,a.addtime,b.addtime as uptime
from Items a left join i_track b
on b.i_id=a.id
and b.id=(select top 1 id from i_track where i_id=a.id order by addtime desc)
[/Quote]

[swazn_yj]

4楼的不对，我都说了，没有写过跟踪记录的列不出来

[ScottYj]

1楼的可以,看点清楚点可以这样写:
select a.i_name,a.i_manage,b.LastAddTime from Items a left outer join
(
select i_id,max(addtime) as LastAddTime from i_track group by i_id
) b on a.id=b.i_id

[ScottYj]

4楼也可以的

[ScottYj]

[Quote=引用 7 楼 swazn_yj 的回复:]
4楼的不对，我都说了，没有写过跟踪记录的列不出来
[/Quote]

就改为右连接啦:

select a.i_name,a.i_manage,b.LastAddTime from Items a right outer join
(
select i_id,max(addtime) as LastAddTime from i_track group by i_id
) b on a.id=b.i_id

[ai_li7758521]

??

select a.id,a.i_name,a.i_calling,a.i_manage,a.addtime,b.addtime as uptime 
from Items a join i_track b 
on b.i_id=a.id and b.id=(select top 1 id from i_track where i_id=a.id order by addtime desc)

[Zoezs]

select a.id,a.i_name,a.i_calling,a.i_manage,a.addtime,b.addtime as uptime 
from Items a left join i_track b 
on b.i_id=a.id 
and b.id=(select top 1 id from i_track where i_id=a.id order by addtime desc)

[ScottYj]

没有写过跟踪记录的列不出来

select a.i_name,a.i_manage,b.LastAddTime from  Items a right outer join 
( 
select i_id,max(addtime) as LastAddTime from i_track group by i_id 
) b on a.id=b.i_id