DB::raw('SUM(CASE WHEN status="C" THEN 1 END) AS num_c'), DB::raw('SUM(CASE WHEN status="C" THEN amount END) AS sum_c'))这两个可以放一条进行吗
时间: 2024-02-23 09:00:33 浏览: 26
是的,您可以将两个 `CASE` 表达式合并为一个,以避免重复扫描行。以下是修改后的查询:
```php
$result = DB::table('table_name')
->select(DB::raw('SUM(CASE WHEN status="A" THEN amount END) AS sum_a'),
DB::raw('SUM(CASE WHEN status="B" THEN amount END) AS sum_b'),
DB::raw('SUM(CASE WHEN status="C" THEN 1 ELSE 0 END) AS num_c'),
DB::raw('SUM(CASE WHEN status="C" THEN amount END) AS sum_c'))
->get();
```
这将返回四个列:`sum_a`、`sum_b`、`num_c` 和 `sum_c`,其中 `num_c` 列现在包括满足 `status="C"` 条件的行数。
相关问题
$result = DB::table('table_name') ->select(DB::raw('SUM(CASE WHEN status="A" THEN amount ELSE 0 END) AS sum_a'), DB::raw('SUM(CASE WHEN status="B" THEN amount ELSE 0 END) AS sum_b'), DB::raw('SUM(CASE WHEN status="C" THEN 1 ELSE 0 END) AS num_c'), DB::raw('SUM(CASE WHEN status="C" THEN amount ELSE 0 END) AS sum_c')) ->get();咋来优化
您可以将 `SUM()` 函数应用于金额列,以避免在 `CASE` 表达式中使用 `ELSE 0`,因为这样会在每行上执行一个条件表达式。同时,您还可以使用条件表达式 `status="C"` 的结果来计算 'C' 状态的行数。这样可以使查询更加高效。以下是修改后的查询:
```php
$result = DB::table('table_name')
->select(DB::raw('SUM(CASE WHEN status="A" THEN amount END) AS sum_a'),
DB::raw('SUM(CASE WHEN status="B" THEN amount END) AS sum_b'),
DB::raw('SUM(CASE WHEN status="C" THEN 1 END) AS num_c'),
DB::raw('SUM(CASE WHEN status="C" THEN amount END) AS sum_c'))
->get();
```
这将返回四个列:`sum_a`、`sum_b`、`num_c` 和 `sum_c`,它们分别包含 `status` 为 'A'、'B'、'C' 的行的金额总和和行数。请注意,在 `CASE` 表达式中不使用 `ELSE` 子句将使未满足条件的行的值为 `NULL`,这对于计算金额总和是没有影响的。
$result = DB::table('table_name') ->select(DB::raw('SUM(CASE WHEN status="A" THEN 1 ELSE 0 END) AS count_a'), DB::raw('SUM(CASE WHEN status="B" THEN 1 ELSE 0 END) AS count_b'), DB::raw('SUM(CASE WHEN status="C" THEN 1 ELSE 0 END) AS count_c'), DB::raw('SUM(CASE WHEN status="D" THEN 1 ELSE 0 END) AS count_d')) ->get();我要sum金额不是条数
如果您想要对金额进行求和而不是对条数进行求和,那么您需要将 `SUM()` 函数应用于金额列,而不是条件表达式。例如,如果您有一个名为 `amount` 的列,您可以将查询修改为以下内容:
```php
$result = DB::table('table_name')
->select(DB::raw('SUM(CASE WHEN status="A" THEN amount ELSE 0 END) AS sum_a'),
DB::raw('SUM(CASE WHEN status="B" THEN amount ELSE 0 END) AS sum_b'),
DB::raw('SUM(CASE WHEN status="C" THEN amount ELSE 0 END) AS sum_c'),
DB::raw('SUM(CASE WHEN status="D" THEN amount ELSE 0 END) AS sum_d'))
->get();
```
这将返回四个列:`sum_a`,`sum_b`,`sum_c` 和 `sum_d`,它们分别包含 `status` 为 'A'、'B'、'C' 和 'D' 的行的金额总和。