为每个用户获取过去 3 个月的数据作为单独的列而不是行 [英] Fetch last 3 month data as separate columns not rows for each user

问题描述

我有一个 mysql 表bf_monthly_bill"，其中针对另一个表bf_users"中每个用户的数据按月存储.

i have a mysql table "bf_monthly_bill" in which data against each users from another table "bf_users" is stored month wise.

我的 bf_users 表的结构是

Structure of my bf_users table is

id   | display_name
1    | ABC
2    | DEF
3    | GHI
4    | JKL

bf_monthly_bill 的结构是

Structure of bf_monthly_bill is

company_id   | package_price  | bill_month
1            | 500            | Jan2k19
2            | 300            | Jan2k19
3            | 900            | Jan2k19
1            | 200            | Feb2k19
3            | 542            | Feb2k19
1            | 500            | Mar2k19
2            | 300            | Mar2k19
3            | 900            | Mar2k19
4            | 200            | Mar2k19

我想要每个公司名称以及每个账单月份的 package_price，例如

I want each company name with its package_price from each bill month e.g

company_name   | first_month_bill  | second_month_bill |third_month_bill 
ABC            | 500               | 200               |   500 
DEF            | 300               | 0 or Null         |   300
GHI            | 900               | 542               |   900
JKL            | 0 or NUll         | 0 or Null         |   200

我尝试过的是

SELECT bf_users.display_name,first_month.package_price firstt_bill,second_month.package_price second_bill,third_month.package_price third_bill from bf_monthly_bill 
AS first_month 
JOIN bf_monthly_bill AS second_month ON first_month.company_id=second_month.company_id
JOIN bf_monthly_bill AS third_month ON first_month.company_id=third_month.company_id
JOIN bf_users ON bf_users.id=first_month.company_id
Where first_month.bill_month='Jan2k19' AND second_month.bill_month='Feb2k19' AND third_month.bill_month='Mar2k19'

这个查询的问题是它只给了我每个月存在的那些公司及其各自的账单

The issue with this query is it gives me only those companies and their respective bills that are present against each month

推荐答案

1. 使用 LEFT JOIN 确保即使没有任何月份的账单，您也可以获得完整的公司列表.
2. 您必须手动或使用脚本创建 n 列.
3. 你可以做简单的连接，不需要子查询.
4. 使用 GROUP BY 为每个公司生成单个记录.
5. 使用CASE WHEN 或IF ELSE 创建n 列并仅从相关行中选取数据.它可以是第一行或任何行，因此使用聚合函数选择数据，即使它位于组的任何行，通过将其他行值设为 NULL 作为聚合函数排除空值.

1. Use LEFT JOIN to make sure, you get complete list of companies even though there is no bill for any month.
2. You have to create n columns manually or using a script.
3. You can just do simple join, no subquery is required.
4. Use GROUP BY to generate single record per company.
5. Use CASE WHEN or IF ELSE to create n columns and pick data from relevent row only. It can be first or any row, so use aggregate function to pick data even it is at any row for a group by making other row values as NULL as aggregate function exclude null values.

SELECT 
    u.display_name,
    SUM(IF( bill_month='Jan2k19', package_price, NULL)) as first_bill,
    SUM(IF( bill_month='Feb2k19', package_price, NULL)) as second_bill,
    SUM(IF( bill_month='Mar2k19', package_price, NULL)) as third_bill
FROM bf_users u 
LEFT JOIN bf_monthly_bill b on u.id=b.company_id
GROUP BY u.display_name;

这篇关于为每个用户获取过去 3 个月的数据作为单独的列而不是行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！