mysql - 从表列中计算值

Question

首先我有一个表，它的枢轴看起来像这样

pivot_product_Id | productsxx_Id | category_Id | subcategory_Id | color_Id
---------------------------------------------------------------------------
       1         |        1        |      1    |      1         |   1
       2         |        1        |      1    |      1         |   2
       3         |        3        |      1    |      1         |   3
       4         |        4        |      1    |      2         |   4
       5         |        4        |      1    |      2         |   5
       6         |        2        |      2    |      4         |   6
       7         |        5        |      2    |      5         |   7

我有这样的颜色表

color_Id   | color   |  color2
------------------------------------------
    1      |  black   | white
    2      |  blue    | orange
    3      |  white   | black
    4      |  purple  | black
    5      |  black   | green
    6      |  red     | black

我的问题是在类别 ID 1 或 2 中...存在多少种黑色？从 color 和 color2 列计数

我尝试了类似的方法，但没有得到我想要的结果，需要帮助来创建正确的查询。

if(isset($shoes_post_var) || isset($nightwear_post_var)|| isset($outwear_post_var)){

                    $query3 = "SELECT count(*)
                FROM pivot
            JOIN category ON
                pivot.category_Id = category.category_Id
            JOIN subcategory ON
                pivot.subcategory_Id = subcategory.subcategory_Id       
            JOIN color ON
                pivot.color_Id = color.color_Id

            JOIN productsxx ON      
                pivot.productsxx_Id = productsxx.productsxx_Id
            WHERE  
             color IN ('$black') 
            or
             color2 IN ('$black')
            AND
            category IN ('$shoes_post_var','$nightwear_post_var','$outwear_post_var')
            GROUP BY pivot.color_Id  ASC ";
        $query5 = mysql_query($query3)or die(mysql_errno());
            $total = mysql_result($query5, 0);  
        echo ' '.'('.$total.')';}

Answer 1

可能的解决方案

SELECT COUNT(*) total
  FROM pivot 
 WHERE category_id IN (1, 2)
   AND color_id IN
( 
  SELECT color_id
    FROM color
   WHERE color = 'black' 
      OR color2 = 'black'
)

这里是SQLFiddle 演示