查询数据(汇总分组) [英] Querying Data (Summarised Grouping)

问题描述

我在查询数据时遇到问题.
以下显示一些数据.

I have a problem to query data.
There are some data shown in following.

Date		Loc	Amount
----------------------------
2010-01-02	place1	100
2010-01-14	place1	110
2010-02-11	place2	80
2010-02-11	place3	200
2010-04-20	place1	330
2010-04-22	place2	120
2010-04-26	place3	220
2010-05-01	place1	150
2010-05-03	place2	180
2010-05-06	place2	50
2010-05-06	place3	100
2010-05-11	place2	200

然后，结果必须介于2010-02-01和2010-02-28之间，如下所示.

Then, the result must be between 2010-02-01 and 2010-02-28 and shown in following.

Loc	Amount
------------
place1	0
place2	80
place3	200

对不起，我的英语不好.

Sorry for my un-perfect english.

推荐答案

解决方案:

从表名中选择Loc，求和(金额)，按Loc分组的日期在"2010-02-01"和"2010-02-28"之间.

Solution:

select Loc, sum(amount) from tablename where date between ''2010-02-01'' and ''2010-02-28'' group by Loc

感谢大家.我找到了解决方法.

Thanks everyone. I found the solution.

CREATE TABLE #t1
(D1 DATETIME
, LOC NVARCHAR(10)
, AMOUNT INT)
GO
INSERT INTO #t1 VALUES('2010-01-02','place1',100)
INSERT INTO #t1 VALUES('2010-01-02','place1',100)
INSERT INTO #t1 VALUES('2010-01-14','place1',110)
INSERT INTO #t1 VALUES('2010-02-11','place2',80)
INSERT INTO #t1 VALUES('2010-02-11','place3',200)
INSERT INTO #t1 VALUES('2010-04-20','place1',330)
INSERT INTO #t1 VALUES('2010-04-22','place2',120)
INSERT INTO #t1 VALUES('2010-04-26','place3',220)
INSERT INTO #t1 VALUES('2010-05-01','place1',150)
INSERT INTO #t1 VALUES('2010-05-03','place2',180)
INSERT INTO #t1 VALUES('2010-05-06','place2',50)
INSERT INTO #t1 VALUES('2010-05-06','place3',100)
INSERT INTO #t1 VALUES('2010-05-11','place2',200)
GO
SELECT DISTINCT a.loc,ISNULL(b.amount,0) AS amount FROM #t1 as a
LEFT OUTER JOIN
(SELECT loc,
sum(amount) as amount
FROM #t1 as b
WHERE d1 >= '2010-02-01' AND d1 <= '2010-02-28'
GROUP BY loc) AS b
ON a.loc = b.loc

这篇关于查询数据(汇总分组)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！