sql语句查出每组中时间最近的一条记录

香菇大肉饼 2017-03-30 03:37:48

我先关联了三张表得到的数据非常多例如：
name id lastxfrq lastmd je
张三 1 2017-03-24 A01 12.7
张三 1 2017-03-17 A01 29.4
张三 1 2017-03-08 A01 50
张三 1 2017-02-15 A01 19.8
李四 2 2017-03-21 A01 69.9
李四 2 2017-03-04 A01 29
李四 2 2017-02-24 A01 17.5
王五 3 2017-03-24 A01 38.9
王五 3 2017-03-14 A01 9.9

需要用什么语句得到每个姓名只有一条数据（最近时间的）名字，id，日期，门店，金额都要

怎么加在我的关联语句里面
SELECT b.xingming,a.id,a.lastxfrq,a.lastmd,c.je FROM ret_cuxiaoka as a
JOIN ret_gukezl as b
ON a.id=b.id
JOIN retmast as c
ON a.id=c.id
ORDER BY c.id

拜托各位啦

...全文

1044 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

redwoodschu 2017-03-31

打赏
举报

把三张表都贴出来

文盲老顾 2017-03-30

打赏
举报

select * from ( SELECT b.xingming,a.id,a.lastxfrq,a.lastmd,c.je ,c.id,row_number() over(partition by name order by lastxfrq desc) as rowid FROM ret_cuxiaoka as a JOIN ret_gukezl as b ON a.id=b.id JOIN retmast as c ON a.id=c.id ) a where rowid=1 ORDER BY id 上边忘了加限制了

文盲老顾 2017-03-30

打赏
举报

二月十六版主 2017-03-30

打赏
举报

引用 5 楼 shiliyu_shiyu 的回复:

[quote=引用 4 楼 sinat_28984567 的回复:]

SELECT  b.xingming ,
        t.id ,
        t.lastxfrq ,
        t.lastmd ,
        t1.je
FROM    ret_gukezl AS b
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      ret_cuxiaoka a
                      WHERE     a.id = b.id
                      ORDER BY  lastxfrq DESC
                    ) t
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      retmast c
                      WHERE     b.id = c.id
                      ORDER BY  c.id DESC
                    ) t1
ORDER BY t1.id

这里没有命名b表里的字段啊不好意思哈我是新人半路出家的那种所以看不太懂[/quote] 什么意思？

香菇大肉饼 2017-03-30

打赏
举报

引用 4 楼 sinat_28984567 的回复:

SELECT  b.xingming ,
        t.id ,
        t.lastxfrq ,
        t.lastmd ,
        t1.je
FROM    ret_gukezl AS b
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      ret_cuxiaoka a
                      WHERE     a.id = b.id
                      ORDER BY  lastxfrq DESC
                    ) t
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      retmast c
                      WHERE     b.id = c.id
                      ORDER BY  c.id DESC
                    ) t1
ORDER BY t1.id

这里没有命名b表里的字段啊不好意思哈我是新人半路出家的那种所以看不太懂

二月十六版主 2017-03-30

打赏
举报

SELECT  b.xingming ,
        t.id ,
        t.lastxfrq ,
        t.lastmd ,
        t1.je
FROM    ret_gukezl AS b
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      ret_cuxiaoka a
                      WHERE     a.id = b.id
                      ORDER BY  lastxfrq DESC
                    ) t
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      retmast c
                      WHERE     b.id = c.id
                      ORDER BY  c.id DESC
                    ) t1
ORDER BY t1.id

香菇大肉饼 2017-03-30

打赏
举报

引用 1 楼 sinat_28984567 的回复:

试试这样行不

SELECT  b.xingming ,
        t.id ,
        t.lastxfrq ,
        t.lastmd ,
        t1.je
FROM    ret_gukezl AS b
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      ret_cuxiaoka a
                      WHERE     a.id = b.id
                      ORDER BY  lastxfrq DESC
                    ) t
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      retmast c
                      WHERE     a.id = c.id
                      ORDER BY  c.id DESC
                    ) t1
ORDER BY t1.id

无法绑定由多个部分组成的标识符 "a.cardid"。

香菇大肉饼 2017-03-30

打赏
举报

不行啊显示无法绑定由多个部分组成的标识符 "a.cardid"。好像是找不到字段

二月十六版主 2017-03-30

打赏
举报

试试这样行不

SELECT  b.xingming ,
        t.id ,
        t.lastxfrq ,
        t.lastmd ,
        t1.je
FROM    ret_gukezl AS b
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      ret_cuxiaoka a
                      WHERE     a.id = b.id
                      ORDER BY  lastxfrq DESC
                    ) t
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      retmast c
                      WHERE     a.id = c.id
                      ORDER BY  c.id DESC
                    ) t1
ORDER BY t1.id