mysql 复杂查询

1.同一个表下多次查询：

sql语句： select b.* ,(select name from exh_common.medicine_type a where b.p_id = a.id) as p_name from exh_common.medicine_type b;

获取表medicine_type中所有内容，并获取表中，所有p_id不为空的父类型的名称

 

2.三表联合查询

sql： SELECT a.*,b.type_id, c.`name`,c.description from medicine_cadn a LEFT JOIN medicine_cadn_type_rel b ON a.id = b.cadn_id LEFT JOIN medicine_type c ON c.id = b.type_id

参考来源：https://zhidao.baidu.com/question/1689579268873125748.html

三张表的表结构：

1.tablename：'medicine_goods'

id = Column(String(40), primary_key=True)
cadn_id = Column(String(40))
cadn_name = Column(String(200))
cadn_py_name = Column(String(200))
name = Column(String(200), nullable=False)
py_name = Column(String(200), nullable=False)
description = Column(String(1020))
factory = Column(String(200), nullable=False)
certificate = Column(String(100), nullable=False)
label = Column(Integer, nullable=False)
drug_type = Column(String(10), nullable=False, server_default=text("'固态'"))
drug_unit = Column(String(20), nullable=False, server_default=text("'mg'"))
specification = Column(String(200))
specification_shape = Column(String(200))
specification_dose = Column(String(200))
specification_count = Column(String(200))
take_way = Column(String(200))
images = Column(Text)
key1 = Column(String(200))
val1 = Column(String(200))
key2 = Column(String(200))
val2 = Column(String(200))
key3 = Column(String(200))
val3 = Column(String(200))
key4 = Column(String(200))
val4 = Column(String(200))
key5 = Column(String(200))
val5 = Column(String(200))
enabled = Column(Integer, nullable=False, server_default=text("'1'"))
is_del = Column(Integer, nullable=False, server_default=text("'0'"))
created = Column(DateTime, nullable=False)
created_by = Column(String(40), nullable=False)
updated = Column(DateTime)
updated_by = Column(String(40))


2.tablename__ = 'medicine_type'

id = Column(String(50), primary_key=True)
name = Column(String(100), nullable=False, unique=True)
description = Column(String)
p_id = Column(String(32))
enabled = Column(Integer, nullable=False, server_default=text("'1'"))
is_del = Column(Integer, nullable=False, server_default=text("'0'"))
created = Column(DateTime, nullable=False)
created_by = Column(String(40), nullable=False)
updated = Column(DateTime)
updated_by = Column(String(40))

3.tablename__ = 'medicine_cadn_type_rel'

id = Column(Integer, primary_key=True)
cadn_id = Column(String(40), nullable=False)
type_id = Column(String(40), nullable=False)