注意: 上面如果数据有完全相同的就不行了,那就要用临时表:select *,IDENTITY(int,1,1) as id into #tmp from 表Aselect a,b from #tmp x where id=(select min(id) from #tmp where a=x.a)
注意: 上面如果数据有完全相同的就不行了,那就要用临时表:select *,IDENTITY(int,1,1) as id into #tmp from 表Aselect a,b from #tmp x where id=(select min(id) from #tmp where a=x.a)drop table #tmp
select a.id,max(a.time),max(a.code) from a join b on a.id=b.id where b.flag=1 group by a.id
TO yesyesyes()条件中没有对A.Code的描述,所以这个语句得到的结果不是预期结果。 谢谢!!
SELECT ID, MAX([TIME]) AS [TIME] FROM (SELECT A.ID, A.TIME, A.CODE FROM A LEFT OUTER JOIN B ON A.ID = B.ID WHERE B.FLAG = 1) C GROUP BY ID
你虽无a.code的条件,但你的输出有,所以只好加上
谢谢大家,根据大家提供的方法,我写出了下面这个语句,解决了我的问题 不知道大家还有什么优化的意见 下午再结贴,都有分 谢谢!!Select ID,Time,Code From A L Where Time= (Select Max(A.Time) From A INNER JOIN B ON A.ID=B.ID And B.Flag=1 Where A.ID=L.ID Group By A.ID)
select distinct a,* from A
Select ID,Time,Code From A L Where Time= (Select Max(A.Time) From A INNER JOIN B ON A.ID=B.ID And B.Flag=1 Where A.ID=L.ID )
上面如果数据有完全相同的就不行了,那就要用临时表:select *,IDENTITY(int,1,1) as id into #tmp from 表Aselect a,b from #tmp x
where id=(select min(id) from #tmp where a=x.a)
上面如果数据有完全相同的就不行了,那就要用临时表:select *,IDENTITY(int,1,1) as id into #tmp from 表Aselect a,b from #tmp x
where id=(select min(id) from #tmp where a=x.a)drop table #tmp
其实我需要从两个表中查询信息
A(ID,Time,Code)
5100000704008701 2004-05-20 15:46:47.000 007
5100000704008801 2004-05-25 11:35:58.000 007
5100000704008901 2004-05-28 14:51:49.000 007
5105300704009001 2004-06-11 13:00:00.000 018
5105300704009001 2004-06-11 11:00:00.000 001
5105300704009001 2004-06-10 13:00:00.000 003
5105300704009001 2004-06-10 12:00:00.000 002
5105300704009001 2004-06-10 11:00:00.000 001B(ID,Flag)
5100000704008701 1
5100000704008801 0
5105300704009001 1查询条件:(A.ID=B.ID) And (B.Flag=1) And (A中ID相同的记录取A.Time最大记录)
查询结果:
5100000704008701 2004-05-20 15:46:47.000 007
5105300704009001 2004-06-11 13:00:00.000 018
能否用一条查询语句实现?谢谢!!
group by a.id
谢谢!!
FROM (SELECT A.ID, A.TIME, A.CODE
FROM A LEFT OUTER JOIN B ON A.ID = B.ID
WHERE B.FLAG = 1) C
GROUP BY ID
不知道大家还有什么优化的意见
下午再结贴,都有分
谢谢!!Select ID,Time,Code From A L
Where Time=
(Select Max(A.Time) From A
INNER JOIN B ON A.ID=B.ID And B.Flag=1
Where A.ID=L.ID
Group By A.ID)
Where Time=
(Select Max(A.Time) From A
INNER JOIN B ON A.ID=B.ID And B.Flag=1
Where A.ID=L.ID
)
能解决这个问题非常感谢大家。
特别感谢 rea1gz(冒牌realgz V0.1) 提供了思路。