需要这两个表的 SQL 选择查询帮助

发布于 2024-09-26 04:51:24 字数 1329 浏览 1 评论 0原文

表格捕获图像: http://img844.imageshack.us/img844/6213/99730337.jpg

------------ PositionTable------------
ID    ContentFK   Position
11     100               1
12     101               1
13     104               2
14     102               2
15     103               2
16     105               3
17     106               3
18     107               2


----------Content Table ------------
ContentID   UpdateDate  Title
100          11.10.2009    Aol
101          12.10.2009    Microsoft
102          12.10.2009    e-Bay
103          12.11.2009    google
104          16.11.2009    novell
105          17.11.2009    asus
106          16.11.2009    nokia
107          11.11.2009    samsung

谁能帮我解决两个表之间的问题 我的场景。

按编号排序为位置 1、2、3。然而,许多组只列出一条记录 (按位置 ASC 排序(位置:1,2,3)

,PositionTable.ContentFK = ContentTable.ContentID 通过 ContentTablo 中上次更新的 UpdateDate

我如何获得与结果相同的列表。

p.Postion     p.ID            p.ContentFK     c.UpdateDate      c.Title

1             12              101             12.10.2009        Microsoft 
2             13              104             16.11.2009        novell 
3             16              105             17.11.2009        asus

谢谢大家,

Table Capture image : http://img844.imageshack.us/img844/6213/99730337.jpg

------------ PositionTable------------
ID    ContentFK   Position
11     100               1
12     101               1
13     104               2
14     102               2
15     103               2
16     105               3
17     106               3
18     107               2


----------Content Table ------------
ContentID   UpdateDate  Title
100          11.10.2009    Aol
101          12.10.2009    Microsoft
102          12.10.2009    e-Bay
103          12.11.2009    google
104          16.11.2009    novell
105          17.11.2009    asus
106          16.11.2009    nokia
107          11.11.2009    samsung

Who can help me to the question between the two tables
My scenario.

Sort by number as Position 1,2,3. However, a number of the groups to list only one record
(order by Position ASC (Position: 1,2,3)

With PositionTable.ContentFK = ContentTable.ContentID
by UpdateDate of last update in ContentTablo

How I can get list same as result.

p.Postion     p.ID            p.ContentFK     c.UpdateDate      c.Title

1             12              101             12.10.2009        Microsoft 
2             13              104             16.11.2009        novell 
3             16              105             17.11.2009        asus

Thanks all,

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

神经暖 2024-10-03 04:51:24

这应该可以做到:

编辑:原始代码是因为我认为你的日期是 MM.DD.YYYY。意识到您的日期是 DD.MM.YYYY 并相应地调整了代码。

编辑 2:根据 UpdateDate 数据类型的反馈更改了答案。

;with cteMaxDate as (
    select p.Position, MAX(c.UpdateDate) as MaxDate
        from PositionTable p
            inner join ContentTable c
                on p.ContentFK = c.ContentID
        group by p.Position
)
select p.Position, p.ID, p.ContentFK, c.UpdateDate, c.Title
    from cteMaxDate m
        inner join PositionTable p
            on m.Position = p.Position
        inner join ContentTable c
            on p.ContentFK = c.ContentID
                and m.MaxDate = c.UpdateDate

This should do it:

EDIT: Original code was because I thought your dates were MM.DD.YYYY. Realized your dates were DD.MM.YYYY and adjusted code accordingly.

EDIT 2: Changed answer based on feedback for UpdateDate datatype.

;with cteMaxDate as (
    select p.Position, MAX(c.UpdateDate) as MaxDate
        from PositionTable p
            inner join ContentTable c
                on p.ContentFK = c.ContentID
        group by p.Position
)
select p.Position, p.ID, p.ContentFK, c.UpdateDate, c.Title
    from cteMaxDate m
        inner join PositionTable p
            on m.Position = p.Position
        inner join ContentTable c
            on p.ContentFK = c.ContentID
                and m.MaxDate = c.UpdateDate
耳钉梦 2024-10-03 04:51:24

尝试:

select Position,  ID, ContentFK, UpdateDate, Title
from (
select  p.Position,
    p.ID,
    p.ContentFK,
    c.UpdateDate,
    c.Title,
    row_number() over (partition by p.Position order by p.Position, c.UpdateDate desc) as num
from    Position p inner join
    Content c on p.ContentFK = c.ContentId ) a
where   num = 1

Try:

select Position,  ID, ContentFK, UpdateDate, Title
from (
select  p.Position,
    p.ID,
    p.ContentFK,
    c.UpdateDate,
    c.Title,
    row_number() over (partition by p.Position order by p.Position, c.UpdateDate desc) as num
from    Position p inner join
    Content c on p.ContentFK = c.ContentId ) a
where   num = 1
他夏了夏天 2024-10-03 04:51:24

子查询有点丑陋(并且相关),但这是我能想到的最简单的方法。

假设您的 UpdateDate 列是 DATETIME - 否则您将不得不像 Joe 那样执行某些操作(或者更好的是,将列更改为 DATETIME :-)

select p.Position
    ,p.ID
    ,p.ContentFK
    ,c.UpdateDate
    ,c.Title
from PositionTable p
join ContentTable c
    on p.ContentFK = c.ContentID
    and c.UpdateDate = (select max(ic.UpdateDate) from ContentTable ic
                            join PositionTable ip
                                on ip.ContentFK = ic.ContentID
                            where ip.Position = p.Position)
order by p.Position 

The subquery's a bit ugly (and correlated), but this is the simplest way I can think of doing it.

Assuming your UpdateDate column is DATETIME - otherwise you'll have to do something like Joe did (or, better, change the column to DATETIME :-)

select p.Position
    ,p.ID
    ,p.ContentFK
    ,c.UpdateDate
    ,c.Title
from PositionTable p
join ContentTable c
    on p.ContentFK = c.ContentID
    and c.UpdateDate = (select max(ic.UpdateDate) from ContentTable ic
                            join PositionTable ip
                                on ip.ContentFK = ic.ContentID
                            where ip.Position = p.Position)
order by p.Position 
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文