Mysql限制列值重复N次 [英] Mysql Limit column value repetition N times

问题描述

我有两张表

I have two tables

Customer (idCustomer, ecc.. ecc..)
Comment (idCustomer, idComment, ecc.. ecc..)

显然这两个表连接在一起，例如 p>

obviously the two table are joined together, for example

SELECT * FROM Comment AS co
  JOIN Customer AS cu ON cu.idCustomer = co.idCustomer

使用此选项，我选择与Customer相关联的所有评论，但现在我想限制评论的数量2 max评论每个客户。

With this I select all comment from that table associated with is Customer, but now I wanna limit the number of Comment by 2 max Comment per Customer.

我看到的第一件事是使用 GROUP BY cu.idCustomer ，但它仅限制1条评论每个客户，但我想每个客户2评论。

The first thing I see is to use GROUP BY cu.idCustomer but it limits only 1 Comment per Customer, but I wanna 2 Comment per Customer.

我该如何做到这一点？

How can I achieve that?

推荐答案

MySQL中的一个选项是服务器端变量。例如：

One option in MySQL is server-side variables. For example:

set @num := 0, @customer := -1;

select  *
from    (
        select  idCustomer
        ,       commentText
        ,       @num := if(@customer = idCustomer, @num + 1, 1) 
                    as row_number
        ,       @customer := idCustomer
        from    Comments
        order by 
                idCustomer, PostDate desc
        ) as co
join    Customer cu
on      co.idCustomer = cu.idCustomer
where   co.row_number <= 2

这篇关于Mysql限制列值重复N次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！