某电商网站的数据库设计(8)——创建花费信息查询视图
admin
2024-02-28 01:30:08
0

某电商网站的数据库设计(8)——创建花费信息查询视图

目录

    • 某电商网站的数据库设计(8)——创建花费信息查询视图
        • 1、统计所有花费
        • 2、按日期分组统计花费信息
        • 3、按单品分组统计花费信息
        • 4、按品牌分组统计花费信息
        • 5、按小组分组统计花费信息
        • 6、按大组分组统计花费信息
        • 7、按平台分组统计花费信息

创建视图查询平台、单品、小组、大组、品牌等的花费数据,所用到的视图如下:

-- ============================================================
-- 各类花费信息统计视图
-- 1、统计所有花费
--    视图名:v_expense_all
--    说明:统计所有花费信息-- 2、按日期分组统计花费信息
--    视图名:v_expense_date
--    说明:统计每天的花费信息-- 3、按单品分组统计花费信息
--    视图名:v_expense_product
--    说明:统计每个单品每天的花费信息-- 4、按品牌分组统计花费信息
--    视图名:v_expense_brand
--    说明:统计每个品牌每天的花费信息-- 5、按小组分组统计花费信息
--    视图名:v_expense_team
--    说明:统计每个小组每天的花费信息-- 6、按大组分组统计花费信息
--    视图名:v_expense_largegroup
--    说明:统计每个大组每天的花费信息-- 7、按平台分组统计花费信息
--    视图名:v_expense_platform
--    说明:统计每个平台每天的花费信息
-- ============================================================

1、统计所有花费

创建视图的SQL语句如下:

-- 1、统计所有花费
-- 视图名:v_expense_all
-- 说明:统计所有花费信息
-- 视图包含:广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)DROP VIEW IF EXISTS v_expense_all;CREATE VIEW v_expense_all
AS
SELECT sum(advertising_expense) advertising_expense,sum(search_expense) search_expense,sum(small_expense) small_expense,sum(advertising_expense+search_expense+small_expense) total_expense
FROM v_expense
;

查询视图数据:

/*
-- 数据查询语句
select '总计',v_expense_all.* from v_expense_all;
*/mysql> select '总计',v_expense_all.* from v_expense_all;
+--------+---------------------+----------------+---------------+---------------+
| 总计   | advertising_expense | search_expense | small_expense | total_expense |
+--------+---------------------+----------------+---------------+---------------+
| 总计   |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+--------+---------------------+----------------+---------------+---------------+
1 row in set (0.01 sec)

2、按日期分组统计花费信息

创建视图的SQL语句如下:

-- 2、按日期分组统计花费信息
-- 视图名:v_expense_date
-- 说明:统计每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_date;CREATE VIEW v_expense_date
AS
SELECT v_expense.expense_date,SUM(advertising_expense) advertising_expense,SUM(search_expense) search_expense,SUM(small_expense) small_expense,SUM(advertising_expense+search_expense+small_expense) total_expense
FROM v_expense
GROUP BYexpense_date
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_date
union
select '总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_date-> union-> select '总计',v_expense_all.* from v_expense_all;
+---------------------+--------------+----------------+---------------+---------------+
| expense_date  | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+-------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 |     662859.36 |     374881.60 |       4460.00 |    1042200.96 |
| 2022-01-02 00:00:00 |     677963.04 |     368711.16 |       5160.00 |    1051834.20 |
| 2022-01-03 00:00:00 |     628863.73 |     352422.66 |       3870.00 |     985156.39 |
| 总计                |     1969686.13 |    1096015.42 |      13490.00 |    3079191.55 |
+---------------------+---------------+----------------+---------------+---------------+
4 rows in set (0.02 sec)

3、按单品分组统计花费信息

创建视图的SQL语句如下:

-- 3、按单品分组统计花费信息
-- 视图名:v_expense_product
-- 说明:统计每个单品每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           单品编号(product_no)
--           单品名称(product_name)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_product;CREATE VIEW v_expense_product
AS
SELECT e.expense_date expense_date,e.product_no product_no,p.product_name product_name,SUM(e.advertising_expense) advertising_expense,SUM(e.search_expense) search_expense,SUM(e.small_expense) small_expense,SUM(e.advertising_expense+search_expense+small_expense) total_expense
FROM v_expense e JOIN tb_product p
ON e.product_no=p.product_no
GROUP BYexpense_date,product_no,product_name
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_product
union
select '','','总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_product-> union-> select '','','总计',v_expense_all.* from v_expense_all;
+---------------------+------------+--------------------------+---------------------+----------------+---------------+---------------+
| expense_date        | product_no | product_name             | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+------------+--------------------------+---------------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 | DER-NS-01  | 戴尔笔记本               |            40581.30 |       26188.56 |          0.00 |      66769.86 |
| 2022-01-01 00:00:00 | DER-NS-02  | 戴尔平板                 |            48782.93 |       19860.05 |          0.00 |      68642.98 |
| 2022-01-01 00:00:00 | FZ-NS-01   | 方正台式机               |            33144.13 |       18539.13 |        950.00 |      52633.26 |
| 2022-01-01 00:00:00 | FZ-NS-02   | 方正一体机               |            38812.53 |       21472.97 |        950.00 |      61235.50 |
| 2022-01-01 00:00:00 | HP-NS-04   | 惠普笔记本               |            29319.97 |       18389.69 |          0.00 |      47709.66 |
| 2022-01-01 00:00:00 | HP-NS-05   | 惠普平板                 |            34602.23 |       20562.71 |          0.00 |      55164.94 |
| 2022-01-01 00:00:00 | JC-SF-01   | 京瓷复印机               |            70662.59 |       38791.64 |       1090.00 |     110544.23 |
| 2022-01-01 00:00:00 | JC-SF-02   | 京瓷一体机               |            53987.89 |       24890.48 |        610.00 |      79488.37 |
| 2022-01-01 00:00:00 | LX-HF-01   | 联想打印机               |            32485.81 |       13896.06 |          0.00 |      46381.87 |
| 2022-01-01 00:00:00 | LX-HF-02   | 联想三合一一体机         |            33939.12 |       27078.05 |          0.00 |      61017.17 |
| 2022-01-01 00:00:00 | SZ_HF-01   | 神州台式机               |            36021.46 |       22451.35 |        860.00 |      59332.81 |
| 2022-01-01 00:00:00 | SZ_HF-02   | 神州一体机               |            26461.68 |       15078.38 |          0.00 |      41540.06 |
| 2022-01-01 00:00:00 | XD-HF-01   | 兄弟打印机               |            29118.45 |       15896.87 |          0.00 |      45015.32 |
| 2022-01-01 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |            41230.86 |       24502.58 |          0.00 |      65733.44 |
| 2022-01-01 00:00:00 | XP-SR-01   | 夏普复印机               |            64686.89 |       31723.96 |          0.00 |      96410.85 |
| 2022-01-01 00:00:00 | XP-SR-02   | 夏普一体机               |            49021.52 |       35559.12 |          0.00 |      84580.64 |
| 2022-01-02 00:00:00 | DER-NS-01  | 戴尔笔记本               |            29435.84 |       17354.99 |          0.00 |      46790.83 |
| 2022-01-02 00:00:00 | DER-NS-02  | 戴尔平板                 |            47998.10 |       19180.48 |          0.00 |      67178.58 |
| 2022-01-02 00:00:00 | FZ-NS-01   | 方正台式机               |            33285.99 |       23608.02 |       1380.00 |      58274.01 |
| 2022-01-02 00:00:00 | FZ-NS-02   | 方正一体机               |            25688.02 |       24884.21 |       1010.00 |      51582.23 |
| 2022-01-02 00:00:00 | HP-NS-04   | 惠普笔记本               |            47435.14 |       15429.12 |          0.00 |      62864.26 |
| 2022-01-02 00:00:00 | HP-NS-05   | 惠普平板                 |            32290.01 |       15005.57 |          0.00 |      47295.58 |
| 2022-01-02 00:00:00 | JC-SF-01   | 京瓷复印机               |            65187.34 |       36782.10 |       1660.00 |     103629.44 |
| 2022-01-02 00:00:00 | JC-SF-02   | 京瓷一体机               |            42492.47 |       24321.58 |        630.00 |      67444.05 |
| 2022-01-02 00:00:00 | LX-HF-01   | 联想打印机               |            36870.97 |       19889.54 |          0.00 |      56760.51 |
| 2022-01-02 00:00:00 | LX-HF-02   | 联想三合一一体机         |            37581.28 |       25310.74 |          0.00 |      62892.02 |
| 2022-01-02 00:00:00 | SZ_HF-01   | 神州台式机               |            37916.20 |       19696.47 |        480.00 |      58092.67 |
| 2022-01-02 00:00:00 | SZ_HF-02   | 神州一体机               |            42532.92 |       21883.43 |          0.00 |      64416.35 |
| 2022-01-02 00:00:00 | XD-HF-01   | 兄弟打印机               |            35581.72 |       18774.67 |          0.00 |      54356.39 |
| 2022-01-02 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |            43268.97 |       22166.62 |          0.00 |      65435.59 |
| 2022-01-02 00:00:00 | XP-SR-01   | 夏普复印机               |            61809.83 |       30592.29 |          0.00 |      92402.12 |
| 2022-01-02 00:00:00 | XP-SR-02   | 夏普一体机               |            58588.24 |       33831.33 |          0.00 |      92419.57 |
| 2022-01-03 00:00:00 | DER-NS-01  | 戴尔笔记本               |            31505.31 |       18434.76 |          0.00 |      49940.07 |
| 2022-01-03 00:00:00 | DER-NS-02  | 戴尔平板                 |            34033.21 |       14792.88 |          0.00 |      48826.09 |
| 2022-01-03 00:00:00 | FZ-NS-01   | 方正台式机               |            27833.45 |       17500.83 |        490.00 |      45824.28 |
| 2022-01-03 00:00:00 | FZ-NS-02   | 方正一体机               |            41134.75 |       22165.21 |        990.00 |      64289.96 |
| 2022-01-03 00:00:00 | HP-NS-04   | 惠普笔记本               |            36337.13 |       15911.38 |          0.00 |      52248.51 |
| 2022-01-03 00:00:00 | HP-NS-05   | 惠普平板                 |            31630.86 |       22001.80 |          0.00 |      53632.66 |
| 2022-01-03 00:00:00 | JC-SF-01   | 京瓷复印机               |            71180.62 |       33937.96 |       1180.00 |     106298.58 |
| 2022-01-03 00:00:00 | JC-SF-02   | 京瓷一体机               |            53994.52 |       25587.22 |        640.00 |      80221.74 |
| 2022-01-03 00:00:00 | LX-HF-01   | 联想打印机               |            38329.77 |       18979.08 |          0.00 |      57308.85 |
| 2022-01-03 00:00:00 | LX-HF-02   | 联想三合一一体机         |            30459.01 |       25302.95 |          0.00 |      55761.96 |
| 2022-01-03 00:00:00 | SZ_HF-01   | 神州台式机               |            22733.20 |       14621.35 |        570.00 |      37924.55 |
| 2022-01-03 00:00:00 | SZ_HF-02   | 神州一体机               |            38457.84 |       21935.26 |          0.00 |      60393.10 |
| 2022-01-03 00:00:00 | XD-HF-01   | 兄弟打印机               |            35111.24 |       12863.69 |          0.00 |      47974.93 |
| 2022-01-03 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |            35641.55 |       25205.21 |          0.00 |      60846.76 |
| 2022-01-03 00:00:00 | XP-SR-01   | 夏普复印机               |            43598.61 |       31985.46 |          0.00 |      75584.07 |
| 2022-01-03 00:00:00 | XP-SR-02   | 夏普一体机               |            56882.66 |       31197.62 |          0.00 |      88080.28 |
|                     |            | 总计                     |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+---------------------+------------+--------------------------+---------------------+----------------+---------------+---------------+
49 rows in set (0.01 sec)

4、按品牌分组统计花费信息

创建视图的SQL语句如下:

-- 4、按品牌分组统计花费信息
-- 视图名:v_expense_brand
-- 说明:统计每个品牌每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           品牌编号(brand_no)
--           品牌名称(brand_name)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_brand;CREATE VIEW v_expense_brand
AS
SELECT e.expense_date expense_date,pb.brand_no brand_no,pb.brand_name brand_name,SUM(e.advertising_expense) advertising_expense,SUM(e.search_expense) search_expense,SUM(e.small_expense) small_expense,SUM(e.advertising_expense+search_expense+small_expense) total_expense
FROM v_expense e JOIN v_relation_product_brand pb
ON e.product_no=pb.product_no
GROUP BYexpense_date,brand_no,brand_name
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_brand
union
select '','','总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_brand-> union-> select '','','总计',v_expense_all.* from v_expense_all;
+---------------------+----------+------------+---------------------+----------------+---------------+---------------+
| expense_date        | brand_no | brand_name | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+----------+------------+---------------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 | B101     | 方正       |            71956.66 |       40012.10 |       1900.00 |     113868.76 |
| 2022-01-01 00:00:00 | B102     | 神州       |            62483.14 |       37529.73 |        860.00 |     100872.87 |
| 2022-01-01 00:00:00 | B201     | 戴尔       |            89364.23 |       46048.61 |          0.00 |     135412.84 |
| 2022-01-01 00:00:00 | B202     | 惠普       |            63922.20 |       38952.40 |          0.00 |     102874.60 |
| 2022-01-01 00:00:00 | B301     | 联想       |            66424.93 |       40974.11 |          0.00 |     107399.04 |
| 2022-01-01 00:00:00 | B302     | 兄弟       |            70349.31 |       40399.45 |          0.00 |     110748.76 |
| 2022-01-01 00:00:00 | B401     | 京瓷       |           124650.48 |       63682.12 |       1700.00 |     190032.60 |
| 2022-01-01 00:00:00 | B402     | 夏普       |           113708.41 |       67283.08 |          0.00 |     180991.49 |
| 2022-01-02 00:00:00 | B101     | 方正       |            58974.01 |       48492.23 |       2390.00 |     109856.24 |
| 2022-01-02 00:00:00 | B102     | 神州       |            80449.12 |       41579.90 |        480.00 |     122509.02 |
| 2022-01-02 00:00:00 | B201     | 戴尔       |            77433.94 |       36535.47 |          0.00 |     113969.41 |
| 2022-01-02 00:00:00 | B202     | 惠普       |            79725.15 |       30434.69 |          0.00 |     110159.84 |
| 2022-01-02 00:00:00 | B301     | 联想       |            74452.25 |       45200.28 |          0.00 |     119652.53 |
| 2022-01-02 00:00:00 | B302     | 兄弟       |            78850.69 |       40941.29 |          0.00 |     119791.98 |
| 2022-01-02 00:00:00 | B401     | 京瓷       |           107679.81 |       61103.68 |       2290.00 |     171073.49 |
| 2022-01-02 00:00:00 | B402     | 夏普       |           120398.07 |       64423.62 |          0.00 |     184821.69 |
| 2022-01-03 00:00:00 | B101     | 方正       |            68968.20 |       39666.04 |       1480.00 |     110114.24 |
| 2022-01-03 00:00:00 | B102     | 神州       |            61191.04 |       36556.61 |        570.00 |      98317.65 |
| 2022-01-03 00:00:00 | B201     | 戴尔       |            65538.52 |       33227.64 |          0.00 |      98766.16 |
| 2022-01-03 00:00:00 | B202     | 惠普       |            67967.99 |       37913.18 |          0.00 |     105881.17 |
| 2022-01-03 00:00:00 | B301     | 联想       |            68788.78 |       44282.03 |          0.00 |     113070.81 |
| 2022-01-03 00:00:00 | B302     | 兄弟       |            70752.79 |       38068.90 |          0.00 |     108821.69 |
| 2022-01-03 00:00:00 | B401     | 京瓷       |           125175.14 |       59525.18 |       1820.00 |     186520.32 |
| 2022-01-03 00:00:00 | B402     | 夏普       |           100481.27 |       63183.08 |          0.00 |     163664.35 |
|                     |          | 总计       |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+---------------------+----------+------------+---------------------+----------------+---------------+---------------+
25 rows in set (0.02 sec)

5、按小组分组统计花费信息

创建视图的SQL语句如下:

-- 5、按小组分组统计花费信息
-- 视图名:v_expense_team
-- 说明:统计每个小组每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           小组编号(team_no)
--           小组名称(team_name)
--           小组长姓名(team_header)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_team;CREATE VIEW v_expense_team
AS
SELECT e.expense_date expense_date,pt.team_no team_no,pt.team_name team_name,pt.team_header team_header,SUM(e.advertising_expense) advertising_expense,SUM(e.search_expense) search_expense,SUM(e.small_expense) small_expense,SUM(e.advertising_expense+search_expense+small_expense) total_expense
FROM v_expense e JOIN v_relation_product_team pt
ON e.product_no=pt.product_no
GROUP BYexpense_date,team_no,team_name,team_header
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_team
union
select '','','','总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_team-> union-> select '','','','总计',v_expense_all.* from v_expense_all;
+---------------------+-----------+-----------------------+-------------+---------------------+----------------+---------------+---------------+
| expense_date        | team_no   | team_name             | team_header | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+-----------+-----------------------+-------------+---------------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |           134439.80 |       77541.83 |       2760.00 |     214741.63 |
| 2022-01-01 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |           153286.43 |       85001.01 |          0.00 |     238287.44 |
| 2022-01-01 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |           136774.24 |       81373.56 |          0.00 |     218147.80 |
| 2022-01-01 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |           238358.89 |      130965.20 |       1700.00 |     371024.09 |
| 2022-01-02 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |           139423.13 |       90072.13 |       2870.00 |     232365.26 |
| 2022-01-02 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |           157159.09 |       66970.16 |          0.00 |     224129.25 |
| 2022-01-02 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |           153302.94 |       86141.57 |          0.00 |     239444.51 |
| 2022-01-02 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |           228077.88 |      125527.30 |       2290.00 |     355895.18 |
| 2022-01-03 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |           130159.24 |       76222.65 |       2050.00 |     208431.89 |
| 2022-01-03 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |           133506.51 |       71140.82 |          0.00 |     204647.33 |
| 2022-01-03 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |           139541.57 |       82350.93 |          0.00 |     221892.50 |
| 2022-01-03 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |           225656.41 |      122708.26 |       1820.00 |     350184.67 |
|                     |           |                       | 总计        |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+---------------------+-----------+-----------------------+-------------+---------------------+----------------+---------------+---------------+
13 rows in set (0.02 sec)

6、按大组分组统计花费信息

创建视图的SQL语句如下:

-- 6、按大组分组统计花费信息
-- 视图名:v_expense_largegroup
-- 说明:统计每个大组每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           大组编号(lg_no)
--           大组名称(lg_name)
--           大组长、主管姓名(lg_header)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_largegroup;CREATE VIEW v_expense_largegroup
AS
SELECT e.expense_date expense_date,ptl.lg_no lg_no,ptl.lg_name lg_name,ptl.lg_header lg_header,SUM(e.advertising_expense) advertising_expense,SUM(e.search_expense) search_expense,SUM(e.small_expense) small_expense,SUM(e.advertising_expense+search_expense+small_expense) total_expense
FROM v_expense e JOIN v_relation_product_team_largegroup ptl
ON e.product_no=ptl.product_no
GROUP BYexpense_date,lg_no,lg_name,lg_header
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_largegroup
union
select '','','','总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_largegroup-> union-> select '','','','总计',v_expense_all.* from v_expense_all;
+---------------------+-------+--------------------------+-----------+---------------------+----------------+---------------+---------------+
| expense_date        | lg_no | lg_name                  | lg_header | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+-------+--------------------------+-----------+---------------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 | DZ01  | 电脑销售大组             | 刘备      |           287726.23 |      162542.84 |       2760.00 |     453029.07 |
| 2022-01-01 00:00:00 | DZ02  | 打印设备销售大组         | 曹操      |           375133.13 |      212338.76 |       1700.00 |     589171.89 |
| 2022-01-02 00:00:00 | DZ01  | 电脑销售大组             | 刘备      |           296582.22 |      157042.29 |       2870.00 |     456494.51 |
| 2022-01-02 00:00:00 | DZ02  | 打印设备销售大组         | 曹操      |           381380.82 |      211668.87 |       2290.00 |     595339.69 |
| 2022-01-03 00:00:00 | DZ01  | 电脑销售大组             | 刘备      |           263665.75 |      147363.47 |       2050.00 |     413079.22 |
| 2022-01-03 00:00:00 | DZ02  | 打印设备销售大组         | 曹操      |           365197.98 |      205059.19 |       1820.00 |     572077.17 |
|                     |       |                          | 总计      |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+---------------------+-------+--------------------------+-----------+---------------------+----------------+---------------+---------------+
7 rows in set (0.02 sec)

7、按平台分组统计花费信息

创建视图的SQL语句如下:

-- 7、按平台分组统计花费信息
-- 视图名:v_expense_platform
-- 说明:统计每个平台每天的花费信息
-- 视图包含:花费产生日期(expense_date)
--           平台编号(platform_no)
--           平台名称(platform_name)
--           广告花费(advertising_expense)
--           搜索花费(search_expense)
--           小额花费(small_expense)
--           所有花费(total_expense)
DROP VIEW IF EXISTS v_expense_platform;CREATE VIEW v_expense_platform
AS
SELECT e.expense_date expense_date,e.platform_no platform_no,p.platform_name platform_name,SUM(e.advertising_expense) advertising_expense,SUM(e.search_expense) search_expense,SUM(e.small_expense) small_expense,SUM(e.advertising_expense+search_expense+small_expense) total_expense
FROM v_expense e JOIN tb_platform p
ON e.platform_no=p.platform_no
GROUP BYexpense_date,platform_no,platform_name
;

查询视图数据:

/*
-- 数据查询语句
select * from v_expense_platform
union
select '','','总计',v_expense_all.* from v_expense_all;
*/mysql> select * from v_expense_platform-> union-> select '','','总计',v_expense_all.* from v_expense_all;
+---------------------+-------------+---------------+---------------------+----------------+---------------+---------------+
| expense_date        | platform_no | platform_name | advertising_expense | search_expense | small_expense | total_expense |
+---------------------+-------------+---------------+---------------------+----------------+---------------+---------------+
| 2022-01-01 00:00:00 | JD          | 京东          |           336513.34 |      202545.71 |       1700.00 |     540759.05 |
| 2022-01-01 00:00:00 | TM          | 天猫          |           326346.02 |      172335.89 |       2760.00 |     501441.91 |
| 2022-01-02 00:00:00 | JD          | 京东          |           346497.64 |      178066.11 |       2290.00 |     526853.75 |
| 2022-01-02 00:00:00 | TM          | 天猫          |           331465.40 |      190645.05 |       2870.00 |     524980.45 |
| 2022-01-03 00:00:00 | JD          | 京东          |           311922.97 |      182451.33 |       1820.00 |     496194.30 |
| 2022-01-03 00:00:00 | TM          | 天猫          |           316940.76 |      169971.33 |       2050.00 |     488962.09 |
|                     |             | 总计          |          1969686.13 |     1096015.42 |      13490.00 |    3079191.55 |
+---------------------+-------------+---------------+---------------------+----------------+---------------+---------------+
7 rows in set (0.03 sec)

相关内容

热门资讯

安卓双系统添加应用,轻松实现多... 你有没有想过,你的安卓手机里可以同时运行两个系统呢?听起来是不是很酷?想象一边是熟悉的安卓系统,一边...
pipo安卓进系统慢,探究pi... 最近是不是发现你的Pipo安卓系统更新或者运行起来特别慢?别急,今天就来给你好好分析分析这个问题,让...
怎样使用安卓手机系统,安卓手机... 你有没有发现,安卓手机已经成为我们生活中不可或缺的一部分呢?从早晨闹钟响起,到晚上睡前刷剧,安卓手机...
双系统安卓安装caj,轻松实现... 你有没有想过,你的安卓手机里装上双系统,是不是就能同时享受安卓和Windows系统的乐趣呢?没错,这...
安卓使用ios系统教程,安卓用... 你是不是也和我一样,对安卓手机上的iOS系统充满了好奇?想要体验一下苹果的优雅和流畅?别急,今天我就...
安卓系统gps快速定位,畅享便... 你有没有遇到过这样的情况:手机里装了各种地图导航软件,但每次出门前都要等上好几分钟才能定位成功,急得...
安卓手机系统更新原理,原理与流... 你有没有发现,你的安卓手机最近是不是总在提醒你更新系统呢?别急,别急,让我来给你揭秘一下安卓手机系统...
安卓系统通知管理,全面解析与优... 你有没有发现,手机里的通知就像是一群调皮的小精灵,时不时地跳出来和你互动?没错,说的就是安卓系统的通...
安卓系统手机哪买,揭秘哪里购买... 你有没有想过,拥有一部安卓系统手机是多么酷的事情呢?想象你可以自由安装各种应用,不受限制地探索各种功...
安卓系统 ipv4,基于安卓系... 你知道吗?在智能手机的世界里,有一个系统可是无人不知、无人不晓,那就是安卓系统。而在这个庞大的安卓家...
目前安卓是什么系统,探索安卓系... 亲爱的读者,你是否曾好奇过,如今安卓系统究竟是什么模样?在这个科技飞速发展的时代,操作系统如同人体的...
安卓6.0系统比5.0,从5.... 你有没有发现,自从手机更新了安卓6.0系统,感觉整个人都清爽了不少呢?没错,今天咱们就来聊聊这个话题...
安卓2.36系统升级,功能革新... 你知道吗?最近安卓系统又来了一次大变身,那就是安卓2.36系统升级!这可不是一个小打小闹的更新,而是...
安卓系统源码怎么打开,并可能需... 你有没有想过,安卓系统的源码就像是一扇神秘的门,隐藏着无数的技术秘密?想要打开这扇门,你得掌握一些小...
安卓8.0系统体验视频,智能革... 你有没有听说安卓8.0系统最近可是火得一塌糊涂啊!作为一个紧跟科技潮流的数码达人,我当然要来给你好好...
宣传系统漫画app安卓,探索安... 亲爱的读者们,你是否曾在某个午后,百无聊赖地打开手机,想要寻找一些轻松愉悦的读物?今天,我要给你介绍...
鸿蒙替换安卓系统吗,开启智能生... 你知道吗?最近科技圈里可是炸开了锅,因为华为的新操作系统鸿蒙系统,据说要大举进军手机市场,替换掉安卓...
手机安卓系统深度清理,解锁手机... 手机里的东西是不是越来越多,感觉就像一个装满了杂物的储物柜?别急,今天就来教你一招——手机安卓系统深...
安卓上的windows系统,融... 你有没有想过,在安卓手机上也能体验到Windows系统的魅力呢?没错,这就是今天我要跟你分享的神奇故...
安卓系统焦点变化事件,Andr... 你知道吗?在安卓系统的世界里,最近发生了一件超级有趣的事情——焦点变化事件。这可不是什么小打小闹,它...