一个数据汇总的问题
交易单号 日期 网点 现收 现付 本地现金
A321564 2007-07-01 A 500 20 480
A321645 2007-07-01 A 500 20 480
A321646 2007-07-01 A 500 20 480
...
A321446 2007-07-02 A 400 38 362
A321447 2007-07-02 A 400 38 362
A321448 2007-07-02 A 400 38 362
...
A321224 2007-07-03 A 1500 220 1280
A321225 2007-07-03 A 1500 220 1280
A321226 2007-07-03 A 1500 220 1280
....
B621764 2007-07-01 B 500 20 480
B621765 2007-07-01 B 500 20 480
B621766 2007-07-01 B 500 20 480
...
B621794 2007-07-02 B 400 38 362
B621795 2007-07-02 B 400 38 362
B621796 2007-07-02 B 400 38 362
...
B621694 2007-07-03 B 1500 220 1280
B621695 2007-07-03 B 1500 220 1280
B621696 2007-07-03 B 1500 220 1280
....
期望得到结果
先按日期汇总再按网点汇总
日期 网点 现收 现付 本地现金
2007-07-01 A 52100 2200 49900
2007-07-01 B 43200 1500 41700
2007-07-01 C 15000 22000 -7000
小计 109400 25700 83700
2007-07-02 A 30000 12200 49900
2007-07-02 B 20000 15000 41700
2007-07-02 C 15000 22000 -7000
小计 109400 25700 83700
......
合计 560000 20000 540000
[解决办法]
SELECT CASE WHEN GROUPING(收货日期) = 1 THEN '合计 ' ELSE 收货日期 END 收货日期,
SUM(现付中转费) AS 现付中转费, SUM(未付中转费) AS 未付中转费, SUM(已收)
AS 已收, SUM(提付) AS 提付, SUM(月结) AS 月结, SUM(回单结) AS 回单结
FROM data_ydxx_s
WHERE 收货日期 BETWEEN '2007-06-01 ' AND '2007-06-30 '
GROUP BY 收货日期 WITH ROLLUP
[解决办法]
楼主实际查询中的“网点”怎么没了？
根据楼主的问题，做一下几种猜测
1：
SELECT CASE WHEN GROUPING(收货日期) = 1 THEN '合计 ' END 收货日期, SUM(现付中转费) AS 现付中转费, SUM(未付中转费) AS 未付中转费,
SUM(已收) AS 已收, SUM(提付) AS 提付, SUM(月结) AS 月结, SUM(回单结)
AS 回单结
FROM data_ydxx_s
WHERE (收货日期 BETWEEN '2007-06-01 ' AND '2007-06-30 ')
GROUP BY 收货日期,网点 WITH ROLLUP
2：
SELECT CASE WHEN GROUPING(收货日期) = 1 THEN '合计 ' ELSE 收货日期 END 收货日期, SUM(现付中转费) AS 现付中转费, SUM(未付中转费) AS 未付中转费,
SUM(已收) AS 已收, SUM(提付) AS 提付, SUM(月结) AS 月结, SUM(回单结)
AS 回单结
FROM data_ydxx_s
WHERE 收货日期 BETWEEN '2007-06-01 ' AND '2007-06-30 '
GROUP BY 收货日期 WITH ROLLUP
[解决办法]
SELECT CASE WHEN GROUPING(收货日期) = 1 THEN '合计 ' ELSE 收货日期 END 收货日期,
SUM(现付中转费) AS 现付中转费, SUM(未付中转费) AS 未付中转费, SUM(已收)
AS 已收, SUM(提付) AS 提付, SUM(月结) AS 月结, SUM(回单结) AS 回单结
FROM data_ydxx_s
WHERE 收货日期 BETWEEN '2007-06-01 ' AND '2007-06-30 '
GROUP BY 收货日期 WITH ROLLUP
在查询分析器里的结果是：
服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。
--------------
做下轉換
SELECT CASE WHEN GROUPING(收货日期) = 1 THEN '合计 ' ELSE Convert(Varchar(10), 收货日期, 120) END 收货日期,
SUM(现付中转费) AS 现付中转费, SUM(未付中转费) AS 未付中转费, SUM(已收)
AS 已收, SUM(提付) AS 提付, SUM(月结) AS 月结, SUM(回单结) AS 回单结
FROM data_ydxx_s
WHERE 收货日期 BETWEEN '2007-06-01 ' AND '2007-06-30 '
GROUP BY 收货日期 WITH ROLLUP
