問題描述

如何用tp寫如下sql語句？

select * from `student` where sex = 1 and ((`age` in (10,11,12) and `name` like '%李%'） or (`age` in (15,16,17) and `name` like '%長%'));

問題解答

在項目中的代碼

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 ) ){括號結(jié)束}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';}回答2：

$sql = '你的sql';

$result = Db::query($sql);