MySQL JOIN语句-同一表中的引用字段 [英] MySQL JOIN Statement - Referenced Field in same Table

问题描述

如何获取表中所有订单的列表(已引用的订单和类型为-1的除外)

how to get a list of all orders in the table except (orders which has been referenced and type of -1)

订单表:

id   |   reference_id  | type
---------------------------------- 
1    |                 | 1
---------------------------------- 
2    |                 | 1
---------------------------------- 
3    |   1             | -1
----------------------------------

类似:

list = ArrayList();

if( order.type > 0 ){
    if( order.id != other_order.reference_id )
        list.add(order)
}

如何在MySQL语句中执行此操作?

how to do this in MySQL Statement?

此语句的结果也相同，但使用的是JOIN .... etc:

also the same result of this statement but using JOIN....etc:

select * from orders as a
where a.type > 0 AND not exists 
(select * from orders as b where a.id = b.ref_id)

谢谢

推荐答案

这将为您提供被引用的记录和有效的记录

This will give you those records which are referenced and those which are valid

SELECT * 
FROM   yourtable A 
       INNER JOIN yourtable B 
               ON A.reference_id = B.order_id 
WHERE  B.reference_type > 0; 

这篇关于MySQL JOIN语句-同一表中的引用字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！