Heim > Fragen und Antworten > Hauptteil
Wie schreibe ich die folgende SQL-Anweisung mit tp?
select * from `student` where sex = 1 and ((`age` in (10,11,12) and `name` like '%李%') or (`age` in (15,16,17) and `name` like '%长%'));
*2021-09-03 17:57:42
在项目中的代码
model('MaterialLike')->alias('like_material') ->join('materials materials' , $materialsAlias.'id = '.$likeMaterailsAlias. 'material_id') ->where($map) ->where(function ($query) use ($where_map){ $query->where($where_map); })->whereOr(function ($query) use ($where_or_map){ $query->where($where_or_map); }) ->field($field) ->order($order) ->page($page,$limit) ->select();
打印的sql语句是
SELECT `materials`.`id`, `materials`.`uid`, `materials`.`title`, `materials`.`type`, `materials`.`tag`, `materials`.`description`, `materials`.`content`, `materials`.`photo_list`, `materials`.`video_list`, `materials`.`view`, `materials`.`likes`, `materials`.`share`, `materials`.`comment`, `materials`.`customer`, `materials`.`create_time`, `materials`.`is_top`, `materials`.`status` FROM `ap_material_like` `like_material` INNER JOIN `ap_materials` `materials` ON `materials`.`id` = `like_material`.`material_id` WHERE `materials`.`seller_id` = 2 AND `materials`.`status` = 1 AND `materials`.`display` = 1 AND ( `materials`.`m_category_id` IN ( 10001, 10005, 10008 ) AND ( ( `materials`.`tag` LIKE '%胖%' ) OR ( `materials`.`content` LIKE '%胖%' ) ) AND `like_material`.`uid` = '196893' AND `like_material`.`typeid` = 3 AND `like_material`.`status` = 1 ) OR ( `materials`.`m_category_id` IN ( 10011, 10012, 10013, 10014, 10015 ) AND ( ( `materials`.`title` LIKE '%胖%' ) OR ( `materials`.`description` LIKE '%胖%' ) OR ( `materials`.`tag` LIKE '%胖%' ) ) AND `like_material`.`uid` = '196893' AND `like_material`.`typeid` = 3 AND `like_material`.`status` = 1 ) ORDER BY `like_material`.`create_time` DESC LIMIT 0, 20
然后我实际的需要的是
SELECT `materials`.`id`, `materials`.`uid`, `materials`.`title`, `materials`.`type`, `materials`.`tag`, `materials`.`description`, `materials`.`content`, `materials`.`photo_list`, `materials`.`video_list`, `materials`.`view`, `materials`.`likes`, `materials`.`share`, `materials`.`comment`, `materials`.`customer`, `materials`.`create_time`, `materials`.`is_top`, `materials`.`status` FROM `ap_material_like` `like_material` INNER JOIN `ap_materials` `materials` ON `materials`.`id` = `like_material`.`material_id` WHERE `materials`.`seller_id` = 2 AND `materials`.`status` = 1 AND `materials`.`display` = 1 AND ( {括号开头} ( `materials`.`m_category_id` IN ( 10001, 10016, 10017 ) AND ( ( `materials`.`tag` LIKE '%胖%' ) OR ( `materials`.`content` LIKE '%胖%' ) ) AND `like_material`.`uid` = '196893' AND `like_material`.`typeid` = 3 AND `like_material`.`status` = 1 ) OR ( `materials`.`m_category_id` IN ( 10020, 10021, 10022, 10023, 10024 ) AND ( ( `materials`.`title` LIKE '%胖%' ) OR ( `materials`.`description` LIKE '%胖%' ) OR ( `materials`.`tag` LIKE '%胖%' ) ) AND `like_material`.`uid` = '196893' AND `like_material`.`typeid` = 3 AND `like_material`.`status` = 1 ) ){括号结束} ORDER BY `like_material`.`create_time` DESC LIMIT 0, 20
而且这个keyword还是这样的
$keyword = trim($keyword); if($keyword !== '') { $keywords = explode(' ', $keyword); //把空字符串的元素筛选掉 $keywords = array_filter($keywords, function ($i) { return $i != ''; }); foreach ($keywords as $key => $val) { $keyword_where[] = array('like', "%{$val}%"); } $keyword_where[] = 'or'; }