如何在Laravels中使用distinct从子查询中进行选择

Question

我有一个表名"，表格包括:Id(主键)，no_card，名称，描述。no_card是来自某个id的组。然后，我将基于no_card对描述进行计数。这是SQL代码:

sql代码:

SELECT description, COUNT(description) as cnt 
FROM ( SELECT distinct no_card, description FROM `warga` ) as t
GROUP by description ;

SQL已经显示了我需要的数据，然后在laravels中:

$data =  DB::table('warga')->selectRaw('description , COUNT(description ) AS CountData')
->select('no_card', 'description')->distinct()->from('warga')
->groupby('description')
->get();

当我运行程序时，未检测到alis表CountData，你有什么建议吗?谢谢你,

Answer 1

您可以使用子查询表作为闭包或将它们作为构建器对象传递。

• DB::table(Closure, alias)或DB::query()->from(Closure, alias)
• DB::table(Builder, alias)或DB::query()->from(Closure, alias)

$subquery = DB::table('warga')
->select('no_card', 'description')
->distinct();
$results = DB::table($subquery, 't')
->select('description')
->selectRaw('count(description) as cnt')
->groupBy('description')
->get();

$results = DB::table(function ($query) {
$query->from('warga')
->select('no_card', 'description')
->distinct();
}, 't')
->select('description')
->selectRaw('count(description) as cnt')
->groupBy('description')
->get();

如果你想的话，你甚至可以使查询看起来非常像sql。

$query = DB::query()
->select('description')
->selectRaw('count(description) as cnt')
->from(function ($sub) {
$sub->select('no_card', 'description')
->distinct();
}, 't')
->groupBy('description')
->get();