MySql:设置重复列的限制 [英] MySql: Set limit for duplicate column

问题描述

好的，我的表上有一列名为style_number的列.可能有style_number的重复项.我想选择所有内容，但每个样式编号最多只能选择3个.

OK I have a column on my table called style_number. There can be duplicates of style_number. I want to select everything, but only up to 3 for each style number.

例如，如果有4个行的style_number ="style 7"，则仅会选择这4个行中的3个(与3个中的任何3个无关).

So for example if there are 4 rows that have a style_number = "style 7" only 3 of those 4 would be selected (doesn't matter which 3, any 3).

这有意义吗?

我该怎么做?

谢谢！

注意:我正在使用4.1.22

Note: I am using 4.1.22

为了清除一些混乱.我也需要所有其他列，而不仅仅是样式7"，否则我可以轻松地限制为3.但是例如，如果这是我的表所在的位置:

To clear some confusion. I need all the other columns as well, and I need more than just "style 7" or I could easily limit to 3. but for example if this where my table:

style_number |价格| stone_count

style_number | price | stone_count

"style 7" | 300 | 2
"style 7" | 400 | 3
"style 7" | 500 | 4
"style 7" | 600 | 5
"style 8" | 200| 1
"style 8" | 300 | 2

我会得到这样的结果:

"style 7" | 300 | 2
"style 7" | 400 | 3
"style 7" | 500 | 4
"style 8" | 200| 1
"style 8" | 300 | 2

这更有意义吗?

推荐答案

我不是MySQL开发人员，但是我认为这可以为您提供所需的东西.虽然没有表现的保证:)

I'm not a MySQL developer, but I think that this will give you what you want. No promises on performance though :)

SELECT
     T1.id,
     T1.style_number,
     T1.col1,
     T1.col2,
     ...
FROM
     My_Table T1
WHERE
     (
          SELECT
               COUNT(*)
          FROM
               My_Table T2
          WHERE
               T2.style_number = T1.style_number AND
               T2.id < T1.id
     ) < 3

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