零售店铺桌面应用：实现Daily_Sale表当前月份销售额查询与统计

Hey there! Let's get your retail desktop app showing the right sales data automatically—no more pulling all records every time. Here's how to tweak your SQL and C# code to meet your needs:

Replace your existing SqlCommand with a query that filters for the current year and month. This will automatically switch to the next month when the calendar rolls over, no manual edits required.

For SQL Server: #

cmd = new SqlCommand(@"
    SELECT SaleDate, Amount  -- Only fetch columns you need (cuts down on unnecessary data)
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE())
      AND MONTH(SaleDate) = MONTH(GETDATE())
    ORDER BY SaleDate ASC;  -- Sort by date for a clean daily view", con);
adapt = new SqlDataAdapter(cmd);
// Continue filling your DataSet/DataTable as you normally would

For MySQL (if you're using it instead): #

cmd = new SqlCommand(@"
    SELECT SaleDate, Amount
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(CURDATE())
      AND MONTH(SaleDate) = MONTH(CURDATE())
    ORDER BY SaleDate ASC;", con);

Why this works: GETDATE() (SQL Server) or CURDATE() (MySQL) grabs the current system date. We match the year and month of your SaleDate field to this value, so the query always targets the active month without any extra work from you.

You can fetch these totals with separate simple queries, or combine them with your daily data to minimize database trips.

Option 1: Separate Queries (Straightforward) #

Monthly Total:

var monthlyTotalCmd = new SqlCommand(@"
    SELECT SUM(Amount) AS MonthlyTotal
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE())
      AND MONTH(SaleDate) = MONTH(GETDATE());", con);
decimal monthlyTotal = (decimal)monthlyTotalCmd.ExecuteScalar();

Annual Total:

var annualTotalCmd = new SqlCommand(@"
    SELECT SUM(Amount) AS AnnualTotal
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE());", con);
decimal annualTotal = (decimal)annualTotalCmd.ExecuteScalar();

Option 2: Combine Daily Data + Totals in One Query #

If you want to get everything in a single database call:

var combinedCmd = new SqlCommand(@"
    -- Daily sales records
    SELECT 'Daily' AS RecordType, SaleDate AS Date, Amount AS Value
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE())
      AND MONTH(SaleDate) = MONTH(GETDATE())
    UNION ALL
    -- Monthly total
    SELECT 'Monthly Total' AS RecordType, NULL AS Date, SUM(Amount) AS Value
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE())
      AND MONTH(SaleDate) = MONTH(GETDATE())
    UNION ALL
    -- Annual total
    SELECT 'Annual Total' AS RecordType, NULL AS Date, SUM(Amount) AS Value
    FROM Daily_Sale
    WHERE YEAR(SaleDate) = YEAR(GETDATE())
    -- Sort to keep daily records first, then totals
    ORDER BY 
        CASE RecordType WHEN 'Daily' THEN 1 ELSE 2 END,
        SaleDate;", con);

• Ditch SELECT *: Always specify the columns you need (like SaleDate and Amount) to reduce data transfer and speed up queries.
• Add an Index to SaleDate: If your Daily_Sale table has lots of records, this will make date filtering way faster:

  -- SQL Server
  CREATE INDEX IX_Daily_Sale_SaleDate ON Daily_Sale(SaleDate);
  
  -- MySQL
  CREATE INDEX IX_Daily_Sale_SaleDate ON Daily_Sale(SaleDate);
  

• Use Date Ranges for Better Performance: For large datasets, replace the YEAR()/MONTH() checks with a date range—this lets the database use the index more efficiently:

  -- SQL Server example
  WHERE SaleDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND SaleDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  

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