MYSQL-选择满足许多计数条件的行 [英] MYSQL - Select rows fulfilling many count conditions

问题描述

我有一个包含4列的表格(比方说表格)-

I have a Table (let's say table) with 4 columns -

Product_ID, Designer, Exclusive, Handloom

ID是主键，其他3列的值为0或1.

ID is the primary key and other 3 columns have values 0 or 1.

例如-Designer中的0表示该产品不是设计者，而1表示它是设计者.

Eg - 0 in Designer means the product is not designer and 1 means it is designer.

我想写下一个查询，从给定的16行中选择6行，这些行具有>=4 Designer，>=4 Exclusive和>=4 Handloom产品.从顶部开始有6行，而不仅限于顶部6行(由于可以有多种组合，因此我们将从顶部开始)

I want to write down a query to select 6 rows out of the given 16 rows, having >=4 Designer, >=4 Exclusive and >=4 Handloom products. 6 rows starting from the top, not limited to top 6 rows only (As there can be multiple combinations, so we will start from top)

我无法找到明确的解决方案.下面是Table表的数据:

I am not able to find out a clear solution to this. Below is the data of the Table table:

   Code         Designer    Exclusive   Handloom
    A           1           0           1
    B           1           0           0
    C           0           0           1
    D           0           1           0
    E           0           1           0
    F           1           0           1
    G           0           1           0
    H           0           0           0
    I           1           1           1
    J           1           1           1
    K           0           0           1
    L           0           1           0
    M           0           1           0
    N           1           1           0
    O           0           1           1
    P           1           1           0

如果我手动解决，结果将是带有Product_ID: a,f,i,j,n,o

If I solve it manually, the result would be rows with Product_ID: a,f,i,j,n,o

推荐答案

这会变得非常慢吗?

select t1.Code, t2.Code, t3.Code, t4.Code, t5.Code, t6.Code
from Table t1, Table t2, Table t3, Table t4, Table t5, Table t6
where   t1.Code < t2.Code and t2.Code < t3.Code
    and t3.Code < t4.Code and t4.Code < t5.Code and t5.Code < t6.Code
    and t1.Designer  + t2.Designer  + t3.Designer  + t4.Designer  + t5.Designer  + t6.Designer  >= 4
    and t1.Exclusive + t2.Exclusive + t3.Exclusive + t4.Exclusive + t5.Exclusive + t6.Exclusive >= 4
    and t1.Handloom  + t2.Handloom  + t3.Handloom  + t4.Handloom  + t5.Handloom  + t6.Handloom  >= 4
order by t1.Code, t2.Code, t3.Code, t4.Code, t5.Code, t6.Code
limit 1;

您似乎想要满足您条件的产品代码的字母顺序最低的组合.我不知道6向交叉联接是否会成为性能问题，但我认为根据要求和可能的合理起点，它是正确的.

It seems you want the alphabetically lowest combination of product codes that satisfy your condition. I don't know if the 6-way cross join is going to be a performance issue but I believe it's correct per the requirement and possibly a reasonable starting point.

根据事先对数据的了解程度，可以通过消除仅设置单个标志的行(从而减少行组合的总数)来提高性能.

Depending on how much you know about the data in advance you might improve performance by eliminating rows (and thus the total number of row combinations) where only a single flag is set.

我在SQL Server上尝试过此操作.没有一行的限制，它将返回157个匹配项. AFIJNP是69.包含B的共有7个，其中第一个是ABDIJO.如果我将查询更改为首先在t6.Code上排序，则结果为ADEFIJ.所以我不知道我是否理解您的要求的一部分.

I tried this on SQL Server. Without the limit of one row it returns 157 matches. AFIJNP is number 69. There are 7 that include B, the first of which is ABDIJO. If I change the query to sort first on t6.Code then the result is ADEFIJ. So I don't know if I understand that part of your requirement.

这篇关于MYSQL-选择满足许多计数条件的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！