使用GROUP_CONCAT、GROUP BY、HAVING

Question

我有一个带有票证的表，我想为不同的odd_id _id选择组合。我的查询如下：

SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id

它给我提供了以下内容：

'28', '14472510,14472813,14472546,14472855,14472746,14472610,14472647'
'29', '14471149,14471138,14471125,14475603'
'30', '14471823,14471781,14471655,14471865,14471597,14471968,14471739,14471697,14471923'
'31', '14473874,14473814,14473862,14473838,14473790,14473802,14473826,14473850'
'32', '14471588,14472766,14471651,14471777,14471419'
'33', '14472647,14472605,14471650,14471734'
'34', '14471588,14472704,14471817'
'35', '14475279,14474499'
'282', '14472756,14472652,14472813'
'283', '14471588,14472766,14471419,14471777,14471651'
'284', '14474521'
'285', '14474529'
'286', '14474547'
'287', '14471134,14471199,14473636,14471242,14471398,14471237'

但是如果我使用Having函数，它不会给出结果。显然:它为我提供了以下功能：

SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
HAVING oddsconcat = '14475279,14474499'

返回ticket_id 35，每个人都很高兴，代码运行良好，但是如果oddsconcat大于这个值，它就不会返回任何值。对于ex：

SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
HAVING oddsconcat = '14473874,14473814,14473862,14473838,14473790,14473802,14473826,14473850'

Answer 1

我会将其重写为：

SELECT 
ticket_id, 
GROUP_CONCAT(DISTINCT odd_id ORDER BY odd_id ASC) as oddsconcat 
FROM ticket_odds 
GROUP BY ticket_id 
HAVING oddsconcat = .....

现在，oddsconcat中的输出是确定性的，因为消除了重复项并且项是按升序排列的。

这应该会使匹配变得更容易。

Answer 2

假设(ticket_id, odd_id)组合为UNIQUE，这将为您提供包含这两个赔率(可能还有更多其他赔率)的所有票据：

SELECT
  ticket_id,
  GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
WHERE odd_id IN (14475279,14474499)
GROUP BY ticket_id

这将为您提供恰好包含这两个赔率(而不包含其他赔率)的所有门票：

SELECT
  ticket_id,
  GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
WHERE odd_id IN (14475279,14474499)     --- list
GROUP BY ticket_id
HAVING COUNT(*) = 2                     --- size of list