Estimated number of products in two categories



  • Table product_filter There are two pillars: product_id and filter_id♪ We need a number. product_idfor which there's a record filter_id = 1 and filter_id = 5
    How do you do that?



  • In the first request, the lines covered by the condition shall be removed. and lines f=1 and lines f=5)

    The second request is to calculate the number of lines of the requested request (the requested is identical to the first).

    http://sqlfiddle.com/#!9/876f0/1

    MySQL 5.6 Schema Setup:

    create table t (p int, f int);
    

    insert into t values
    (1, 1)
    ,(2, 1)
    ,(1, 5)
    ,(3, 1)
    ,(4, 5)
    ,(1, 1)
    ,(2, 5)
    ;

    Query 1:

    select *
    from t as t1
    join t as t2
    on t1.p = t2.p
    where t1.f = 1 and t2.f = 5
    group by t1.p

    http://sqlfiddle.com/#!9/876f0/1/0 :

    | p | f | p | f |
    |---|---|---|---|
    | 1 | 1 | 1 | 5 |
    | 2 | 1 | 2 | 5 |

    Query 2:

    select count() from
    (
    select t1.p
    from t as t1
    join t as t2
    on t1.p = t2.p
    where t1.f = 1 and t2.f = 5
    group by t1.p
    ) as t0

    http://sqlfiddle.com/#!9/876f0/1/1 :

    | count() |
    |----------|
    | 2 |


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2