求助：MySQL查询无法为Tkinter发票系统填充多城市多KM区间结果

问题根源 #

原SQL查询通过WHERE city like '%XB' and KM between 1 and 3限制了仅统计DXB城市、KM1-3区间的数据，且未对城市和KM区间进行分组，无法展示多维度统计结果。

修改方案 #

1. 调整SQL查询逻辑：

   • 移除限制城市和KM范围的过滤条件（如需保留特定城市可按需添加）
   • 使用CASE WHEN将KM划分为指定区间（1-3、4-5、6-7）
   • 通过GROUP BY按城市和KM区间分组统计
   • 确保total_amount在分组后取有效值（如MAX/AVG，假设同一城市+KM区间内费率一致）

2. 更新PDF表头：添加KM区间列，对应新增的统计维度

修改后的代码片段 #

替换原generateInvoice函数中的SQL查询及表头部分：

def generateInvoice():
    # Connect to the MySQL database
    conn = mysql.connector.connect(user='root', password='', host='localhost', database='magnetico')
    cursor = conn.cursor()

    # 按城市和KM区间分组统计的SQL
    cursor.execute("""
        SELECT 
            city,
            CASE 
                WHEN KM BETWEEN 1 AND 3 THEN '1-3'
                WHEN KM BETWEEN 4 AND 5 THEN '4-5'
                WHEN KM BETWEEN 6 AND 7 THEN '6-7'
                ELSE 'Other' 
            END AS km_range,
            COUNT(KM) AS quantity,
            MAX(total_amount) AS rate,
            MAX(total_amount)*COUNT(KM) AS amount,
            MAX(total_amount)*COUNT(KM) AS taxable_value,
            ROUND(MAX(total_amount)*COUNT(KM)*0.05,2) AS vat_amount,
            MAX(total_amount)*COUNT(KM)*1.05 AS grand_total
        FROM perorder
        GROUP BY city, km_range
        ORDER BY city, km_range
    """)

    # Fetch the results
    results = cursor.fetchall()

    # Create the PDF file
    pdf_file = "invoice.pdf"
    doc = SimpleDocTemplate(pdf_file, pagesize=letter)

    # 更新表头，添加KM区间列
    table_data = []
    table_data.append(['City', 'KM Range','Quantity', 'Rate', 'Amount', 'Taxable Value','5% VAT', 'Grand Total'])
    for result in results:
        table_data.append([result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]])

    # Create the table
    table = Table(table_data)

    # Set the table style
    table.setStyle(TableStyle([
        ('INNERGRID', (0,0), (-1,-1), 0.25, colors.black),
        ('BOX', (0,0), (-1,-1), 0.25, colors.black)
    ]))

    # Build the document
    doc.build([table])
    
    # 关闭数据库连接
    cursor.close()
    conn.close()

关键说明 #

• CASE WHEN：将KM值映射为指定区间字符串，实现按区间分组统计
• GROUP BY city, km_range：确保每个城市的每个KM区间都生成独立统计行
• MAX(total_amount)：假设同一城市+KM区间内的费率一致，若数据存在差异可改为AVG(total_amount)
• ORDER BY：让结果按城市和KM区间排序，PDF展示更规整

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