Mysql两张表连接多字段查询_mysql一张表多个字段关联另一张表查询

如下：一张订单表多个字段关联用户表：

1.链表查询

SELECT cu.id AS 'id',

cu.version AS 'version',

cu.cid AS 'cid',

cu.uid AS 'uid',

cu.shopName AS 'shopName',

cu.address AS 'address',

cu.totalPrice AS 'totalPrice',

cu.orderType AS 'orderType',

cu.state AS 'state',

cu.cCreateTime AS 'cCreateTime',

cu.decorate AS 'decorate',

cu.area AS 'area',

cu.roomArea AS 'roomArea',

cu.machinePrice AS 'machinePrice',

cu.caid AS 'caid',

cu.cooperationtypeid AS 'cooperationtypeid',

cu.cooperationRebate AS 'cooperationRebate',

cu.cooperationPrcie AS 'cooperationPrcie',

cu.machineDiscount AS 'machineDiscount',

cu.alreadyPaid AS 'alreadyPaid',

cu.updateTime AS 'updateTime',

cu.cooperationdeposit AS 'cooperationdeposit',

cu.updateUserid AS 'updateUserid',

cu.overseerId AS 'overseerId',

cu.decorationQuotation AS 'decorationQuotation',

cu.machineDeposit AS 'machineDeposit',

us1.uName AS 'serviceuName',

us2.uName AS 'updateName'

FROM customerorder AS cu

LEFT JOIN userdetail AS us1

ON us1.id = cu.uid

LEFT JOIN userdetail AS us2

ON us2.id = cu.updateUserid;

2.子查询

SELECT cu.id AS 'id',

cu.version AS'version',

cu.cid AS'cid',

cu.uid AS'uid',

cu.shopName AS'shopName',

cu.address AS'address',

cu.totalPrice AS'totalPrice',

cu.orderType AS'orderType',

cu.state AS'state',

cu.cCreateTime AS'cCreateTime',

cu.decorate AS'decorate',

cu.area AS'area',

cu.roomArea AS'roomArea',

cu.machinePrice AS'machinePrice',

cu.caid AS'caid',

cu.cooperationtypeid AS'cooperationtypeid',

cu.cooperationRebate AS'cooperationRebate',

cu.cooperationPrcie AS'cooperationPrcie',

cu.machineDiscount AS'machineDiscount',

cu.alreadyPaid AS'alreadyPaid',

cu.updateTime AS'updateTime',

cu.cooperationdeposit AS'cooperationdeposit',

cu.updateUserid AS'updateUserid',

cu.overseerId AS'overseerId',

cu.decorationQuotation AS'decorationQuotation',

cu.machineDeposit AS'machineDeposit',

(SELECT uName FROM userdetail WHERE id= cu.uid) AS 'serviceuName',

(SELECT uName FROM userdetail WHERE id= cu.updateUserid) AS 'updateName'FROM customerorder AS cu;

总结：

1，表关联的效率要高于子查询，因为子查询走的是笛卡尔积

2，表关联可能有多条记录，子查询只有一条记录，如果需要唯一的列，最好走子查询