如何在mysql中编写此自连接查询

本文介绍了如何在mysql中编写此自连接查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

你好，我有一个这样的表结构

Hello I have a table structure like this

products_id | model_num | master_model_num
   1        |  cth001   |    0
   2        |  cth002   |    0
   3        |  cth003   |    cth001
   4        |  cth004   |    cth001
   5        |  cth005   |    0
   6        |  cth006   |    cth002

我的问题

我将提供products_id到表中，它将获得所有产品ID，其中master_model_num等于给定products_id

I will provide the products_id to the table and it will get all product ids whoes master_model_num is equal to the model_num of the given products_id

我尝试了以下查询，但没有生成我想要的结果

I have tried following query but it doen't generate the result that I want

SELECT p.products_id
FROM products p,products pp
WHERE p.products_id=pp.products_id
AND p.products_model=pp.products_master_model
AND p.products_id='1'

推荐答案

SELECT pp.products_id
    FROM products p
        INNER JOIN products pp
            ON p.model_num = pp.master_model_num
    WHERE p.products_id = '1'

这篇关于如何在mysql中编写此自连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！