Question

sql select group by 语句

XJrain表里存放每天的雨量,我想一次显示过去一天,过去二天,过去三天的雨量统计,sql语句该如何写啊?

SELECT SUM(雨量) AS 雨量, 站名
FROM dbo.XJrain
WHERE (日期 > 20090829)
GROUP BY 站名

SELECT SUM(雨量) AS 雨量, 站名
FROM dbo.XJrain
WHERE (日期 > 20090830)
GROUP BY 站名

SELECT SUM(雨量) AS 雨量, 站名
FROM dbo.XJrain
WHERE (日期 > 20090831)
GROUP BY 站名
可能我讲得不够请楚,我想实现 的是:
select 一天雨量1,二天雨量2,三天雨量,站名 .....这样子,
各位只是一次显示了一个时间段的雨量,我想要的是一次显示三个时间段的雨量

Answer 1

select sum(雨量1),sum(雨量2)
,sum(雨量3), a.站名

from (
SELECT SUM(雨量) AS 雨量1, 站名
FROM dbo.XJrain
WHERE (日期 > 20090829)
GROUP BY 站名 ) a
left join

(SELECT SUM(雨量) AS 雨量2, 站名
FROM dbo.XJrain
WHERE (日期 > 20090830)
GROUP BY 站名) b on (a.站名=b.站名)
left join
(SELECT SUM(雨量) AS 雨量3, 站名
FROM dbo.XJrain
WHERE (日期 > 20090831)
GROUP BY 站名 )c on (b.站名=a.站名)
group by 站名

或者这样
select 站名,
sum(case when 日期 > 20090829 then 雨量 else 0 end ) 雨量1,
sum(case when 日期 > 20090830 then 雨量 else 0 end ) 雨量2,
sum(case when 日期 > 20090831 then 雨量 else 0 end ) 雨量3,
from dbo.XJrain
group by 站名
没测试 ，你试试吧。

Answer 2

select 站名,
sum(case when 日期 between '20090829' and '20090830' then 雨量1 else 0 end ) 雨量1,
sum(case when 日期 between '20090830' and '20090831' then 雨量2 else 0 end ) 雨量2,
sum(case when 日期 between '20090831' and '20090901' then 雨量3 else 0 end ) 雨量3,
from dbo.XJrain
group by 站名

Answer 3

--示例表
declare @t table (zhanming varchar(10),yuliang int,riqi datetime)
insert into @t
select 'a',1,'2009-08-29' union all
select 'b',2,'2009-08-29' union all
select 'a',5,'2009-08-30' union all
select 'b',6,'2009-08-30' union all
select 'a',3,'2009-08-31' union all
select 'b',4,'2009-08-31'

--查询语句
select zhanming,
sum(case when riqi>='2009-08-31' then yuliang else 0 end) [yuliang1],
sum(case when riqi>='2009-08-30' then yuliang else 0 end) [yuliang2],
sum(case when riqi>='2009-08-29' then yuliang else 0 end) [yuliang3]
from @t
group by zhanming

--结果
zhanming yuliang1 yuliang2 yuliang3
---------- ----------- ----------- -----------
a 3 8 9
b 4 10 12

（所影响的行数为 2 行）

Answer 4

select SUM(雨量1) 雨量1, SUM(雨量2) 雨量2,SUM(雨量3) 雨量3, 站名
(
SELECT SUM(雨量) AS 雨量1,0 雨量2,0 雨量3, 站名
FROM dbo.XJrain
WHERE datediff(dd,日期,getdate())>=1
GROUP BY 站名

union all
SELECT 0,SUM(雨量) AS 雨量,0, 站名
FROM dbo.XJrain
WHERE datediff(dd,日期,getdate())>=2
GROUP BY 站名
union all

SELECT SUM(雨量) AS 雨量,0,0, 站名
FROM dbo.XJrain
WHERE datediff(dd,日期,getdate())>=3
GROUP BY 站名
) group by 站名

Answer 5

SELECT SUM(雨量) AS 雨量, 站名
FROM dbo.XJrain
WHERE DATEDIFF(day,日期,getdate())>=1 and DATEDIFF(day,日期,getdate())>)<=3
GROUP BY 站名

Answer 6

SELECT SUM(雨量) AS 雨量, 站名
FROM dbo.XJrain
WHERE datediff(day,日期,getdate())=天数
GROUP BY 站名