我有这张数据表：
+-----+-----------+------------------------------------+---------+
| ID | post_type | name | term_id |
+-----+-----------+------------------------------------+---------+
| 278 | supplier | Heating | 15 |
| 282 | supplier | Central Heating | 16 |
| 278 | supplier | Biomass | 17 |
| 278 | supplier | Ground Source Heat Pumps | 18 |
| 278 | supplier | Passive Solar | 19 |
| 282 | supplier | Air Source Heat Pumps | 21 |
| 278 | supplier | Air Conditioning | 22 |
| 278 | supplier | Boilers | 23 |
| 277 | supplier | Lighting | 25 |
| 277 | supplier | LED's | 26 |
| 282 | supplier | Halogen | 28 |
| 277 | supplier | CFL | 29 |
| 282 | supplier | Sustainable Construction Materials | 31 |
| 282 | supplier | Plaster | 33 |
| 282 | supplier | Floors | 37 |
| 282 | supplier | Water | 38 |
| 282 | supplier | Showers & Baths | 43 |
| 278 | supplier | Cooling | 44 |
| 278 | supplier | Refrigeration | 46 |
| 282 | supplier | Passive Design | 47 |
| 278 | supplier | Chillers | 48 |
| 282 | supplier | Renewable Energy | 49 |
| 282 | supplier | Air Source Heat Pumps | 53 |
| 282 | supplier | Biomass Heating | 55 |
| 282 | supplier | Biofuels | 57 |
| 282 | supplier | Insulation | 61 |
| 282 | supplier | Wall | 63 |
| 282 | supplier | Floor | 64 |
| 282 | supplier | Draught Proofing | 65 |
| 282 | supplier | Energy Efficiency | 70 |
| 282 | supplier | Gas Boiler Management Systems | 71 |
| 282 | supplier | Low Energy Lighting | 72 |
| 282 | supplier | Voltage Control | 73 |
| 282 | supplier | Smart Meters | 74 |
| 282 | supplier | Electric Heating | 75 |
+-----+-----------+------------------------------------+---------+
并且需要提取同时适用于指定的
ID（通配符字符串）和name（整数）的term_ids列表。例如，我会搜索具有aID和aname LIKE '%Lighting%'的term_id = 26s，它应该返回ID 277。下面的查询可以工作，但并不漂亮：
SELECT a.ID, d.name, d.term_id
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND a.ID IN (
SELECT a.ID
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE d.term_id = '26'
)
GROUP BY a.ID
我尝试了以下查询，但都没有返回结果：
SELECT DISTINCT a.ID, d.name, d.term_id
FROM cn_posts AS a
LEFT JOIN cn_postmeta AS b ON a.ID = b.post_id
LEFT JOIN cn_term_relationships AS c ON a.ID = c.object_id
LEFT JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND d.term_id = '26'
SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND d.term_id = '26'
GROUP BY a.ID
这两个返回岗位：
SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.term_id = '26'
但是当您在查询中同时包含名称和术语id时，它不会返回任何内容？
最佳答案
显然，表中没有一行（数据示例）匹配d.name LIKE '%Lighting%' AND d.term_id = '26'的条件。有与第一部分匹配的行和与第二部分匹配的行，但没有与这两个部分匹配的行。但是，如果作为一组行，则ID = 277与条件匹配。因此，您需要在having子句中引入分组并应用条件（尽管稍作修改），如下所示：
SELECT a.ID
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
GROUP BY a.ID
HAVING COUNT(d.name LIKE '%Lighting%' OR NULL) > 0
AND COUNT(d.term_id = '26' OR NULL) > 0
;
不过，请注意，这意味着您不能在同一个查询中获得id旁边的
d.name或d.term之类的详细信息（但在这种情况下，从a中提取其他列就可以了）。如果在同一个查询中返回d列是解决问题所必需的，那么您可能需要在提取所需所有数据的几乎相同的查询中使用上述作为派生表。在这种情况下，派生表将被用作一种过滤方法，将id集向下筛选为只匹配这两个条件的id集。在主查询中，这两个条件需要连接到OR而不是AND，如下所示：SELECT a.ID, d.name, d.term_id
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND (d.name LIKE '%Lighting%' OR d.term_id = '26')
AND a.ID IN (
/* the above query, now used just as a filter */
SELECT a.ID
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
GROUP BY a.ID
HAVING COUNT(d.name LIKE '%Lighting%' OR NULL) > 0
AND COUNT(d.term_id = '26' OR NULL) > 0
)
;
显然，这不会比你最后的查询更好。
关于mysql - 如何从匹配两个相似列的表中选择不同的ID？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12563065/