CONCAT的用法就是查询的时候拼接多个字段为一个字段,比较常用的就是地址拼接,比如:
//拼接用户地址的省市区、详细地址、门牌号
SELECT CONCAT(province,city,district,address,house_no) AS addre FROM user_address
还有一种情况就是我们需要搜索查询用户地址,但是又不想过多的使用or去查询,这个时候子查询+拼接就能实现我们的需求,SQL:
SELECT * FROM (SELECT CONCAT(province,city,district,address,house_no) AS addre FROM yw_user_address) AS a WHERE a.addre LIKE "%大夫%";
子查询必须要加别名,不加别名sql会报错。
使用拼接的方式模糊搜索地址关联用户
SELECT u.*,CONCAT(a.province,a.city,a.district,a.address,a.house_no) FROM `user_address` `a` INNER JOIN `user` `u` ON `u`.`id`=`a`.`user_id`
SELECT * FROM ( SELECT u.*,CONCAT(a.province,a.city,a.district,a.address,a.house_no) AS addre FROM `user_address` `a` INNER JOIN `user` `u` ON `u`.`id`=`a`.`user_id`) addre WHERE `addre`.`addre` LIKE '%搜索地址%'
我经常用的是TP6框架,所以我这里在加一个项目中的运用方式
//获取子查询sql
$sql = UserAddress::field("u.*,CONCAT(a.province,a.city,a.district,a.address,a.house_no) as addre")->alias("a")->join("user u","u.id=a.user_id")->buildSql();
//查询搜索的结果
$sql = Db::table($sql." addre")->where("addre.addre","like","%搜索地址%")->fetchSql(true)->select();
fetchSql方法是打印sql,true的时候会返回sql语句,false时会返回查询结果