sql语法记录

yii2显示sql原始语句

$query = UserModel::find()->where(['status'=>1]);

echo $query->createCommand()->getRawSql();

yii2 的between

$model = User::find()->andFilterWhere(['between', 'created_at', $starTime, $endTime]);

yii2 的updateAll 修改全部,多条件

Baodan::updateAll(['logistics_sn' => $post['Baodan']['logistics_sn']], ['and', ['in', 'id', json_decode($data)], ['status' => 1]]);

查询重复的字段值

select * from lhh_log group by order_id having count(order_id)>1

排除重复的字段,取唯一

select DISTINCT user_id from lhh_cn

yii2 写法

$model = Log::find()->where(['type' => 3, 'is_fanli' => 0, 'user_id' => '30509'])->select('user_id')->distinct('user_id')->all();

对sql查出的时间格式化

Select * From order Where DATE_FORMAT(createTime,'%m-%d') >= '05-01'

判断expression_1字段是否为空,为空则返回expression_2

select IFNULL(expression_1,expression_2) from bkt_summary

sql自定义排序

SELECT * FROM lhh_order where is_tuikuan <> 0 ORDER BY FIELD(`is_tuikuan`, 1, 3, -1)

yii2 写法

Order::find()->where(['<>', 'is_tuikuan', '0'])->orderBy(["FIELD(is_tuikuan, '1', '3', '-1')" => true]);

Order::find()->where(['<>', 'is_tuikuan', '0'])->orderBy([new Expression("FIELD(is_tuikuan, '1', '3', '-1')")]);