我有一个问题,我在Laravel Eloquent ORM中写查询语句时遇到了麻烦。
如果有人能帮忙就非常感激。
这是SQL表达式:
SELECT DISTINCT cust, cust_no FROM delivery_sap
WHERE cust NOT IN ( SELECT cust_name FROM customer)
AND cust_no NOT IN ( SELECT cust_code FROM customer)
我有一个问题,我在Laravel Eloquent ORM中写查询语句时遇到了麻烦。
如果有人能帮忙就非常感激。
这是SQL表达式:
SELECT DISTINCT cust, cust_no FROM delivery_sap
WHERE cust NOT IN ( SELECT cust_name FROM customer)
AND cust_no NOT IN ( SELECT cust_code FROM customer)
您可以像下面展示的那样使用以下查询替代执行3个不同的查询:
DB::table('delivery_sap')
->whereNotIn('cust', function ($query) {
$query->select('cust_name')->from('customer');
})
->whereNotIn('cust_no', function ($query) {
$query->select('cust_code')->from('customer');
})
->select('cust', 'cust_no')
->distinct('cust')
->get();
这段代码将提供与问题中所请求的完全相同的查询, 要检查查询,请使用以下代码
DB::table('delivery_sap')
->whereNotIn('cust', function ($query) {
$query->select('cust_name')->from('customer');
})
->whereNotIn('cust_no', function ($query) {
$query->select('cust_code')->from('customer');
})
->select('cust', 'cust_no')
->distinct('cust')
->toSql();
输出结果为:
select distinct `cust`, `cust_no` from `delivery_sap`
where `cust` not in (select `cust_name` from `customer`)
and `cust_no` not in (select `cust_code` from `customer`)
尝试类似这样的操作:
DB::table('delivery_sap')
->whereNotIn('cust', DB::table('customer')->pluck('cust'))
->whereNotIn('cust_no', DB::table('customer')->pluck('cust_no'))
->select('cust', 'cust_no')
->groupBy('cust', 'cust_no')
->get();
我将下面的代码中的pluck('cust')更正为pluck('cust_name'),pluck('cust_no')更正为pluck('cust_code'),现在它可以工作了。
DB::table('delivery_sap')
->whereNotIn('cust', DB::table('customer')->pluck('cust_name'))
->whereNotIn('cust_no', DB::table('customer')->pluck('cust_code'))
->select('cust', 'cust_no')
->groupBy('cust', 'cust_no')
->get();
您可以使用exists
或left join
来代替现有解决方案中对同一表的子查询,以获得更好的性能,这样就不需要这两个额外的子查询了。
SELECT DISTINCT cust, cust_no
FROM delivery_sap d
WHERE EXISTS (
SELECT 1
FROM delivery_sap
WHERE cust_name = d.cust OR cust_code = d.cust
)
或者
SELECT DISTINCT d.cust, d.cust_no
FROM delivery_sap d
LEFT JOIN delivery_sap d1 ON d.cust = d1.cust_name OR d.cust = d1.cust_code
WHERE d1.cust IS NULL
DB::table('delivery_sap as d')
->leftJoin('delivery_sap as d1', function ($join) {
$join->on('d.cust','=','d1.cust_name')
->orWhere('d.cust', '=', 'd1.cust_code');
})
->whereNull('d1.cust')
->select('cust', 'cust_no')
->distinct()
->get();