MySQL-返回唯一记录

Question

我有两个表:

Table: products
-----------------------------------------------------
|  id  |      name                |  other fields...
-----------------------------------------------------
|  20   |      Intel i5 4690K     |   ...
|  21   |      AMD A6-7400K       |   ...
|  23   |      AMD A8-3850        |   ...
|  ...  |      ...                |   ...

和表product_details:

Table: products_details
-----------------------------------------------
|  id  |  product_id  |  option_id  |  value  |
-----------------------------------------------
|  1   |      20      |     2478    |  55032  |
|  2   |      20      |     2482    |  55051  |
|  3   |      21      |     2478    |  54966  |
|  4   |      21      |     2482    |  55050  |
|  5   |      22      |     2478    |  55032  |
|  5   |      22      |     2482    |  55050  |
-----------------------------------------------

2478 = Number of Cores
2482 = Manufacturer

55032 = 4 cores
55050 = 6 cores

我正在尝试获取产品只有 4 核且来自 AMD 的记录:

Select
  product_details.product_id,
  products.name,
  product_details.option_id,
  product_details.value
From
  product_details Inner Join
  products On product_details.product_id = products.id
Where
  product_details.option_id In (2478, 2482) And
  product_details.value In (55032, 55050)

上面的 SQL 给了我双记录

product_id  name                 option_id  value   
20          Intel i5 4690K       2478       55032
21          AMD A6-7400K         2482       55050
23          AMD A8-3850K         2478       55032
23          AMD A8-3850K         2482       55050

显然，它不适用于 IN。有什么想法吗？

Answer 1

要获得 AMD 4 CORES，此查询工作正常。

您需要对 product_details 进行两次内部联接才能获取 manufacturer 以及核心数。

这是SQLFiddle Demo

Select
          product_details.product_id,
          products.name,
          product_details.option_id,
          product_details.value
        From
          product_details 
          Inner Join
          products On product_details.product_id = products.id AND product_details.option_id=2478
          INNER JOIN product_details P2 ON P2.product_id = product_details.product_id AND P2.option_id=2482
        Where
          product_details.value = 55032 And
          P2.value = 55050

希望这有帮助。