MySQL SELECT 与 GROUP_CONCAT、GROUP BY、HAVING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7915903/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT with GROUP_CONCAT, GROUP BY, HAVING
提问by Johan
I Have table with odd_id and i want to select combinations for different ticket_id's. Here's my query:
我有一个带有odd_id 的表，我想为不同的ticket_id 选择组合。这是我的查询：
SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
And it gives me Following:
它给了我以下内容：
'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'
But if i use Having function, it doesn't give me results. Clearly: it gives me for the following:
但是如果我使用具有功能，它不会给我结果。显然：它给了我以下内容：
SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
HAVING oddsconcat = '14475279,14474499'
Returns ticket_id 35 and everyone is happy and code works fine, but if oddsconcat is larger then this one, it doesn't returns any value. for ex:
返回 ticket_id 35 并且每个人都很高兴并且代码工作正常，但是如果oddsconcat 大于这个，它不会返回任何值。例如：
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'
回答by Johan
I would rewrite it as:
我会把它改写为：
SELECT
ticket_id,
GROUP_CONCAT(DISTINCT odd_id ORDER BY odd_id ASC) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
HAVING oddsconcat = .....
Now the output in oddsconcatis determinatistic, because duplicates are eliminated and the items are in ascending order.
This should make matching much easier.
现在输出oddsconcat是确定性的，因为消除了重复项并且项目按升序排列。
这应该使匹配更容易。
回答by ypercube??
Assuming that (ticket_id, odd_id)combination is UNIQUE, this will give you all tickets that contain those two odds (and possibly more other odds):
假设(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
And this will give you all tickets that contain exactly those two odds (and no others):
这将为您提供包含这两个赔率的所有门票（没有其他赔率）：
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
