declare @t table(goodid varchar(10),date1 datetime,bh int,Num1 int,Num2 int)
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000002 ', '2005-3-1 ',1101,50,10
insert into @t select '000002 ', '2005-3-2 ',1101,50,10
insert into @t select '000002 ', '2005-3-10 ',1101,50,10
insert into @t select '000002 ', '2005-3-27 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-5-2 ',1101,50,10
insert into @t select '000003 ', '2005-5-25 ',1101,50,10
insert into @t select '000003 ', '2005-7-4 ',1101,50,10
insert into @t select '000003 ', '2007-8-1 ',1101,50,10
insert into @t select '000003 ', '2008-9-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000002 ', '2005-3-1 ',1101,50,10
insert into @t select '000002 ', '2005-3-2 ',1101,50,10
insert into @t select '000002 ', '2005-3-10 ',1101,50,10
insert into @t select '000002 ', '2005-3-27 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-5-2 ',1101,50,10
insert into @t select '000003 ', '2005-5-25 ',1101,50,10
insert into @t select '000003 ', '2005-7-4 ',1101,50,10
insert into @t select '000003 ', '2007-8-1 ',1101,50,10
insert into @t select '000003 ', '2008-9-2 ',1101,50,10
解决方案 »
- T-SQL语句中..是神马意思?
- c#程序连接sql server 数据库连不上
- SQL的自带的函数是怎么写的?估计不是用SQL语句.
- 这个语句应该怎么写啊,不麻烦邹老大,兄弟们帮下忙,比较简单,急
- 字符串截取
- 江湖救急,查询分钟位不为零的数据
- 为难的查询语句
- 请问:如何对SQL SERVER进行异地备份。也就是自动在局域网的其他机器中备份数据库。谢谢。
- 请问各位大虾SQL Server、Oracle、SyBase、MySQL、IBM DB2、Informix这些数据库的优缺点?一定结帖!
- 低能问题:怎样在临时数据库里面建立临时表?一定给分
- 求一个sql
- 弱弱的问:是不是一定要文件写完才能执行
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000002 ', '2005-3-1 ',1101,50,10
insert into @t select '000002 ', '2005-3-2 ',1101,50,10
insert into @t select '000002 ', '2005-3-10 ',1101,50,10
insert into @t select '000002 ', '2005-3-27 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-5-2 ',1101,50,10
insert into @t select '000003 ', '2005-5-25 ',1101,50,10
insert into @t select '000003 ', '2005-7-4 ',1101,50,10
insert into @t select '000003 ', '2007-8-1 ',1101,50,10
insert into @t select '000003 ', '2008-9-2 ',1101,50,10 --按年
select year(date1) , count(*) , sum(num1) , sum(num2) from @t group by year(date1)
--按年+季度
select year(date1) , datepart(quarter,date1), count(*) , sum(num1) , sum(num2) from @t group by year(date1),datepart(quarter,date1)
--按年+月份
select year(date1) , datepart(month,date1), count(*) , sum(num1) , sum(num2) from @t group by year(date1),datepart(month,date1)
--按日
select convert(varchar(10),date1,120), count(*) , sum(num1) , sum(num2) from @t group by convert(varchar(10),date1,120)
返回表示指定日期的指定日期部分的整数。
from @t
group by goodid,datepart(qq,date1),datepart(m,date1) with rollup
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000002 ', '2005-3-1 ',1101,50,10
insert into @t select '000002 ', '2005-3-2 ',1101,50,10
insert into @t select '000002 ', '2005-3-10 ',1101,50,10
insert into @t select '000002 ', '2005-3-27 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-5-2 ',1101,50,10
insert into @t select '000003 ', '2005-5-25 ',1101,50,10
insert into @t select '000003 ', '2005-7-4 ',1101,50,10
insert into @t select '000003 ', '2007-8-1 ',1101,50,10
insert into @t select '000003 ', '2008-9-2 ',1101,50,10 select isnull(goodid,'total'),isnull(ltrim(datepart(qq,date1)),'季total'),isnull(ltrim(datepart(m,date1)),'月total'),sum(Num1),sum(Num2)
from @t
group by goodid,datepart(qq,date1),datepart(m,date1) with rollup
---------- ------------ ------------ ----------- -----------
000001 1 1 200 40
000001 1 2 100 20
000001 1 月total 300 60
000001 月total 300 60
000002 1 3 200 40
000002 1 月total 200 40
000002 2 4 100 20
000002 2 月total 100 20
000002 月total 300 60
000003 2 4 50 10
000003 2 5 100 20
000003 2 月total 150 30
000003 3 7 50 10
000003 3 8 50 10
000003 3 9 50 10
000003 3 月total 150 30
000003 月total 300 60
total 月total 900 180(18 行受影响)
declare @t table(goodid varchar(10),date1 datetime,bh int,Num1 int,Num2 int)
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000001 ', '2005-2-3 ',1101,50,10
insert into @t select '000002 ', '2005-3-1 ',1101,50,10
insert into @t select '000002 ', '2005-3-2 ',1101,50,10
insert into @t select '000002 ', '2005-3-10 ',1101,50,10
insert into @t select '000002 ', '2005-3-27 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000002 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-4-1 ',1101,50,10
insert into @t select '000003 ', '2005-5-2 ',1101,50,10
insert into @t select '000003 ', '2005-5-25 ',1101,50,10
insert into @t select '000003 ', '2005-7-4 ',1101,50,10
insert into @t select '000003 ', '2007-8-1 ',1101,50,10
insert into @t select '000003 ', '2008-9-2 ',1101,50,10
select goodid,datepart(yy,date1)年,datepart(mm,date1)月,bh,num1,num2
from @t group by goodid,datepart(mm,date1),bh,num1,num2,datepart(yy,date1)
----------
000001 2005 1 1101 50 10
000001 2005 2 1101 50 10
000002 2005 3 1101 50 10
000002 2005 4 1101 50 10
000003 2005 4 1101 50 10
000003 2005 5 1101 50 10
000003 2005 7 1101 50 10
000003 2007 8 1101 50 10
000003 2008 9 1101 50 10