ThinkCMF多条件查询sql where条件判断
 
TP中 Where 条件表达式格式为：
 
 
 
$map['字段名'] = array('表达式', '操作条件');
 
 
 
 
$map['status'] = array('=', '1');
 $map['title'] = array('like', '%测试%');
 $map['delete_time'] = array('=', '0');
 
 
实际跑起来不知道为什么就变成
 
 
 
where `status` in ('=', '1') and `title` in ('like', '%测试%') and `delete_time` in ('=', '0')
 
 
无奈之下换种写法：
 
		$map = array();
        $map[] = ['post_status', '=', 1];
        $map[] = ['delete_time', '=', 0];
        if ( $word != '' ){
            $map[] = ['post_title', 'like', '%'.$word.'%'];
        }
 
这个才是我要的结果
 
	Db::name('recruit')
		->where($map)
		->order('is_top DESC')
		->order('published_time DESC')
		->paginate(20);

using System.Linq.Expressions;

 public static class PredicateBuilder

 {

     public static Expression<Func<T, bool>> True<T>() { return f => true; }

     public static Expression<Func<T, bool>> False<T>() { return f => false; }

     //false

     public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,

                                                         Expression<Func<T, bool>> expr2)

     {

         var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());

         return Expression.Lambda<Func<T, bool>>

               (Expression.Or(expr1.Body, invokedExpr), expr1.Parameters);

     }

     //true

     public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,

                                                          Expression<Func<T, bool>> expr2)

     {

         var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());

         return Expression.Lambda<Func<T, bool>>

               (Expression.And(expr1.Body, invokedExpr), expr1.Parameters);

     }

 }

 

 

 ---------------------------------------

 var list = db.t_schedule;//from t in db.テーブル名 select t;

 //True：And検索/False：Or検索

 var where = PredicateBuilder.False<テーブル名 >();

 if (!string.IsNullOrEmpty(検索条件１))

 {

     where = where.Or(p => p.カラム１ == 検索条件１);

 }

 if (!string.IsNullOrEmpty(検索条件２))

 {

     where = where.Or(p => p.カラム２ == 検索条件２);

 }

 var result = list.Where(where.Compile()).ToList();

 
 
首先介绍一下 Case ..When...Then..End  的用法：
 
CASEJiXiaoFind_RowID 
 
WHEN '1' THENJiXiao_Money1 
 WHEN '2' THEN JiXiao_Money2 
 WHEN '3' THEN JiXiao_Money3 
 WHEN '4' THEN JiXiao_Money4 
 ELSE JiXiao_Money5 
 
 
 
END
 
举例说明：
 

 
 
 
上图是一个视图的截图，我想在 表B 中添加一个字段 JiXiao_Money(绩效金额)；
 
如果
 
JiXiaoFind_RowID=1    则    JiXiao_Money=JiXiao_Money1
 
JiXiaoFind_RowID=1     则   JiXiao_Money=JiXiao_Money1
 
JiXiaoFind_RowID=1    则   JiXiao_Money=JiXiao_Money3
 
JiXiaoFind_RowID=1     则   JiXiao_Money=JiXiao_Money1
 
JiXiaoFind_RowID=1     则   JiXiao_Money=JiXiao_Money1
 
如何做呢？
 
****************************************************************************
 
具体做法:
 
视图中加上图中红字部分，即可。
 
 
 
 
 
 

 
 
sql code:
 
 
select * from table_name t
 
 
where (case when t.table_column='条件' then t.table_column
 
 
when t.table_column='条件' then t.table_column end)='条件'
 
 
my sql code:
 
 
-- 测试sql
 
 
SELECT  *FROM PROJECT_INFO pi
 
 

  WHERE (CASE WHEN PI.START_DATE_OF_REMOTE_AUDIT IS NOT NULL THEN 
TO_CHAR(PI.END_DATE_OF_REMOTE_AUDIT, 'yyyy-mm-dd')
WHEN PI.START_DATE_OF_REMOTE_AUDIT IS NULL THEN 
TO_CHAR(PI.END_DATE_OF_SITE_AUDIT, 'yyyy-mm-dd')
END)<'2019-09-18'
ORDER BY APPROVED_ID;
 
 
 
转载于:https://www.cnblogs.com/wk814007042/p/11537749.html