问题描述
以下是Laravel文档的摘录:
Below is an excerpt from the Laravel documentation:
$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
但是,如果我想确定一个值是否在数据库的两列之间,该怎么办?
But what if I want to find out if a value is between two columns in my database?
这是我的原始SQL:
SELECT a.*, b.name FROM restaurants a, restaurant_class b
WHERE a.restaurant_class_id = b.id
AND '$d' = CURRENT_DATE
AND '$t' BETWEEN a.saturday_ot AND a.saturday_ct
ORDER BY id DESC
saturday_ot
和saturday_ct
是表中的TIME
列,而$t
是时间变量.因此,我想检查一下时间是否介于两列中的时间之间.
saturday_ot
and saturday_ct
are TIME
columns in my table and $t
is a time variable. So I want to check if the time is in between the the times in both columns.
推荐答案
对于两个列,whereBetween
方法别无选择.但是,您可以通过以下两种方式之一进行操作:
There is no alternative to the whereBetween
method that applies to two columns. You can however do this in one of two ways:
1..将whereRaw
与绑定一起使用,在其中使用原始条件和变量的绑定:
1. Use whereRaw
with bindings, where you use the raw condition and a binding for the variable:
whereRaw('? between saturday_ot and saturday_ct', [$t])
2..在两个条件下使用where
,这些条件使用两个列值作为$t
变量值的边界:
2. Use a where
with two conditions that use the two column values as boundaries for the $t
variable value:
where(function ($query) use ($t) {
$query->where('saturday_ot', '<=', $t);
$query->where('saturday_ct', '>=', $t);
})
这篇关于Laravel/Lumen中的两列之间的SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!