现有一个表
月份 部门id 商品id 标记1 标记 2
200907 1034 10001273 0 1
200907 1035 10001273 0 1
200907 1036 10001273 0 1
200907 1037 10001273 0 1
200907 1038 10001273 0 1
200907 001 10001274 1 1
200907 1004 10001274 1 1
200907 1020 10001274 0 1
200907 1025 10001274 0 1
200907 1026 10001274 0 1
200907 1037 10001274 0 1
如何取得 200907月 每个标记值为1 商品个数
如
200907 1 2需要用一个sql语句完成
月份 部门id 商品id 标记1 标记 2
200907 1034 10001273 0 1
200907 1035 10001273 0 1
200907 1036 10001273 0 1
200907 1037 10001273 0 1
200907 1038 10001273 0 1
200907 001 10001274 1 1
200907 1004 10001274 1 1
200907 1020 10001274 0 1
200907 1025 10001274 0 1
200907 1026 10001274 0 1
200907 1037 10001274 0 1
如何取得 200907月 每个标记值为1 商品个数
如
200907 1 2需要用一个sql语句完成
标记1,
个数=count(*)
from
tb
where 标记1=1 and 标记2=1
group by 月份
from (
select 商品id,max(标记1) as c1,max(标记2) as c2
from 一个表
where 月份=200907
group by 商品id
) t
3> go
月份 |部门id |商品id |标记1 |标记2
-----------|-----------|-----------|-----------|-----------
200907| 1034| 10001273| 0| 1
200907| 1035| 10001273| 0| 1
200907| 1036| 10001273| 0| 1
200907| 1037| 10001273| 0| 1
200907| 1038| 10001273| 0| 1
200907| 001| 10001274| 1| 1
200907| 1004| 10001274| 1| 1
200907| 1020| 10001274| 0| 1
200907| 1025| 10001274| 0| 1
200907| 1026| 10001274| 0| 1
200907| 1037| 10001274| 0| 1(11 rows affected)
1> select 2007,sum(c1),sum(c2)
2> from (
3> select 商品id,max(标记1) as c1,max(标记2) as c2
4> from 一个表
5> where 月份=200907
6> group by 商品id
7> ) t
8> go
| |
-----------|-----------|-----------
2007| 1| 2(1 rows affected)
1>
from tb
where 月份 = '200907' and 标记1 = 1 and 标记2 = 1
insert into tb values('200907', 1034, '10001273', 0, 1)
insert into tb values('200907', 1035, '10001273', 0, 1)
insert into tb values('200907', 1036, '10001273', 0, 1)
insert into tb values('200907', 1037, '10001273', 0, 1)
insert into tb values('200907', 1038, '10001273', 0, 1)
insert into tb values('200907', 001, '10001274', 1, 1)
insert into tb values('200907', 1004, '10001274', 1, 1)
insert into tb values('200907', 1020, '10001274', 0, 1)
insert into tb values('200907', 1025, '10001274', 0, 1)
insert into tb values('200907', 1026, '10001274', 0, 1)
insert into tb values('200907', 1037, '10001274', 0, 1)
goselect 月份 ,
标记1 = (select count(distinct 商品id) from tb where 月份 = t.月份 and 标记1 = 1),
标记2 = (select count(distinct 商品id) from tb where 月份 = t.月份 and 标记2 = 1)
from tb t
where 月份 = '200907'
group by 月份drop table tb/*
月份 标记1 标记2
---------- ----------- -----------
200907 1 2(所影响的行数为 1 行)
*/
这里写法是很好 不错 我这里的group by 可能会是动态的
现在是按月份汇总 也可能按 部门汇总 也可能按两者一起汇总
这样的话 里面的子查询就不好写了
能有一个函数就好
create table tb(月份 varchar(10) ,部门id varchar(10) ,商品id varchar(10) ,标记1 int ,标记2 int)
insert into tb values('200907', '1034', '10001273', 0, 1)
insert into tb values('200907', '1035', '10001273', 0, 1)
insert into tb values('200907', '1036', '10001273', 0, 1)
insert into tb values('200907', '1037', '10001273', 0, 1)
insert into tb values('200907', '1038', '10001273', 0, 1)
insert into tb values('200907', '001', '10001274', 1, 1)
insert into tb values('200907', '1004', '10001274', 1, 1)
insert into tb values('200907', '1020', '10001274', 0, 1)
insert into tb values('200907', '1025', '10001274', 0, 1)
insert into tb values('200907', '1026', '10001274', 0, 1)
insert into tb values('200907', '1037', '10001274', 0, 1)
gocreate proc my_proc @zd1 varchar(20) , @zd2 varchar(20) , @val1 varchar(20) , @val2 varchar(20)
as
begin
declare @sql as varchar(1000)
if @zd2 is null or @zd2 = ''
begin
set @sql = 'select ' + @zd1 + ' , ' +
'标记1 = (select count(distinct 商品id) from tb where ' + @zd1 + ' = t.' + @zd1 + ' and 标记1 = 1), ' +
'标记2 = (select count(distinct 商品id) from tb where ' + @zd1 + ' = t.' + @zd1 + ' and 标记2 = 1) ' +
'from tb t where ' + @zd1 + ' = ''' + @val1 + ''' group by ' + @zd1
end
else
begin
set @sql = 'select ' + @zd1 + ' , ' + @zd2 + ' , ' +
'标记1 = (select count(distinct 商品id) from tb where ' + @zd1 + ' = t.' + @zd1 + ' and ' + @zd2 + ' = t.' + @zd2 + ' and 标记1 = 1), ' +
'标记2 = (select count(distinct 商品id) from tb where ' + @zd1 + ' = t.' + @zd1 + ' and ' + @zd2 + ' = t.' + @zd2 + ' and 标记2 = 1) ' +
'from tb t where ' + @zd1 + ' = ''' + @val1 + ''' and ' + @zd2 + ' = ''' + @val2 + ''' group by ' + @zd1 + ' , ' + @zd2
end
exec(@sql)
end
goexec my_proc '月份' , '', '200907' , ''
/*
月份 标记1 标记2
---------- ----------- -----------
200907 1 2(所影响的行数为 1 行)
*/exec my_proc '部门id' , '' , '1037' , ''
/*
部门id 标记1 标记2
---------- ----------- -----------
1037 0 2(所影响的行数为 1 行)
*/exec my_proc '月份' , '部门id', '200907' , '1037'
/*
月份 部门id 标记1 标记2
---------- ---------- ----------- -----------
200907 1037 0 2(所影响的行数为 1 行)
*/drop table tb
drop proc my_proc
--有关动态sql语句基本语法见下:
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