如何从匹配两个相似列的表中选择不同的ID？

作者: 淋了一整夜的雨
发布时间: 2025-02-06 03:56:23 (16天前)
转自：

7 条回复

0#
回复此人
LOGO | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <p>这个简单的选择不会做你需要的。 </p> <pre> <code> select ID, name, term_id from yourtable where name LIKE '%Lighting%' and a term_id = 26 </code> </pre> </DIV>

编辑
1#
回复此人
Gassyc加西可 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> 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 INNER JOIN cn_terms AS d2 ON c.term_taxonomy_id = d2.term_id WHERE a.post_status = 'publish' AND d.name LIKE '%Lighting%' AND d2.term_id = '26' </code> </pre> <P> 由于条件在不同的行上，因此必须两次加入cn_terms。 </p> </DIV>

编辑
2#
回复此人
关于贤的记忆 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> SELECT a.ID, d.name, d.term_id, a.post_status FROM wp_posts AS a JOIN wp_postmeta AS b ON a.ID = b.post_id JOIN wp_term_relationships AS c ON a.ID = c.object_id JOIN wp_terms AS d ON c.term_taxonomy_id = d.term_id WHERE a.post_status = 'publish' AND d.name LIKE '%Halogen%' AND d.term_id = '25' GROUP BY a.ID </code> </pre> </DIV>

编辑
3#
回复此人
逆转的风 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 显然，表中没有单行（您的数据样本）与条件匹配 <code> d.name LIKE '%Lighting%' AND d.term_id = '26' </code> 。有与第一部分匹配的行和与第二部分匹配的行，但没有匹配两者的行。但是，如果作为一个 <EM> 组 </EM> 行， <code> ID = 277 </code> 确实符合条件。因此，您需要引入分组并在HAVING子句中应用条件（尽管稍作修改），如下所示： </p> <pre> <code> 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 ; </code> </pre> <P> 但请注意，这意味着您无法获得详细信息 <code> d.name </code> 要么 <code> d.term </code> 与同一查询中的ID一起（但从中拉出其他列） <code> a </code> 在这种情况下会没事的。）如果回来了 <code> d </code> 同一查询中的列对于解决您的问题是必需的，您可能需要在几乎相同的查询中使用上述作为派生表 <EM> 所有 </EM> 您需要的数据。在这种情况下，派生表将用作将ID集合过滤到仅匹配两个条件的ID的方法。在该主查询中，需要将两个条件连接起来 <code> OR </code> 而不是 <code> AND </code> ，像这样： </p> <pre> <code> 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 ) ; </code> </pre> <P> 显然，这几乎不会比你最终的查询更好。 </p> </DIV>

编辑
4#
回复此人
你抱着孩子@先跑 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 你试过这个吗？ </p> <pre> <code> 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 d.term_id = '28' GROUP BY a.ID </code> </pre> </DIV>

编辑
5#
回复此人
小蚊子 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 在得到所有人的帮助之后（非常感谢!!）我最终只是使用了这个查询，这个查询不是最干净但是唯一一个得到我所追求的结果的查询，我需要让工作顺利进行。 </p> <P> 还匹配帖子标题和说明上的关键字以及服务名称。 </p> <pre> <code> SELECT a.ID, a.post_title, a.post_status, d.name, d.term_id 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 ( a.post_title LIKE '%Lighting%' OR a.post_content LIKE '%Lighting%' OR 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 </code> </pre> <P> 非常感谢你们的帮助。显然，如果你能弄清楚为什么其他查询不起作用或者可以清理这个查询，请随时回复。 </p> <P> 戴夫 </p> </DIV>

编辑

登录后才能参与评论