MySQL跨表查询客户月度总销售数据超时及需求

Hey there! Let's tackle this problem step by step—since your dataset isn't huge (450 customers, 800 monthly rows), the timeout is almost certainly due to missing indexes or an inefficient query structure. Here's how to fix it and get your desired pivot-style report:

First, let's eliminate the root cause of the timeout. You need targeted indexes to speed up table joins and date filtering:

• For Customer_data, ensure custcode is either the primary key (ideal) or has an index. If not, create one:

  CREATE INDEX idx_customer_custcode ON Customer_data(custcode);
  

• For delivery_data, add a composite index on custcode (to join quickly with customer data) and date (to filter months efficiently):

  CREATE INDEX idx_delivery_custcode_date ON delivery_data(custcode, date);
  

These indexes will drastically cut down the time MySQL spends scanning rows and matching records between tables.

Your desired output is a pivot table with customer names as rows and months as columns. Here are two approaches depending on how flexible you need the date range to be:

Option 1: Static Pivot (Fixed Month Range) #

If you want to hardcode a specific set of months (like Jan-18 to Apr-18), use CASE WHEN to calculate monthly totals:

SELECT
  cd.name AS `Customer Name`,
  SUM(CASE WHEN DATE_FORMAT(dd.date, '%b-%y') = 'Jan-18' THEN dd.quantity ELSE 0 END) AS `Jan-18`,
  SUM(CASE WHEN DATE_FORMAT(dd.date, '%b-%y') = 'Feb-18' THEN dd.quantity ELSE 0 END) AS `Feb-18`,
  SUM(CASE WHEN DATE_FORMAT(dd.date, '%b-%y') = 'Mar-18' THEN dd.quantity ELSE 0 END) AS `Mar-18`,
  SUM(CASE WHEN DATE_FORMAT(dd.date, '%b-%y') = 'Apr-18' THEN dd.quantity ELSE 0 END) AS `Apr-18`
  -- Add more month columns as needed
FROM Customer_data cd
LEFT JOIN delivery_data dd ON cd.custcode = dd.custcode
WHERE dd.date BETWEEN '2018-01-01' AND '2018-04-30' -- Filter your date range here
GROUP BY cd.custcode, cd.name
ORDER BY cd.name;

The LEFT JOIN ensures you include customers with no sales in the selected months (they'll show 0 for those columns).

Option 2: Dynamic Pivot (Flexible Month Range) #

If you want to let users input any start/end month without rewriting the query, use a stored procedure that generates pivot columns dynamically:

DELIMITER //
CREATE PROCEDURE GetMonthlySales(IN start_date DATE, IN end_date DATE)
BEGIN
  -- Generate list of month columns dynamically
  SET @cols = NULL;
  SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN DATE_FORMAT(date, ''%b-%y'') = ''', DATE_FORMAT(date, '%b-%y'), ''' THEN quantity ELSE 0 END) AS `', DATE_FORMAT(date, '%b-%y'), '`')
  ) INTO @cols
  FROM delivery_data
  WHERE date BETWEEN start_date AND end_date;

  -- Build and execute the full query
  SET @query = CONCAT('
    SELECT cd.name AS `Customer Name`, ', @cols, '
    FROM Customer_data cd
    LEFT JOIN delivery_data dd ON cd.custcode = dd.custcode
    WHERE (dd.date BETWEEN ''', start_date, ''' AND ''', end_date, ''') OR dd.date IS NULL
    GROUP BY cd.custcode, cd.name
    ORDER BY cd.name
  ');

  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Call the procedure with your desired date range like this:

CALL GetMonthlySales('2018-01-01', '2018-04-30');

This will automatically create columns for every month in the specified range, even if some months have no sales for certain customers.

After adding indexes, run EXPLAIN on your query to confirm MySQL is using them efficiently:

EXPLAIN
SELECT
  cd.name AS `Customer Name`,
  SUM(CASE WHEN DATE_FORMAT(dd.date, '%b-%y') = 'Jan-18' THEN dd.quantity ELSE 0 END) AS `Jan-18`
FROM Customer_data cd
LEFT JOIN delivery_data dd ON cd.custcode = dd.custcode
WHERE dd.date BETWEEN '2018-01-01' AND '2018-01-31'
GROUP BY cd.custcode, cd.name;

Look for Using index or Using where; Using index in the Extra column for the delivery_data row—this means your composite index is working as intended.

内容的提问来源于stack exchange，提问作者nk369