mysql like 表字段_mysql关联模糊查询他表字段

如下：订单表关联了用户的id(多个)，要根据用户名模糊查询订单信息，但是订单表只有id。创建视图用不着，咱也没权限。于是如下

SELECT * FROM (

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',cs.cName AS 'cName',se.sName AS 'serviceType',coop.ctName AS 'cooperationName'

FROM `customerorder` AS cu

LEFT JOIN `userdetail` AS us1 ON us1.id=cu.uid

LEFT JOIN `userdetail` AS us2 ON us2.id=cu.updateUserid

LEFT JOIN `customer` AS cs ON cs.id=cu.cid

LEFT JOIN `servicetype` AS se ON se.id=cu.orderType

LEFT JOIN `cooperationtype` AS coop ON coop.id=cu.cooperationtypeid)

AS `customerorder`

WHERE cCreateTime >='2018-1-1 01:10:11' AND cCreateTime<='2018-11-30 10:00:15'

AND serviceuName LIKE CONCAT('%','业务','%')

ORDER BY cCreateTime ASC

LIMIT 0,10

总结：

1.为什么用left join 而不是join 或者inner join,你试一下就知道了。后两者在数据匹配不到的情况下整条记录都没有。

2.时间类型用的是timestamp,因为方便省空间。但是时间段查询用bewteen就不行，所以只能用>=,<=

顺便记录mybatis语句：

SELECT * FROM (

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',cs.cName AS 'cName',se.sName AS 'serviceType',coop.ctName AS 'cooperationName'

FROM `customerorder` AS cu

LEFT JOIN `userdetail` AS us1 ON us1.id=cu.uid

LEFT JOIN `userdetail` AS us2 ON us2.id=cu.updateUserid

LEFT JOIN `customer` AS cs ON cs.id=cu.cid

LEFT JOIN `servicetype` AS se ON se.id=cu.orderType

LEFT JOIN `cooperationtype` AS coop ON coop.id=cu.cooperationtypeid)

AS `customerorder`

AND cid=#{cid}

AND serviceuName LIKE CONCAT('%',#{uName},'%')

AND cCreateTime >=#{startTime}

AND #{endTime}>=cCreateTime

AND alreadyPaid>=totalPrice

AND totalPrice>alreadyPaid

ORDER BY totalPrice ASC

ORDER BY totalPrice DESC

ORDER BY cCreateTime ASC

ORDER BY cCreateTime DESC

limit #{index},#{pagesize}