--统计表如下：
Create table tb(id int,productNum nvarchar(5),orderId nvarchar(2),quantity int,favoure numeric(12,2),stime smalldatetime)
Go
Insert into tb
select 1,'02200','1Z', 1,0.1, '2009-01-01' union all
select 2,'02205','1', 2,1, '2009-01-01' union all
select 3,'02210','1', 3,0.2, '2009-01-01' union all
select 4,'02220','1', 4,0.3, '2009-02-01' union all
select 5,'02225','1Z', 5,0.4, '2009-02-01' union all
select 6,'02230','1', 6,1, '2009-02-01' union all
select 7,'02240','1', 7,1, '2009-02-01' union all
select 8,'02245','1', 8,0.5, '2009-03-01' union all
select 9,'02250','1', 9,0.6, '2009-04-01' union all
select 10,'02256','1Z', 10,0.7, '2009-04-01' union all
select 10,'02256','1a', 11,0, '2009-05-01'
Godeclare @sql varchar(8000)
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb) as a
set @sql = @sql + ' from tb group by productNum'
exec(@sql) drop table tb--1：以上每一个每都有一个合计,现在需要在统计表的最后一项多一个总合计 (各个月份的合计相加的和)
--2：只统计 2009-1-1 到 2009-3-31 的销售记录。
Create table tb(id int,productNum nvarchar(5),orderId nvarchar(2),quantity int,favoure numeric(12,2),stime smalldatetime)
Go
Insert into tb
select 1,'02200','1Z', 1,0.1, '2009-01-01' union all
select 2,'02205','1', 2,1, '2009-01-01' union all
select 3,'02210','1', 3,0.2, '2009-01-01' union all
select 4,'02220','1', 4,0.3, '2009-02-01' union all
select 5,'02225','1Z', 5,0.4, '2009-02-01' union all
select 6,'02230','1', 6,1, '2009-02-01' union all
select 7,'02240','1', 7,1, '2009-02-01' union all
select 8,'02245','1', 8,0.5, '2009-03-01' union all
select 9,'02250','1', 9,0.6, '2009-04-01' union all
select 10,'02256','1Z', 10,0.7, '2009-04-01' union all
select 10,'02256','1a', 11,0, '2009-05-01'
Godeclare @sql varchar(8000)
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb) as a
set @sql = @sql + ' from tb group by productNum'
exec(@sql) drop table tb--1：以上每一个每都有一个合计,现在需要在统计表的最后一项多一个总合计 (各个月份的合计相加的和)
--2：只统计 2009-1-1 到 2009-3-31 的销售记录。
解决方案 »
- SQLsever2008精简版基本功能怎么样？
- 存储过程的问题
- 初次接触sql,指点一下,我打算开发一个双机版程序,就是两个电脑使用同一个数据库....
- 请教一条语句
- 建立链接服务器的错误
- 怎样用T-SQL的内置函数将"四月"转化为"April"？
- sql语句???
- 各位高手多多指教，ok的话，分数吾是问题!!!
- 我原来的win2000server崩掉了，现在是win2000pro,请问怎样装sql server2000才能恢复原来的数据呢？
- 关于数据库恢复问题
- 关于字段的选择问题?
- |zyciis|第四贴：如何锁死一个表中的一条记录，不被其他事务更新和读取呢 上贴大家给出锁页和加字段表示：那么SQL2005有没有好的解决方法
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb) as a
set @sql = @sql + ',sum(quantity) as 总合计 from tb group by productNum'
exec(@sql) 这样？
Go
Insert into tb
select 1,'02200','1Z', 1,0.1, '2009-01-01' union all
select 2,'02205','1', 2,1, '2009-01-01' union all
select 3,'02210','1', 3,0.2, '2009-01-01' union all
select 4,'02220','1', 4,0.3, '2009-02-01' union all
select 5,'02225','1Z', 5,0.4, '2009-02-01' union all
select 6,'02230','1', 6,1, '2009-02-01' union all
select 7,'02240','1', 7,1, '2009-02-01' union all
select 8,'02245','1', 8,0.5, '2009-03-01' union all
select 9,'02250','1', 9,0.6, '2009-04-01' union all
select 10,'02256','1Z', 10,0.7, '2009-04-01' union all
select 10,'02256','1a', 11,0, '2009-05-01'
Go
declare @a as varchar(10)
declare @b as varchar(10)
set @a = '2009-01-01'
set @b = '2009-03-31'
declare @sql varchar(8000)
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb where stime between @a and @b) as a
set @sql = @sql + ' from tb where stime between ''' + @a + ''' and ''' + @b + ''' group by productNum'
exec(@sql) drop table tb /*
productNum 01月收费 01月返点 01月免费 01月合计 02月收费 02月返点 02月免费 02月合计 03月收费 03月返点 03月免费 03月合计
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
02200 0 0 0 0 0 0 0 0 0 0 0 0
02205 0 2 2 4 0 0 0 0 0 0 0 0
02210 3 3 0 6 0 0 0 0 0 0 0 0
02220 0 0 0 0 4 4 0 8 0 0 0 0
02225 0 0 0 0 0 0 0 0 0 0 0 0
02230 0 0 0 0 0 6 6 12 0 0 0 0
02240 0 0 0 0 0 7 7 14 0 0 0 0
02245 0 0 0 0 0 0 0 0 8 8 0 16*/
Go
Insert into tb
select 1,'02200','1Z', 1,0.1, '2009-01-01' union all
select 2,'02205','1', 2,1, '2009-01-01' union all
select 3,'02210','1', 3,0.2, '2009-01-01' union all
select 4,'02220','1', 4,0.3, '2009-02-01' union all
select 5,'02225','1Z', 5,0.4, '2009-02-01' union all
select 6,'02230','1', 6,1, '2009-02-01' union all
select 7,'02240','1', 7,1, '2009-02-01' union all
select 8,'02245','1', 8,0.5, '2009-03-01' union all
select 9,'02250','1', 9,0.6, '2009-04-01' union all
select 10,'02256','1Z', 10,0.7, '2009-04-01' union all
select 10,'02256','1a', 11,0, '2009-05-01'
Go
declare @a as varchar(10)
declare @b as varchar(10)
set @a = '2009-01-01'
set @b = '2009-03-31'
declare @sql varchar(8000)
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb where stime between @a and @b) as a
set @sql = @sql + ' , sum(case when right(orderid,1) <> ''Z'' and favoure < 1 then quantity else 0 end) '
+ ' + sum(case when right(orderid,1) <> ''Z'' then quantity else 0 end) '
+ ' + sum(case when favoure = 1 then quantity else 0 end) [总合计]
from tb where stime between ''' + @a + ''' and ''' + @b + ''' group by productNum'
exec(@sql) drop table tb /*
productNum 01月收费 01月返点 01月免费 01月合计 02月收费 02月返点 02月免费 02月合计 03月收费 03月返点 03月免费 03月合计 总合计
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
02200 0 0 0 0 0 0 0 0 0 0 0 0 0
02205 0 2 2 4 0 0 0 0 0 0 0 0 4
02210 3 3 0 6 0 0 0 0 0 0 0 0 6
02220 0 0 0 0 4 4 0 8 0 0 0 0 8
02225 0 0 0 0 0 0 0 0 0 0 0 0 0
02230 0 0 0 0 0 6 6 12 0 0 0 0 12
02240 0 0 0 0 0 7 7 14 0 0 0 0 14
02245 0 0 0 0 0 0 0 0 8 8 0 16 16*/
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？ declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
Go
Insert into tb
select 1,'02200','1Z', 1,0.1, '2009-01-01' union all
select 2,'02205','1', 2,1, '2009-01-01' union all
select 3,'02210','1', 3,0.2, '2009-01-01' union all
select 4,'02220','1', 4,0.3, '2009-02-01' union all
select 5,'02225','1Z', 5,0.4, '2009-02-01' union all
select 6,'02230','1', 6,1, '2009-02-01' union all
select 7,'02240','1', 7,1, '2009-02-01' union all
select 8,'02245','1', 8,0.5, '2009-03-01' union all
select 9,'02250','1', 9,0.6, '2009-04-01' union all
select 10,'02256','1Z', 10,0.7, '2009-04-01' union all
select 10,'02256','1a', 11,0, '2009-05-01'
Go
declare @a as varchar(10)
declare @b as varchar(10)
set @a = '2009-01-01'
set @b = '2009-05-31'
declare @sql varchar(8000)
set @sql = 'select productNum '
select @sql = @sql + ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) <> ''Z'' and favoure <> 1 then quantity else 0 end) [' + month + '月收费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and right(orderid,1) = ''Z'' then quantity else 0 end) [' + month + '月返点]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' and favoure = 1 then quantity else 0 end) [' + month + '月免费]'
+ ' , sum(case when datename(mm , stime) = ''' + [month] + ''' then quantity else 0 end) [' + month + '月合计]'
from (select distinct datename(mm , stime) [month] from tb where stime between @a and @b) as a
set @sql = @sql + ' , sum(quantity) [总合计] from tb where stime between ''' + @a + ''' and ''' + @b + ''' group by productNum'
exec(@sql) drop table tb /*
productNum 01月收费 01月返点 01月免费 01月合计 02月收费 02月返点 02月免费 02月合计 03月收费 03月返点 03月免费 03月合计 04月收费 04月返点 04月免费 04月合计 05月收费 05月返点 05月免费 05月合计 总合计
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
02200 0 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
02205 0 0 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
02210 3 0 0 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
02220 0 0 0 0 4 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 4
02225 0 0 0 0 0 5 0 5 0 0 0 0 0 0 0 0 0 0 0 0 5
02230 0 0 0 0 0 0 6 6 0 0 0 0 0 0 0 0 0 0 0 0 6
02240 0 0 0 0 0 0 7 7 0 0 0 0 0 0 0 0 0 0 0 0 7
02245 0 0 0 0 0 0 0 0 8 0 0 8 0 0 0 0 0 0 0 0 8
02250 0 0 0 0 0 0 0 0 0 0 0 0 9 0 0 9 0 0 0 0 9
02256 0 0 0 0 0 0 0 0 0 0 0 0 0 10 0 10 11 0 0 11 21
*/