我有这个数据表:

+-----+-----------+------------------------------------+---------+
| 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 |
+-----+-----------+------------------------------------+---------+

并且需要提取适用于指定的 name(通配符字符串)和 term_id(整数)的 ID 列表.因此，例如，我将搜索具有 name LIKE '%Lighting%' 和 term_id = 26 的 ID...它应该返回 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'

但是当您在查询中同时包含 name 和 term_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/