求SQL 語句

[勉励前行]

表：　
Year int ,Month int, GID int, Price int
年,月,商品,采購價格
求全部商品，最後一次采購的價格。

[shoter193]

請問像這種([Year]> a.[Year]
or [Year]=a.[Year]
and [Month]>a.[Month]
自己和自己比較是怎麼個比法？？

[echiynn]

...

這不是自己和自己比,這是子查詢與主查詢比

沒看到比較運算符右邊的列是帶了表名前綴的嗎,那個表別名"a"就是主表,第一句"select * from 表 a"就已經給主查詢的表取了別名了

[昵称被占用了]

表：　
Year int ,Month int, GID int, Price int

select * from 表 a
where not exists (
select 1 from 表
where GID=a.GID
and ([Year]> a.[Year]
or [Year]=a.[Year]
and [Month]>a.[Month]
)
)

[echiynn]

[Year]=a.[Year] and [Month]>a.[Month]
好象還應該套個括號,呵呵

[昵称被占用了]

or:

select * from 表 a
where [Year]=(select max([Year]) from 表 where GID=a.GID)
and [Month]=(select max([Month]) from 表 where GID=a.GID and [Year]=a.[Year])

[昵称被占用了]

and 优先级高于 or 可以不必加括号

[leo_lesley]

----是这样么？
select *
from table_name t
inner join (select GID , c=max(Year),c1=max(Month) from table_name group by GID) t1
on t.GID=t1.GID and t.Year=t1.c and t.Month=t1.c1

[勉励前行]

Haiwer(海阔天空) ，　leo_lesley(leo) 的方法
哪個效率高點呢？

[caibaoying]

select * from 表 a where not exists (select 1 from 表 where GID=a.GID and [Year]> a.[Year] or ([Year]=a.[Year] and [Month]>a.[Month]))

[caibaoying]

个人倾向于用`` leo_lesley(leo)的```

[勉励前行]

leo_lesley(leo) 的應該不對。
c=max(Year),c1=max(Month) 不能匹配最後的一個年月