我正在使用一个MySQL wokrbatch。 我需要结果组明智的限制为 2 条记录。我有如下表格:
|P_ID |Name |Price | p_date |
|1 |T-shirt |500 | 2016-06-10 10:10:25|
|2 |T-shirt |410 | 2016-06-10 10:10:27|
|3 |shirt |450 | 2016-06-10 10:10:30|
|4 |T-shirt |300 | 2016-06-10 10:10:30|
|5 |shirt |500 | 2016-06-10 10:10:25|
|6 |pent |600 | 2016-06-10 10:10:10|
我想要以下结果:
|Name |Price | p_date |
|shirt |450 | 2016-06-10 10:10:30|
|shirt |500 | 2016-06-10 10:10:25|
|T-shirt |300 | 2016-06-10 10:10:30|
|T-shirt |410 | 2016-06-10 10:10:27|
结果基于按名称降序分组,并且只有前 2 条记录
我使用了以下查询
select
name, price, p_date
from
tempData.item_Master
where
name in (select
name
from
tempData.item_Master
group by name
having count(name) > 1)
group by name , p_date
order by name , p_date DESC;
但它给出了按名称分组的结果,其中包含所有记录 我只需要两个顶级记录。
您可以使用相关子查询进行分组限制,但不能使用 count()
和 group by
。 这是一种方法:
select im.name, im.price, im.p_date
from tempData.item_Master im
where (select count(*)
from tempData.item_Master im2
where im2.name = im.name and
im2.p_id <= im.p_id
) <= 2
order by name, p_date DESC;
select name, price, p_date
from tempData.item_Master
where name in
(select name
from tempData.item_Master
group by name
having count(*) > 1)
order by name , p_date DESC;
根据您最后 2 条评论,您可以获得请求 1 和 2,但不能获得请求 3,因为如果您希望拥有 = 2 并按 p_date 分组,则无法获得 3(请参阅您的示例)
select name, price, p_date
from tempData.item_Master
where name in
(select name
from tempData.item_Master
group by name, p_date
having count(*) = 2)
order by name , p_date DESC;