34,593
社区成员
发帖
与我相关
我的任务
分享求SQL语句 多表联合按日期(天)sum求和汇总
先贴下表中数据
目前的SQL 写法
得到的结果
实际上我想求的是 2015-03-07 到 2016-10-04 这其中每一天的汇总， 尝试用between 结果却总是多 本来只有2天的数据 却有个8天的数据，，，
access的数据库，，
目前的SQL 写法
SELECT a.sDay AS a日期, b.fare AS b金额, b.cardfare AS b会员金额, (b.moneyFare) AS b现金金额, (b.otherFare) AS b其他金额, (b.cent) AS b产生积分, (c.Fare) AS c充值金额, (c.sFare) AS c赠送金额, (c.sumfare) AS c实际充值, (d.sumcent) AS d兑换积分
FROM SumDay AS a, [select sum(e.yfare)as fare,sum(e.cardfare) as cardfare,sum(e.moneyFare)as moneyFare,sum(e.otherFare)as otherFare,sum(e.cent)as cent from consume as e
where (Format(e.SellDate,"yyyy-mm-dd"))= #2016-10-04#
group by (Format(e.SellDate,"yyyy-mm-dd")) ]. AS b, [select sum(f.Fare) as fare,sum(f.sFare) as sFare,sum(f.fare+f.sfare) as sumfare from Cashrec as f
where (Format(f.cashrecDate,"yyyy-mm-dd"))=#2016-10-04#
group by (Format(f.cashrecDate,"yyyy-mm-dd")) ]. AS c, [select sum(g.reccent)as sumcent from cent as g
where (Format(g.CentDate,"yyyy-mm-dd"))= #2016-10-04#
group by (Format(g.centDate,"yyyy-mm-dd")) ]. AS d
WHERE (((a.sDay)=#10/4/2016#))
ORDER BY a.sDay DESC;得到的结果
实际上我想求的是 2015-03-07 到 2016-10-04 这其中每一天的汇总， 尝试用between 结果却总是多 本来只有2天的数据 却有个8天的数据，，，
access的数据库，，
...全文
请发表友善的回复…
发表回复
三樱椒 2016-10-23
- 打赏
- 举报
谢谢各位回复, 试验了下，在SQL SERVER中没问题， 在Access中不行，它没有 union all ---
baidu_35289351 2016-10-08
- 打赏
- 举报
斑竹的更加简单，但有个地方有问题，A1没数据，改为这样才是。
SELECT a2 ,
SUM(a1) AS a1 ,
SUM(b1) AS b1
FROM ( SELECT a1 , a2 , 0 AS b1 FROM #TableA
UNION ALL
SELECT 0 AS a1 , b2 , b1 FROM #TableB
) AS t
GROUP BY a2;
baidu_35289351 2016-10-08
- 打赏
- 举报
分2步走，第1步，先等到所有的日期，第2步左连接表A和表B,如下
create table #TableA(a1 int,a2 datetime)
insert into #TableA
select 8,'2016-10-01' union all
select 8,'2016-10-01' union all
select 25,'2016-10-03' union all
select 12,'2016-10-04' union all
select 12,'2016-10-04'
create table #TableB(b1 int,b2 datetime)
insert into #TableB
select 123,'2016-10-01' union all
select 123,'2016-10-01' union all
select 223,'2016-10-02' union all
select 24,'2016-10-04' union all
select 24,'2016-10-04'
select distinct t3.dt,(case when t1.a1 is null then 0 else t1.a1 end) as a1,(case when t2.b1 is null then 0 else t2.b1 end) as b1
from(select a2 dt,0 as a1,0 as b1 from #TableA group by a2
union all
select b2 dt,0 as b1,0 as a1 from #Tableb group by b2)t3
left join (select a2 dt,sum(a1) a1,0 as b1 from #TableA group by a2)t1
on t1.dt=t3.dt
left join (select b2 dt,sum(b1) b1,0 as a1 from #Tableb group by b2)t2
on t2.dt=t3.dt
中国风 2016-10-07
- 打赏
- 举报
SELECT a2 ,
SUM(a1) AS a1 ,
SUM(b1) AS b1
FROM ( SELECT 0 AS a1 ,
a2 ,
0 AS b1
FROM TableA
UNION ALL
SELECT 0 AS a1 ,
b2 ,
b1
FROM TableB
) AS t
GROUP BY a2;
mingqing6364 2016-10-06
- 打赏
- 举报
TO：#4
按下面修改
SELECT ISNULL(T1.a2, ISNULL(T2.b2,T3.c2)) AS [DATE], T1.SUM_T1,T2.SUM_T2,T3.SUM_T3
FROM
(SELECT SUM(a1) AS SUM_T1,a2 FROM #TABLEA where a2 between '2016-10-01' and '2016-10-04' GROUP BY a2) T1
FULL JOIN
(SELECT SUM(b1) AS SUM_T2,b2 FROM #TABLEB where b2 between '2016-10-01' and '2016-10-04' GROUP BY b2) T2
ON T1.a2 = T2.b2
FULL JOIN
(SELECT SUM(c1) AS SUM_T3,c2 FROM #TABLEc where c2 between '2016-10-01' and '2016-10-04' GROUP BY c2) T3
ON T1.a2 =T3.c2 mingqing6364 2016-10-05
- 打赏
- 举报
我翻了下我的虚拟机，没有找到装了Access，所以就用SQL SERVER来作答吧，可以参考一下
拿#1的列子来说：结果要求把多个表中数据按日期求和汇总
那就应该有两个步骤，一是聚集函数聚集、二是连接查询结果
这两个步骤顺序不能乱，按#1的列子来说，如果先按日期相等连接，那么2016-10-01会有4（笛卡儿积，2X2）条记录，那么求和肯定会出错
具体SQL如下：
CREATE TABLE #TABLEA
(
a1 INT,
a2 DATE
);
INSERT INTO #TABLEA VALUES
(8,'2016-10-01'),
(8,'2016-10-01'),
(8,'2016-10-02'),
(12,'2016-10-04'),
(12,'2016-10-04');
CREATE TABLE #TABLEB
(
b1 INT,
b2 DATE
);
INSERT INTO #TABLEB VALUES
(123,'2016-10-01'),
(123,'2016-10-01'),
(123,'2016-10-03'),
(24,'2016-10-04'),
(24,'2016-10-04')
SELECT
ISNULL(T1.a2, T2.b2) AS [DATE],
T1.SUM_T1,
T2.SUM_T2
FROM
(
SELECT SUM(a1) AS SUM_T1,a2 FROM #TABLEA GROUP BY a2
) T1
FULL JOIN
(
SELECT SUM(b1) AS SUM_T2,b2 FROM #TABLEB GROUP BY b2
) T2
ON T1.a2 = T2.b2
DROP TABLE #TABLEA
DROP TABLE #TABLEB三樱椒 2016-10-05
- 打赏
- 举报
回复 mingqing6364
是我SB了
ISNULL 是为了防止T1.a2 T2.b2中没有时间是， T3.c2可以单独成一条记录
三樱椒 2016-10-05
- 打赏
- 举报
是的 不过SQL server 的也行， 主要是解决步骤想不明白
三樱椒 2016-10-05
- 打赏
- 举报
谢谢@@，我想实现的就是这种结果，我看了下我的code ，不正确的原因好像在最后一句 on t1.a2=t2.b2 上， 不知道是不是
另外，你前面说的是对的， 一个日期在表A中出现，却未必在表B中出现， 你的写法就可以 谢谢
我加了T3
SELECT ISNULL(T1.a2, T2.b2) AS [DATE], T1.SUM_T1,T2.SUM_T2,T3.SUM_T3
FROM
(SELECT SUM(a1) AS SUM_T1,a2 FROM #TABLEA where a2 between '2016-10-01' and '2016-10-04' GROUP BY a2) T1
FULL JOIN
(SELECT SUM(b1) AS SUM_T2,b2 FROM #TABLEB where b2 between '2016-10-01' and '2016-10-04' GROUP BY b2) T2
ON T1.a2 = T2.b2
FULL JOIN
(SELECT SUM(c1) AS SUM_T3,c2 FROM #TABLEc where c2 between '2016-10-01' and '2016-10-04' GROUP BY c2) T3
ON T1.a2 =T3.c2
为啥即使没有 ISNULL 条件那里添加T3 可结果T3中没有的日期仍是NULL
图片 查询结果
tablec 的数据 图片
卖水果的net 版主 2016-10-05
- 打赏
- 举报
你是 Access 的库吗？
三樱椒 2016-10-04
- 打赏
- 举报
额 简单的点的数据
TableA
字段a1 字段a2
8 2016-10-01
8 2016-10-01
12 2016-10-04
12 2016-10-04
TableB
字段b1 字段b2
123 2016-10-01
123 2016-10-01
24 2016-10-04
24 2016-10-04
想的到的结果:
日期 字段a1 字段b1
2016-10-01 16 246
2016-10-04 24 48
单个测试 sum(字段a1) where 字段a2 between 2016-10-01 and 2016-10-04 可以正确汇总 但是在加上 TableB就不对了
