如何编写可复用月度交易明细SQL脚本，批量按客户导出Excel？

Hey there! Let's break down how to build a reusable script that pulls daily transaction details for specific clients each month, exports each result to an Excel file with your desired naming convention, and only requires quick variable updates when the month changes. Below are practical, tool-specific solutions:

1. Python (Cross-Database, Most Flexible) #

Python is perfect here because it works with almost any database (SQL Server, MySQL, PostgreSQL, etc.) and makes exporting to Excel straightforward with libraries like pandas and sqlalchemy.

Step-by-Step Script #

First, install the required packages if you haven't already:

pip install pandas sqlalchemy openpyxl pyodbc  # Use pymysql instead of pyodbc for MySQL

Then write your script:

import pandas as pd
from sqlalchemy import create_engine

# --------------------------
# Update these variables once per month
# --------------------------
month = 'April'
begin_date = '2018-04-01'
end_date = '2018-04-30'
# List of clients you want to query
client_list = ['xxxx', 'yyyy', 'zzzz']

# --------------------------
# Database connection (adjust for your DB type)
# --------------------------
# For SQL Server:
engine = create_engine('mssql+pyodbc://username:password@server/database?driver=ODBC+Driver+17+for+SQL+Server')
# For MySQL:
# engine = create_engine('mysql+pymysql://username:password@server/database')

# --------------------------
# Loop through each client and export
# --------------------------
for client in client_list:
    query = f"""
        SELECT date, client, price 
        FROM clientdb 
        WHERE date >= '{begin_date}' 
          AND date <= '{end_date}' 
          AND client = '{client}'
    """
    # Fetch data into a DataFrame
    df = pd.read_sql(query, engine)
    # Export to Excel with your desired filename
    output_filename = f"{month}_{client}.xlsx"
    df.to_excel(output_filename, index=False)
    print(f"Exported {output_filename} successfully!")

Why this works: #

• You only need to update the month, begin_date, end_date, and client_list variables once per month.
• Each client's data is saved to a separate Excel file named like April_xxxx.xlsx.
• Works across multiple database systems with just a quick change to the connection string.

2. SQL Server Management Studio (T-SQL + PowerShell) #

If you prefer staying within SQL Server's ecosystem, you can combine T-SQL variables with a PowerShell script to handle exports.

Step 1: Define Variables in T-SQL #

Create a .sql file with your base query and variables:

DECLARE @month NVARCHAR(20) = 'April'
DECLARE @begin_date DATE = '2018-04-01'
DECLARE @end_date DATE = '2018-04-30'
DECLARE @client NVARCHAR(50)

-- We'll pass @client from PowerShell, but you can also define a list here
SELECT date, client, price 
FROM clientdb 
WHERE date >= @begin_date 
  AND date <= @end_date 
  AND client = @client

Step 2: PowerShell Script to Loop Clients #

Create a .ps1 file to execute the SQL script for each client and export to Excel:

# Update these variables once per month
$month = "April"
$beginDate = "2018-04-01"
$endDate = "2018-04-30"
$clientList = @("xxxx", "yyyy", "zzzz")
$server = "YourServerName"
$database = "YourDatabaseName"

# Loop through each client
foreach ($client in $clientList) {
    # Run SQL query and get results
    $query = @"
        DECLARE @month NVARCHAR(20) = '$month'
        DECLARE @begin_date DATE = '$beginDate'
        DECLARE @end_date DATE = '$endDate'
        DECLARE @client NVARCHAR(50) = '$client'
        SELECT date, client, price 
        FROM clientdb 
        WHERE date >= @begin_date 
          AND date <= @end_date 
          AND client = @client
"@
    $data = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query
    # Export to Excel (requires ImportExcel module)
    Install-Module -Name ImportExcel -Force -Scope CurrentUser  # Run once to install
    $outputPath = ".\$month`_$client.xlsx"
    $data | Export-Excel -Path $outputPath -AutoSize -TableName "Transactions"
    Write-Host "Exported $outputPath successfully!"
}

3. MySQL Workbench (Variables + CSV to Excel) #

For MySQL, you can use user-defined variables and export results to CSV first, then convert to Excel (or use Python to handle the conversion directly as in the first solution).

Example Script #

-- Update these variables once per month
SET @month = 'April';
SET @begin_date = '2018-04-01';
SET @end_date = '2018-04-30';

-- For client xxxx
SELECT date, client, price 
FROM clientdb 
WHERE date >= @begin_date 
  AND date <= @end_date 
  AND client = 'xxxx'
INTO OUTFILE '/path/to/April_xxxx.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Repeat for client yyyy
SELECT date, client, price 
FROM clientdb 
WHERE date >= @begin_date 
  AND date <= @end_date 
  AND client = 'yyyy'
INTO OUTFILE '/path/to/April_yyyy.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

You can then rename the CSV files to .xlsx (most spreadsheet apps will recognize them) or use a tool like LibreOffice Calc to batch convert them.

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