如何在sql中创建具有LIKE语句的列的和？

Question

我有一个sql表，如下所示

select * from Daily_Usage;

+------------+-------------------+-------------+
| Date       | Provider          | Total_Usage |
+------------+-------------------+-------------+
| 2022-01-11 | X1_IC_03          |       22.62 |
| 2022-01-11 | X1_IC_04          |       24.81 |
| 2022-01-11 | X1_IC_05          |       23.90 |
| 2022-01-11 | X2_IC_1           |        0.00 |
| 2022-01-11 | X3_08             |       16.86 |
| 2022-01-11 | X4_IC_01          |        0.00 |
| 2022-01-11 | X4_IC_02          |        0.00 |
| 2022-01-11 | X5_IC_02          |        6.66 |
| 2022-01-11 | X6_12             |       38.66 |
| 2022-01-11 | X6_13             |       25.27 |
| 2022-01-11 | X6_14             |     3205.78 |
| 2022-01-11 | X7_03             |        0.00 |
| 2022-01-11 | X8_05             |       11.32 |
| 2022-01-11 | X8_06             |        6.83 |
| 2022-01-11 | X8_07             |        0.53 |
| 2022-01-11 | X8_08             |        0.18 |
| 2022-01-11 | X8_09             |        0.98 |
| 2022-01-11 | X9_IC_03          |         0.00|
| 2022-01-11 | X10_01            |        0.00 |
| 2022-01-11 | X11_IC_07         |        0.11 |
| 2022-01-11 | X12_01            |     3178.98 |
+------------+-------------------+-------------+
21 rows in set (0.00 sec)

让我们先来看看X1_IC_03，

在X1_IC_03中，主要提供者是X1，它可能包含X1右侧的任何内容，比如X1%.So，我需要得到所有X1值的Total_Usage之和。所以我的输出如下，

select * from Daily_Usage;

+------------+-------------------+-------------+
| Date       | Provider          | Total_Usage |
+------------+-------------------+-------------+
| 2022-01-11 | X1                |       71.33 |
| 2022-01-11 | X2                |        0.00 |
| 2022-01-11 | X3                |       16.86 |
| 2022-01-11 | X4                |        0.00 |
| 2022-01-11 | X5                |        6.66 |
| 2022-01-11 | X6                |      3269.71|
| 2022-01-11 | X7                |        0.00 |
| 2022-01-11 | X8                |       19.84 |
| 2022-01-11 | X9                |         0.00|
| 2022-01-11 | X10               |        0.00 |
| 2022-01-11 | X11               |        0.11 |
| 2022-01-11 | X12               |     3178.98 |
+------------+-------------------+-------------+
21 rows in set (0.00 sec)

我将使用下面的查询获得这个输出，

select 
    Date, Provider, sum(Total_Usage) 
from 
    Daily_Usage 
where 
    Provider like 'X1%' or Provider like 'X2%';

但它没有归还我需要的东西。它只提供了以下X1的详细信息，

+------------+-------------+------------------+
| Date       | Provider    | sum(Total_Usage) |
+------------+-------------+------------------+
| 2022-01-11 | X1_IC_03    |            71.33 |
+------------+-------------+------------------+

提供程序名称也不应请求。有人能教我怎么做吗？

Answer 1

按组是你的正确选择。但你得小心点。您可以有不同的日期值(在您的情况下是相同的)。在这种情况下，脚本不知道要选择哪个日期。我用了max，因为你只有一个值。

select 
    max(Date), LEFT(provider,LOCATE('_',provider) - 1), sum(Total_Usage) 
from 
    Daily_Usage 
group by  LEFT(provider,LOCATE('_',provider) - 1)