SQL Server 2008单查询实现分组求和并关联日期需求咨询

没问题，我帮你把这两个统计查询合并成一个，还能带上你需要的Table2.Date字段。核心思路是用条件聚合函数（SUM+CASE）来分别统计现金和信用卡的金额与票据数量，下面分两种常见场景给出方案：

场景1：按日期统计每日的支付数据 #

如果你需要按日期拆分，查看每一天的现金、信用卡支付情况，用这个查询：

SELECT 
    Table1.Id AS Z_Number,
    Table2.Date, -- 包含你要求的日期字段
    SUM(CASE WHEN Table2.Name = 'Cash' THEN Table2.Amount ELSE 0 END) AS Cash_Payments,
    COUNT(CASE WHEN Table2.Name = 'Cash' THEN Table2.TicketId ELSE NULL END) AS Cash_Ticket_Count,
    SUM(CASE WHEN Table2.Name = 'Credit Card' THEN Table2.Amount ELSE 0 END) AS Credit_Card_Payments,
    COUNT(CASE WHEN Table2.Name = 'Credit Card' THEN Table2.TicketId ELSE NULL END) AS Credit_Card_Ticket_Count
FROM Table2
INNER JOIN Table1 
    ON Table2.Date BETWEEN Table1.StartDate AND Table1.EndDate
WHERE Table1.Id = 1
  AND Table2.Name IN ('Cash', 'Credit Card') -- 只筛选目标支付方式，提升查询效率
GROUP BY Table1.Id, Table2.Date -- 按Z编号和日期分组，确保每日数据单独一行
ORDER BY Table2.Date; -- 按日期排序，方便后续展示

场景2：汇总所有日期的总支付数据 #

如果你只需要和原来两个查询一样的总合计结果（合并成一行展示），可以去掉日期分组，用这个查询：

SELECT 
    Table1.Id AS Z_Number,
    SUM(CASE WHEN Table2.Name = 'Cash' THEN Table2.Amount ELSE 0 END) AS Cash_Payments,
    COUNT(CASE WHEN Table2.Name = 'Cash' THEN Table2.TicketId ELSE NULL END) AS No_of_Cash_Tickets,
    SUM(CASE WHEN Table2.Name = 'Credit Card' THEN Table2.Amount ELSE 0 END) AS Credit_Card_Payments,
    COUNT(CASE WHEN Table2.Name = 'Credit Card' THEN Table2.TicketId ELSE NULL END) AS No_of_Credit_Card_Tickets
FROM Table2
INNER JOIN Table1 
    ON Table2.Date BETWEEN Table1.StartDate AND Table1.EndDate
WHERE Table1.Id = 1
  AND Table2.Name IN ('Cash', 'Credit Card')
GROUP BY Table1.Id;

额外说明 #

• 我把原来的隐式表连接（逗号分隔）改成了INNER JOIN的显式写法，这是SQL的规范写法，可读性和维护性更强
• 两种方案都能直接在VB.NET窗体中使用：你可以把查询结果填充到DataTable，再绑定到DataGridView展示；导出Excel的话，用.NET自带的Excel互操作或者EPPlus这类第三方库都能轻松实现

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