求SQL语句多表联合按日期(天)sum求和汇总

三樱椒 2016-10-04 10:32:50

先贴下表中数据

目前的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的数据库，，

...全文

3802 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

三樱椒 2016-10-23

打赏
举报

谢谢各位回复, 试验了下，在SQL SERVER中没问题，在Access中不行，它没有 union all ---

baidu_35289351 2016-10-08

打赏
举报

引用 8 楼 roy_88 的回复:


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;

斑竹的更加简单，但有个地方有问题，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

ISNULL(T1.a2, ISNULL(T2.b2,T3.c2))按编程语言的理解就是 if T1.a2 IS NOT NULL THEN T1.a2 else if T2.b2 IS NOT NULL THEN T2.b2 else 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

我在#1的数据基础上分别给表A和B加入了一组独有的日期数据同时使用FULL JOIN来连接两个子查询，查询结果的日期列也考虑到其中一项为空的情况如果有更多的表连接，日期列可以继续扩展ISNULL(T1.DATE,ISNULL(T2.DATE,ISNULL(T3.DATE,……))) 但是根据具体的业务需求，如果一个日期在表A中出现，必定会在所有的表中出现，那么就建议使用INNER JOIN连接处理日期列也可以直接写成T1.DATE

三樱椒 2016-10-05

打赏
举报

回复 mingqing6364 是我SB了 ISNULL 是为了防止T1.a2 T2.b2中没有时间是， T3.c2可以单独成一条记录

三樱椒 2016-10-05

打赏
举报

引用 2 楼 wmxcn2000 的回复:

你是 Access 的库吗？

是的不过SQL server 的也行，主要是解决步骤想不明白

三樱椒 2016-10-05

打赏
举报

引用 3 楼 mingqing6364 的回复:

我翻了下我的虚拟机，没有找到装了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
我在#1的数据基础上分别给表A和B加入了一组独有的日期数据
同时使用FULL JOIN来连接两个子查询，查询结果的日期列也考虑到其中一项为空的情况
如果有更多的表连接，日期列可以继续扩展ISNULL(T1.DATE,ISNULL(T2.DATE,ISNULL(T3.DATE,……)))

但是根据具体的业务需求，如果一个日期在表A中出现，必定会在所有的表中出现，那么就建议使用INNER JOIN连接处理
日期列也可以直接写成T1.DATE

谢谢@@，我想实现的就是这种结果，我看了下我的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就不对了

分组后分组合计以及总计SQL语句（稍微整理了一下）MYSQL每隔10分钟进行分组统计的实现方法mysql使用GROUP BY分组实现取前N条记录的方法详解MySQL中的分组查询与连接查询语句sql server如何利用开窗函数over()进行...

8.2.1 求和函数—sum() 140 8.2.2 计数函数—count() 140 8.2.3 最大/最小值函数—max()/min() 143 8.2.4 均值函数—avg() 145 8.2.5 聚合分析的重值处理 147 8.2.6 聚合函数的组合使用 148 8.3 组合查询 148 ...

这个例子是对课程进度表里面的某个学生的剩余课时进行求和汇总。 laravel 版本是 lts 5.5 StudentLessonProgress::where('student_info_id', $student_info_id) ->sum('total_left_class_num'); 打印sql语句如下：...