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;