来自带有 IN 子句的两个表的 mysql 查询请求

Question

我有 mysql 表:

<强>1。 ms_fields

id name

1  Color
2  Gender

<强>2。 ms_fields_values

id fieldsid     value   ordering

1   1            White   0
2   1            Black   1
3   1            Orange  2
4   1            Green   3
5   1            Blue    4
6   2            Male    0
7   2            Female  1 

<强>3。 ms_conn_products_to_fields_values

productid   fields_values_id

    9       5
    9       7
    10      5
    10      6
    11      2
    11      7
    12      1
    12      7

我有一个问题:

SELECT V.id, V.fieldsid, V.value, V.ordering, COUNT(P.productid) AS count
FROM `ms_fields_values` V
LEFT JOIN `ms_conn_products_to_fields_values` P ON P.fields_values_id = V.id
WHERE V.fieldsid =1
AND P.productid
IN (9, 11, 12)
GROUP BY V.id
ORDER BY `ordering` ASC

此查询将返回我:

id  fieldsid    value   ordering count

1   1           White   0         1
2   1           Black   1         1
5   1           Blue    4         1

如何更改获取此数据的请求:

id  fieldsid    value   ordering count

1   1           White   0         1
2   1           Black   1         1
*3   1           Orange  2             0* // row needed
*4   1           Green   3             0* // row needed
5   1           Blue    4         1

Answer 1

试试这个:

SELECT V.id, V.fieldsid, V.value, V.ordering, COUNT(P.productid) AS COUNT
FROM `ms_fields_values` V
LEFT JOIN `ms_conn_products_to_fields_values` P ON P.fields_values_id = V.id AND P.productid IN (9, 11, 12)
WHERE V.fieldsid =1
GROUP BY V.id
ORDER BY `ordering` ASC