php - 选择连接重复

Question

我的数据库有 3 个表，例如:

服务

   id | name | service_id (UNIQUE)    1  | new  |    123    2  | service1  |    543
    

counts

   id | counts | counts_price | service_id    1  | 100    |     200      | 123    5  | 200    |     250      | 123    6  | 300    |     350      | 543

background

   id | background | background_price | service_id    1  |   white    |        50        | 123   12  |   red      |        150        | 123    

My query looks like:

SELECT background.background, services.name, counts.count 
FROM services INNER JOIN background 
ON background.service_id=services.service_id INNER JOIN counts 
ON counts.service_id=services.service_id 
WHERE services.service_id = :service_id

:service_id = 123
在输出中我得到
var_dump

为什么会重复？查询出了什么问题？

<pre>
 array (size=4)
 0 => 
 array (size=3)
  0 => string 'white' (length=5)
  1 => string 'new' (length=3)
  2 => string '200' (length=3)
 1 => 
 array (size=3)
  0 => string 'red' (length=3)
  1 => string 'new' (length=3)
  2 => string '200' (length=3)
 2 => 
 array (size=3)
  0 => string 'white' (length=5)
  1 => string 'new' (length=3)
  2 => string '250' (length=3)
 3 => 
 array (size=3)
  0 => string 'red' (length=3)
  1 => string 'new' (length=3)
  2 => string '250' (length=3)
</pre>

Answer 1

为此添加分组依据。

GROUP BY services.service_id

所以你的最终查询将是:

SELECT background.background, services.name, counts.count, services.service_id 
FROM services INNER JOIN background 
ON background.service_id=services.service_id INNER JOIN counts 
ON counts.service_id=services.service_id 
WHERE services.service_id = :service_id
GROUP BY services.service_id