限制 group_concat 函数的结果集只包含实际结果

Question

我有以下查询，大部分工作已经完成，但是当其中一个连接的表返回不同数量的结果时，在group_concat()中返回了太多结果：

select 
    a.sku, a.ek, a.mwst, 
    concat('[[', group_concat('{"offer": ', b.offer, ', "minQuantity": ', b.minQuantity, '}') , ']]') offersA,
    concat('[[', group_concat('{"offer": ', c.offer, ', "minQuantity": ', c.minQuantity, '}') , ']]') offersB,
    concat('[[', group_concat('{"offer": ', d.offer, ', "minQuantity": ', d.minQuantity, '}') , ']]') offersC
from all_prices a 
    left join all_prices_a b on a.sku = b.sku 
    left join all_prices_b c on a.sku = c.sku 
    left join all_prices_c d on a.sku = d.sku
where a.sku in (123,456) 
group by a.sku

我得到的结果是(请运行片段查看表格)，或者查看fiddle。

<table border=1>
<tr>
<td bgcolor=silver class='medium'>sku</td>
<td bgcolor=silver class='medium'>ek</td>
<td bgcolor=silver class='medium'>mwst</td>
<td bgcolor=silver class='medium'>offersA</td>
<td bgcolor=silver class='medium'>offersB</td>
<td bgcolor=silver class='medium'>offersC</td>
</tr>

<tr>
<td class='normal' valign='top'>123</td>
<td class='normal' valign='top'>154.32</td>
<td class='normal' valign='top'>19</td>
<td class='normal' valign='top'>[[{&quot;offer&quot;: 9.65, &quot;minQuantity&quot;: 3},{&quot;offer&quot;: 9.86, &quot;minQuantity&quot;: 1}]]</td>
<td class='normal' valign='top'>[[{&quot;offer&quot;: 9.66, &quot;minQuantity&quot;: 1},{&quot;offer&quot;: 9.66, &quot;minQuantity&quot;: 1}]]</td>
<td class='normal' valign='top'>[[{&quot;offer&quot;: 9.65, &quot;minQuantity&quot;: 1},{&quot;offer&quot;: 9.65, &quot;minQuantity&quot;: 1}]]</td>
</tr>

<tr>
<td class='normal' valign='top'>456</td>
<td class='normal' valign='top'>48.48</td>
<td class='normal' valign='top'>19</td>
<td class='normal' valign='top'>[[{&quot;offer&quot;: 13.30, &quot;minQuantity&quot;: 1},{&quot;offer&quot;: 13.30, &quot;minQuantity&quot;: 1}]]</td>
<td class='normal' valign='top'>[[{&quot;offer&quot;: 13.30, &quot;minQuantity&quot;: 1},{&quot;offer&quot;: 122.00, &quot;minQuantity&quot;: 3}]]</td>
<td class='normal' valign='top'>NULL</td>
</tr>
</table>

正如您所看到的，例如offersB保存了两个结果。

[[{"offer": 9.66, "minQuantity": 1},{"offer": 9.66, "minQuantity": 1}]]

如果两个数量相等，那么给定sku 123 的数据库中只有一个条目，但offersA为此sku提供了两个不同数量的报价：

[[{"offer": 9.65, "minQuantity": 3},{"offer": 9.86, "minQuantity": 1}]] 

我正在使用JavaScript处理后续的结果，所以我可以直接删除重复的结果 - 但是想知道是否存在以下两种更聪明的方式：

a) 查询数据的更妙方法

b) 在查询本身中删除这些重复项的方法

- baao

Answer 1

尝试这个：

SELECT a.sku, a.ek, a.mwst, 
      CONCAT('[[', b.offersA , ']]') offersA,
      CONCAT('[[', c.offersB , ']]') offersB,
      CONCAT('[[', d.offersC , ']]') offersC
FROM all_prices a 
LEFT JOIN ( SELECT b.sku, GROUP_CONCAT('{"offer": ', b.offer, ', "minQuantity": ', b.minQuantity, '}') AS offersA
            FROM all_prices_a b 
            GROUP BY b.sku
          ) AS b ON a.sku = b.sku 
LEFT JOIN ( SELECT c.sku, GROUP_CONCAT('{"offer": ', c.offer, ', "minQuantity": ', c.minQuantity, '}') AS offersB
            FROM all_prices_b c 
            GROUP BY c.sku
          ) AS c ON a.sku = c.sku 
LEFT JOIN ( SELECT d.sku, GROUP_CONCAT('{"offer": ', d.offer, ', "minQuantity": ', d.minQuantity, '}') AS offersC
            FROM all_prices_c d 
            GROUP BY d.sku
          ) AS d ON a.sku = d.sku 
WHERE a.sku IN (123, 456);

请查看此SQL FIDDLE DEMO

::输出::

| sku |    ek | mwst |                                                                   offersA |                                offersB |                                offersC |
|-----|-------|------|---------------------------------------------------------------------------|----------------------------------------|----------------------------------------|
| 123 | 12.48 |   19 | [[{"offer": 12.28, "minQuantity": 1},{"offer": 11.24, "minQuantity": 3}]] | [[{"offer": 12.28, "minQuantity": 1}]] | [[{"offer": 12.28, "minQuantity": 1}]] |
| 456 | 13.24 |   19 |  [[{"offer": 10.00, "minQuantity": 1},{"offer": 9.00, "minQuantity": 3}]] |  [[{"offer": 9.00, "minQuantity": 3}]] |  [[{"offer": 9.00, "minQuantity": 3}]] |

- Saharsh Shah

---

没错。如果你在连接的表上执行聚合（GROUP_CONCAT ... GROUP BY）操作，会得到重复的结果。 - O. Jones

Answer 2

以下是一种稍微不太正派的方法来完成您所需的操作。使用GROUP_CONCAT(DISTINCT...)。

select 
  a.sku, a.ek, a.mwst, 
  concat('[[', group_concat(DISTINCT '{"offer": ', b.offer, ', "minQuantity": ', b.minQuantity, '}') , ']]') offersA,
...

我称它为肮脏的，因为它会不正确地消除您数据中真正存在的重复项。 http://sqlfiddle.com/#!9/2481e/6/0

- O. Jones