求大神解答sql中group by和max一起使用的语句

[dy410543731]

有一个表r_sale_memcard，内容大概如下

我想得到同一个memcardno在wareid相同的情况下最大的acccdate 的那一条明细，小弟sql技术粗浅只会写这个：
select max(accdate) as accdate,memcardno,wareid
from r_sale_memcard
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
group by memcardno,wareid
但是我想要的是 r_sale_memcard.* 全部明细 跪求大神解答

[LongRui888]

可以用这个：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

[dy410543731]

引用 1 楼 yupeigu 的回复:

可以用这个：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

语句貌似有点问题看不太懂。d.*的 d表哪里来的

[LongRui888]

引用 2 楼 dy410543731 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 可以用这个：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

语句貌似有点问题看不太懂。d.*的 d表哪里来的 [/quote] 忘记写表别名了：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard d
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

[dy410543731]

引用 3 楼 yupeigu 的回复:

[quote=引用 2 楼 dy410543731 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 可以用这个：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

语句貌似有点问题看不太懂。d.*的 d表哪里来的 [/quote] 忘记写表别名了：

select *
from 
(
select d.*
       row_number() over(partition by memcardno,wareid order by accdate desc) rn
from r_sale_memcard d
where wareid<>'TOTAL' and saleqty<>'0' and wareid<>'TOTAL' and saleqty<>'0'
)v
where v.rn = 1

[/quote] 谢谢大神 完美解决我的问题了