php - MySql 使用现有表中的列和来自另一个表的不同查找创建新表

Question

我在数据库中有 2 个现有表，即 Table_1 和 Table_2。

我需要将 Table_1 和 Table_2 的信息合并到一个名为 Combined_table 的新表中。

Table_1 列名和值可以直接复制到Combined_table。

然后，我需要在 Table_2 中添加名称包含来自 Service Description 的不同值的列。每个不同的值必须成为添加到 Combined_table 的列名。

然后，这些新添加的列必须将它们各自的值添加到正确的行中。每一行都可以通过所有表中可用的 MSISDN 列来标识。

Table_1

|MSISDN        |Abar Date  |Upgrd |Pack Code |Tariff               |Avg Spend |SIM Number
|--------------|-----------|------|----------|---------------------|----------|---------------------|
|9602005689344 |18-Mar-15  |Y     |MDA       |Machine2Machine Data |32.56     |89490000000000001215 |
|9602005926494 |06-Feb-15  |Y     |MLT       |Machine2Machine Lite |27.38     |89490000000000004457 |
|9602005926509 |06-Feb-15  |Y     |MLT       |Machine2Machine Lite |10.1      |89490000000000045677 |
|9602005926524 |06-Feb-15  |Y     |MLT       |Machine2Machine Lite |31.77     |89490000000000002887 |
|9602005926539 |18-Mar-15  |Y     |MDA       |Machine2Machine Data |32.36     |94900000000000100212 |
|9602006330948 |11-Mar-16  |N     |MLT       |Machine2Machine Lite |4.39      |89460000000015600111 |
|9602006330950 |11-Mar-16  |N     |MLT       |Machine2Machine Lite |4.39      |89940000000000426577 |
|--------------|-----------|------|----------|---------------------|----------|---------------------|

表_2

|MSISDN         |Service Description               |Value     |
|---------------|----------------------------------|----------|
|9602005689344  |100MB                             |25.44     |
|9602005689344  |Basic Data 9600                   |0.00      |
|9602005689344  |Basic Telephony                   |6.58      |
|9602005689344  |Ebilling - Corporate CD Extract   |0.00      |
|9602005689344  |Unrestricted APN                  |0.00      |
|9602005926494  |100MB                             |25.44     |
|9602005926494  |Basic Data 9600                   |0.00      |
|9602005926494  |Basic Telephony                   |4.39      |
|9602005926494  |Ebilling - Corporate CD Extract   |0.00      |
|9602005926494  |Unrestricted APN                  |0.00      |
|9602005926509  |Basic Data 9600                   |0.00      |
|9602005926509  |Basic Telephony                   |4.39      |
|9602005926509  |Ebilling - Corporate CD Extract   |0.00      |
|9602005926509  |My Meg 0                          |0.00      |
|9602005926509  |Unrestricted APN                  |0.00      |
|9602005926524  |100MB                             |25.44     |
|9602005926524  |Basic Data 9600                   |0.00      |
|9602005926524  |Basic Telephony                   |4.39      |
|9602005926524  |Ebilling - Corporate CD Extract   |0.00      |
|9602005926524  |Unrestricted APN                  |0.00      |
|9602005926539  |100MB                             |25.44     |
|9602005926539  |Basic Data 9600                   |0.00      |
|9602005926539  |Basic Telephony                   |6.58      |
|9602005926539  |Ebilling - Corporate CD Extract   |0.00      |
|9602005926539  |Unrestricted APN                  |0.00      |
|9602006330948  |Basic Data 9600                   |0.00      |
|9602006330948  |Basic Telephony                   |4.39      |
|9602006330948  |Caller Identity FREE              |0.00      |
|9602006330948  |Ebilling - Corporate CD Extract   |0.00      |
|9602006330948  |My Meg 0                          |0.00      |
|9602006330948  |Promotional My Gig 1 - 24 Months  |0.00      |
|9602006330948  |SMS Mobile Originating            |0.00      |
|9602006330948  |Unrestricted APN                  |0.00      |
|9602006330950  |Basic Data 9600                   |0.00      |
|9602006330950  |Basic Telephony                   |4.39      |
|9602006330950  |Caller Identity FREE              |0.00      |
|9602006330950  |Ebilling - Corporate CD Extract   |0.00      |
|9602006330950  |My Meg 0                          |0.00      |
|9602006330950  |Promotional My Gig 1 - 24 Months  |0.00      |
|9602006330950  |SMS Mobile Originating            |0.00      |
|9602006330950  |Unrestricted APN                  |0.00      |
|---------------|----------------------------------|----------|

Combined_table 的示例。

|MSISDN         |Abar Date  |Upgrd  |Pack   |Code Tariff            |Avg Spend  |SIM Number             |100MB  |Basic Data 9600    |Basic Telephony    |Caller Identity FREE   |Ebilling - Corporate CD Extract    |My Meg 0   |Promotional My Gig 1 - 24 Months   |SMS Mobile Originating |Unrestricted APN   |
|9602005689344  |18-Mar-15  |Y      |MDA    |Machine2Machine Data   |32.56      |89490000000000001215   |25.44  |0.00               |6.58               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602005926494  |06-Feb-15  |Y      |MLT    |Machine2Machine Lite   |27.38      |89490000000000004457   |25.44  |0.00               |4.39               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602005926509  |06-Feb-15  |Y      |MLT    |Machine2Machine Lite   |10.10      |89490000000000045677   |0.00   |0.00               |4.39               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602005926524  |06-Feb-15  |Y      |MLT    |Machine2Machine Lite   |31.77      |89490000000000002887   |25.44  |0.00               |4.39               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602005926539  |18-Mar-15  |Y      |MDA    |Machine2Machine Data   |32.36      |89490000000000100212   |25.44  |0.00               |6.58               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602006330948  |11-Mar-16  |N      |MLT    |Machine2Machine Lite   |4.39       |89460000000015600111   |0.00   |0.00               |4.39               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |
|9602006330950  |11-Mar-16  |N      |MLT    |Machine2Machine Lite   |4.39       |89940000000000426577   |0.00   |0.00               |4.39               |0.00                   |0.00                               |0.00       |0.00                               |0.00                   |0.00               |

我不知道如何使用 PHP 创建表格。我想过执行单个 MySQL 请求来创建和添加数据，但不知道如何开始。

任何建议都会很有帮助。

Answer 1

如果您已经知道列名(即不同的描述)，那么您可以创建一个表并编写一个 SELECT 查询，如下所示:

SELECT MSISDN, Abar Date, Upgrd, Pack Code, Tariff, Avg Spend, SIM Number,
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = '100MB') AS 100MB,
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Basic Data 9600') AS 'Basic Data 9600',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Basic Telephony') AS 'Basic Telephony',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Ebilling - Corporate CD Extract') AS 'Ebilling - Corporate CD Extract',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Unrestricted APN') AS 'Unrestricted APN'
FROM Table_1 t1;

您可以使用这些列创建一个表并使用 INSERT INTO .. SELECT 语法，例如

INSERT INTO table_3
SELECT MSISDN, Abar Date, Upgrd, Pack Code, Tariff, Avg Spend, SIM Number,
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = '100MB') AS 100MB,
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Basic Data 9600') AS 'Basic Data 9600',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Basic Telephony') AS 'Basic Telephony',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Ebilling - Corporate CD Extract') AS 'Ebilling - Corporate CD Extract',
(SELECT COALESCE(SUM(VALUE), 0) FROM Table_2 WHERE MSISDN = t1.MSISDN AND description = 'Unrestricted APN') AS 'Unrestricted APN'
FROM Table_1 t1;