开发者

How can I write an SQL version of this LINQ query?

As shown at: How to li开发者_如何学Cmit an SQL query to return at most one of something?

I have the following tables:

Platforms

  • PlatformID
  • Name

Products

  • ProductID
  • Name

Releases

  • ID
  • ProductID
  • PlatformID
  • Version
  • ReleaseDate

The releases table stores each release of a software product for a particular platform. I want to write a query to return the latest release of each product for each platform in the Releases table. I.E. If I release product X version 2.0 on platforms A, B and C, there will be 3 records in the Releases table:

  • one for product X version 2.0 on platform A released on 2010-11-17
  • one for product X version 2.0 on platform B released on 2010-11-17
  • one for product X version 2.0 on platform C released on 2010-11-17

So, given a product ID, how do I obtain this information? I tried using GROUP BY like the LINQ solution I used but apparently I can't translate LINQ to SQL as well as .NET can.


Pedantic:

Select
  PR.Name,
  PL.Name,
  REL.Version,
  REL.ReleaseDate
From
  Releases As REL
  Inner Join Products As PR
    On REL.ProductID = PR.ProductID
  Inner Join Platforms As PL
    On REL.PlatformID = PL.PlatformID
Where
  REL.ID = (Select Top 1 Latest.ID
            From Releases As Latest
            Where Latest.PlatformID = REL.PlatformID
              And Latest.ProductID = REL.ProductID
            Order By Latest.ReleaseDate Desc)

Should also work (assuming you never decrease versions):

Select
  PR.Name,
  PL.Name,
  Max(REL.Version),
  Max(REL.ReleaseDate)
From
  Releases As REL
  Inner Join Products As PR
    On REL.ProductID = PR.ProductID
  Inner Join Platforms As PL
    On REL.PlatformID = PL.PlatformID
Group By
  PR.Name,
  PL.Name


Did you know LINQ's DataContext class has a Log property?

Since you already have a LINQ query, why not peek behind the curtain? :-)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜