php - SQL连接两个group BY sum

Question

我有两个表 Lossess_tab 和 numbers_tab。 losses_tab是每一项的损失。loss的Sum就是一个项目的总损失。numbers_tab是每一个项目的个数。Sum of numbers就是一个项目的总个数。

CREATE TABLE IF NOT EXISTS `losses_tab` (`id` bigint(20) unsigned NOT NULL,`item_id` varchar(50) NOT NULL,`loss` varchar(120) NOT NULL);


INSERT INTO `losses_tab` (`id`, `item_id`, `loss`) VALUES (1,100,1.5),(2,100,1.8),(3,102,1.0),(4,103,1.0),(5,101,0),(6,102,1);

CREATE TABLE IF NOT EXISTS `numbers_tab` (
  `item_id` varchar(50) NOT NULL,
  `number` varchar(120) NOT NULL);
  INSERT INTO `numbers_tab` ( `item_id`, `number`) VALUES
    (100,10),(100,12),(102,1),(103,25),(101,16),(103,9),(102,8);

我想要的结果是

------------------------------------------------
| item_id  | total loss   | total number  |
------------------------------------------------
| 100      | 3.3          | 22            |
------------------------------------------------
| 101      | 0            | 16            |
------------------------------------------------
| 102      | 2.0          | 9             |
------------------------------------------------
| 103      | 1.0          | 34            |
------------------------------------------------

这是我的 fiddle 链接 Link to view the fiddle

TABLE losses_tab

 id item_id loss
    1   100     1.5
    2   100     1.8
    3   102     1.0
    4   103     1.0
    5   101     0
    6   102     1

表 numbers_tab

item_id number
100    10
100    12
102    1
103    25
101    16
103    9
102    8

Answer 1

尝试作为

select item_id, sum(loss) as total_loss,
(select sum(number) from numbers_tab nt where nt.item_id = losses_tab.item_id) as total_number
from losses_tab group by item_id

Fiddle