代码之家 › 专栏 › 技术社区 › M. Ade Erik

基于mysql中的日期范围计算关系表中的值数

laravel-11 join laravel mysql sql

0

M. Ade Erik · 技术社区 · 1 年前

巴亚兰加斯表

| tglbayar   | bayarlanja |user_id|
| ---------- | ---------- |-------|
| 2024-01-05 |     10     |   1   |
| 2024-02-06 |     20     |   1   |
| 2024-03-07 |     30     |   1   |
| 2024-04-08 |     40     |   1   |

桌上zakats

| tglawalhaul| tglakhirhaul |user_id|
| ---------- | ------------ |-------|
| 2024-01-01 |  2024-02-25  |   1   |
| 2024-03-01 |  2024-04-25  |   1   |

我试过了

SELECT
  zakats.*,
  bayarlanjas.user_id,
  sum(bayarlanja) AS total_bayar
FROM
  `zakats`
  LEFT JOIN `bayarlanjas` ON `bayarlanjas`.`user_id` = `zakats`.`user_id`
WHERE
  `bayarlanjas`.`tglbayar` BETWEEN zakats.tglawalhaul
  AND zakats.tglakhirhaul
  AND `zakats`.`user_id` = 1
GROUP BY
  `bayarlanjas`.`user_id`

但是错误:

SQLSTATE[42000]:语法错误或访问冲突:SELECT列表的1055表达式#1不在GROUP BY子句中,并且包含未聚合的

我想要什么


| tglawalhaul| tglakhirhaul |total_bayar|user_id|
| ---------- | ------------ |-----------|-------|
| 2024-01-01 |  2024-02-25  |     30    |   1   |
| 2024-03-01 |  2024-04-25  |     70    |   1   |

1 回复 | 直到 1 年前

1

Saint Rollox Digital 1 年前

试试这个:

SELECT
  zakats.tglawalhaul,
  zakats.tglakhirhaul,
  zakats.user_id,
  SUM(bayarlanjas.bayarlanja) AS total_bayar
FROM
  zakats
LEFT JOIN bayarlanjas ON bayarlanjas.user_id = zakats.user_id
  AND bayarlanjas.tglbayar BETWEEN zakats.tglawalhaul AND zakats.tglakhirhaul
WHERE
  zakats.user_id = 1
GROUP BY
  zakats.tglawalhaul,
  zakats.tglakhirhaul,
  zakats.user_id