同类型列数据求和及MySQL advance预支款表技术咨询

Let's break down your needs into two main parts: summing same-data-type columns in the advance table, and setting up monthly deduction logic for these advance payments. Here's how to handle both:

Since most numeric columns here are int(11) or tinyint, summing them is straightforward with the SUM() aggregate function. Below are common use cases based on typical business needs:

1. Total Sum of All Advance Amounts #

If you want the total amount of all valid advance payments (excluding inactive/deleted records):

SELECT SUM(Amount) AS Total_Advance_Amount
FROM advance
WHERE IsActive = 1 AND IsDeleted = 0;

2. Sum per Employee #

To get a breakdown of how much each employee has taken in advances:

SELECT EmployeeID, SUM(Amount) AS Total_Advance_Per_Employee
FROM advance
WHERE IsActive = 1 AND IsDeleted = 0
GROUP BY EmployeeID;

3. Monthly/Yearly Sum #

For payroll reconciliation or reporting, you can group sums by month and year:

SELECT Year, Month, SUM(Amount) AS Monthly_Advance_Total
FROM advance
WHERE IsActive = 1 AND IsDeleted = 0
GROUP BY Year, Month
ORDER BY Year DESC, Month DESC;

Note on Other Numeric Columns #

Flag columns like Rule, IsPaid, or IsActive can also be summed to get counts (e.g., SUM(IsPaid) gives the number of fully paid advances):

SELECT SUM(IsPaid) AS Number_of_Paid_Advances
FROM advance;

Since this table tracks advance loans that require monthly repayments, here are practical implementations for common scenarios:

Basic Fixed-Amount Monthly Deduction #

If you deduct a fixed amount (e.g., 500) from each unpaid advance every month:

1. First, verify which records need deduction:

SELECT AdvanceID, EmployeeID, Amount
FROM advance
WHERE IsPaid = 0 AND IsActive = 1 AND IsDeleted = 0;

2. Execute the deduction update (with transaction safety):

START TRANSACTION; -- Ensures no partial updates if something goes wrong

UPDATE advance
SET 
  Amount = Amount - 500,
  UpdatedDate = CURRENT_TIMESTAMP,
  IsPaid = CASE WHEN (Amount - 500) <= 0 THEN 1 ELSE 0 END -- Mark as fully paid if balance hits 0
WHERE IsPaid = 0 AND IsActive = 1 AND IsDeleted = 0;

COMMIT;

Deduction Based on Rule Field #

If the Rule field defines a deduction percentage (e.g., Rule=1 = 10% deduction, Rule=2 = 20%):

START TRANSACTION;

UPDATE advance
SET 
  Amount = Amount * (1 - (Rule * 0.1)), -- Calculate remaining amount after percentage deduction
  UpdatedDate = CURRENT_TIMESTAMP,
  IsPaid = CASE WHEN (Amount * (1 - (Rule * 0.1))) <= 0 THEN 1 ELSE 0 END
WHERE IsPaid = 0 AND IsActive = 1 AND IsDeleted = 0;

COMMIT;

Automate Deductions with MySQL Event Scheduler #

To avoid running the query manually each month, set up an automated event:

1. Enable the event scheduler (if not already active):

SET GLOBAL event_scheduler = ON;

2. Create the monthly event (runs on the 1st of every month at midnight):

CREATE EVENT monthly_advance_deduction
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 00:00:00'
DO
BEGIN
  START TRANSACTION;
  
  UPDATE advance
  SET 
    Amount = Amount - 500,
    UpdatedDate = CURRENT_TIMESTAMP,
    IsPaid = CASE WHEN (Amount - 500) <= 0 THEN 1 ELSE 0 END
  WHERE IsPaid = 0 AND IsActive = 1 AND IsDeleted = 0;
  
  COMMIT;
END;

Key Notes #

• Always back up your data before running mass update queries.
• Adjust the deduction amount/percentage and event schedule to match your actual business rules.
• If you need to track each deduction individually (instead of updating the Amount directly), consider adding a separate deduction_log table to log each transaction with timestamps and amounts.

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