现在，我有：

custom_id| field_id | value
-------------------------
 20987   | 72       | No mark
 20987   | 122      | None
 20987   | 126      | http
 20999   | 72       | No mark
 20999   | 122      | 100
 20999   | 126      | http

我需要找到相匹配三个子句custom_id。第一 - field_id = 72和值= '无标记'。第二 - field_id = 122和值= ''。第三 - field_id = 126和值LIKE '％HTTP％'。

我运行此查询：

SELECT custom_id, value, field_id, count(custom_id) 
WHERE(field_id = '72' AND value = 'No mark') 
OR (field_id = '126' AND value like '%http%') 
OR (field_id = '122' AND value ='') 
GROUP BY custom_id
HAVING count(custom_id)=3 
LIMIT 10000

和我有结果：

[(20987,72,'No mark',3)]

我怎样才能ORDER BY或在“GROUP BY /计数”条款有结果排序一样值：

[(20987,126,'http',3)]
分析解答

在MySQL 8.0，你可以使用窗口函数来解决这个问题。首先，筛选对符合3个条件是，在执行窗口数量和分配，首先会将'http'值条件等级的记录。然后，外部查询过滤器上有3窗口数和排名第一的记录：

SELECT custom_id, value, field_id, cnt
FROM (
    SELECT 
        t.*, 
        ROW_NUMBER() OVER(PARTITION BY custom_id ORDER BY value LIKE '%http%') rn,
        COUNT(*) OVER (PARTITION BY custom_id) cnt
    FROM mytable t
    WHERE 
           (field_id = '72' AND value = 'No mark') 
        OR (field_id = '126' AND value like '%http%') 
        OR (field_id = '122' AND value ='') 
) x
WHERE cnt = 3 AND rn = 1
LIMIT 10000

附注：根据您的样本数据，value LIKE '%http%'会更好书面value = 'http'。

Demo在DB提琴：

| custom_id | value | field_id | cnt |
| --------- | ----- | -------- | --- |
| 20987     | http  | 126      | 3   |

在早期版本中，在窗口的功能都无法使用，你可以在一个子查询执行聚合生成custom_ids的list满足的条件，然后JOIN的结果与原始表，而在记录筛选具有value你期望：

SELECT t.*
FROM mytable t
INNER JOIN (
    SELECT custom_id
    FROM mytable
    WHERE
            (field_id = '72' AND value = 'No mark') 
        OR (field_id = '126' AND value like '%http%') 
        OR (field_id = '122' AND value ='') 
    GROUP BY custom_id
    HAVING count(custom_id)=3 
) x ON x.custom_id = t.custom_id
WHERE t.value like '%http%'

Demo在DB提琴（相同的结果如上述）。