create procedure my_proc @member as varchar(10)
as
begin
select 成员 , count(*) 数目 , 时间 , 所属 from tb where member = @member group by 成员 , 时间 , 所属
end
go
as
begin
select 成员 , count(*) 数目 , 时间 , 所属 from tb where member = @member group by 成员 , 时间 , 所属
end
go
解决方案 »
- 请教一个存储过程的问题
- 我建的触发器,如何能在查询分析器中看到呀。我只能在企业管理器中看到。(就象在查询分析器中查看存贮过程那样)
- SQL语句不知道该怎么写了??求助
- SQL 循环不停止....急!
- 求一字符串汇总方法 SQL。。。
- 如何在2005的sys.databases视图建立这样的触发器呢
- 求教怎么提高夸server的数据插入性能
- 如何将异地的图片文件上传到SQL服务器上?
- 急需!在线等待!答后必有重谢!!
- SQL Server中如何产生一自动创建的主键,而不是自己指定,就象Access中一样......
- sqlserver2000和2005的默认事务隔离有什么区别?
- 2000中有没有类似rownmu的sql函数或者方法?(为了读从n到m条数据)
Create proc test
@menber varchar2(20)
as
begin
select member as 成員,count(*) as 數目,date as 時間,belongid as 所屬
From tbl
where member=@member
group by member,date,belongid
end
insert into tb values ('lww' , 'aaaa' , '090317' , 10 )
insert into tb values ('lww' , 'bbbb' , '090317' , 10 )
insert into tb values ('lww' , 'cccc' , '090317' , 10 )
insert into tb values ('lww' , 'dddd' , '090317' , 10 )
insert into tb values ('lww' , 'eeee' , '090318' , 11 )
insert into tb values ('lww' , 'ffff' , '090318' , 11 )
insert into tb values ('lww' , 'gggg' , '090318' , 11 )
insert into tb values ('lww' , 'hhhh' , '090318' , 11 )
insert into tb values ('lww' , 'iiii' , '090318' , 11 )
insert into tb values ('lww' , 'jjjj' , '090318' , 11 )
insert into tb values ('moh' , 'kkkk' , '090318' , 11 )
insert into tb values ('moh' , 'llll' , '090318' , 11 )
insert into tb values ('moh' , 'mmmm' , '090318' , 11 )
insert into tb values ('moh' , 'nnnn' , '090319' , 12 )
insert into tb values ('moh' , 'oooo' , '090319' , 12 )
gocreate procedure my_proc @member as varchar(10)
as
begin
select member , count(*) 数目 , date , belongid from tb where member = @member group by member ,date , belongid
end
goexec my_proc 'lww'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
lww 4 090317 10
lww 6 090318 11(所影响的行数为 2 行)
*/exec my_proc 'moh'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
moh 3 090318 11
moh 2 090319 12(所影响的行数为 2 行)
*/drop proc my_proc
drop table tb
@member varchar(10)
as
set nocount on
select
成员=member,
数目=count(distinct order),
时间=[date],
所属=[belongid]
from tb
where member=@member
group by member,date,belongidgo
@member varchar(10)
as
set nocount on
select
成员=member,
数目=count(distinct [order]),
时间=[date],
所属=[belongid]
from tb
where member=@member
group by member,date,belongidgo
insert into tb values ('lww' , 'aaaa' , '090317' , 10 )
insert into tb values ('lww' , 'bbbb' , '090317' , 10 )
insert into tb values ('lww' , 'cccc' , '090317' , 10 )
insert into tb values ('lww' , 'dddd' , '090317' , 10 )
insert into tb values ('lww' , 'eeee' , '090318' , 11 )
insert into tb values ('lww' , 'ffff' , '090318' , 11 )
insert into tb values ('lww' , 'gggg' , '090318' , 11 )
insert into tb values ('lww' , 'hhhh' , '090318' , 11 )
insert into tb values ('lww' , 'iiii' , '090318' , 11 )
insert into tb values ('lww' , 'jjjj' , '090318' , 11 )
insert into tb values ('moh' , 'kkkk' , '090318' , 11 )
insert into tb values ('moh' , 'llll' , '090318' , 11 )
insert into tb values ('moh' , 'mmmm' , '090318' , 11 )
insert into tb values ('moh' , 'nnnn' , '090319' , 12 )
insert into tb values ('moh' , 'oooo' , '090319' , 12 )
gocreate procedure my_proc @member as varchar(10)
as
begin
--这里三条语句都能实现你的需求,自己选择一个。
select member , count(*) 数目 , date , belongid from tb where member = @member group by member ,date , belongid
select member , count(*) 数目 , date , max(belongid) belongid from tb where member = @member group by member ,date
select member , count(*) 数目 , date , min(belongid) belongid from tb where member = @member group by member ,date
end
goexec my_proc 'lww'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
lww 4 090317 10
lww 6 090318 11(所影响的行数为 2 行)
*/exec my_proc 'moh'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
moh 3 090318 11
moh 2 090319 12(所影响的行数为 2 行)
*/drop proc my_proc
drop table tb
(
@member as varchar(20)
)
as
begin
select 成员,count(*) 数目,时间,所属 from tb where member=@member group by 成员,时间,所属
end
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([member] varchar(3),[order] varchar(4),[date] varchar(6),[belongid] int)
insert [tb]
select 'lww','aaaa','090317',10 union all
select 'lww','bbbb','090317',10 union all
select 'lww','cccc','090317',10 union all
select 'lww','dddd','090317',10 union all
select 'lww','eeee','090318',11 union all
select 'lww','ffff','090318',11 union all
select 'lww','gggg','090318',11 union all
select 'lww','hhhh','090318',11 union all
select 'lww','iiii','090318',11 union all
select 'lww','jjjj','090318',11 union all
select 'moh','kkkk','090318',11 union all
select 'moh','llll','090318',11 union all
select 'moh','mmmm','090318',11 union all
select 'moh','nnnn','090319',12 union all
select 'moh','oooo','090319',12
---存储过程---
create proc testproc @member varchar(100)
as
select
member as 成员,
count(1) as 数目,
[date] as 时间,
belongid as 所属
from tb
where member=@member
group by member,[date],belongid
---执行存储过程---
exec testproc 'lww'
exec testproc 'moh'---结果---
/**
成员 数目 时间 所属
---- ----------- ------ -----------
lww 4 090317 10
lww 6 090318 11(所影响的行数为 2 行)成员 数目 时间 所属
---- ----------- ------ -----------
moh 3 090318 11
moh 2 090319 12(所影响的行数为 2 行)
**/
insert into tb values ('lww' , 'aaaa' , '090317' , 10 )
insert into tb values ('lww' , 'bbbb' , '090317' , 10 )
insert into tb values ('lww' , 'cccc' , '090317' , 10 )
insert into tb values ('lww' , 'dddd' , '090317' , 10 )
insert into tb values ('lww' , 'eeee' , '090318' , 11 )
insert into tb values ('lww' , 'ffff' , '090318' , 11 )
insert into tb values ('lww' , 'gggg' , '090318' , 11 )
insert into tb values ('lww' , 'hhhh' , '090318' , 11 )
insert into tb values ('lww' , 'iiii' , '090318' , 11 )
insert into tb values ('lww' , 'jjjj' , '090318' , 11 )
insert into tb values ('moh' , 'kkkk' , '090318' , 11 )
insert into tb values ('moh' , 'llll' , '090318' , 11 )
insert into tb values ('moh' , 'mmmm' , '090318' , 11 )
insert into tb values ('moh' , 'nnnn' , '090319' , 12 )
insert into tb values ('moh' , 'oooo' , '090319' , 12 )
godeclare @Member varchar(20)
set @Member='lww'
select member,count(member),date,belongid from tb where member=@Member group by member,date,belongid
create procedure pro_get
@member varchar(25)
as
begin select distinct member
,数目 = (select count([order]) from tb where member = a.member and belongid = a.belongid)
,date,belongid
from tb a
where member = @memberendexec pro_get 'lww'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
lww 4 090317 10
lww 6 090318 11(2 row(s) affected)*/exec pro_get 'moh'
/*
member 数目 date belongid
---------- ----------- ---------- -----------
moh 2 090319 12
moh 3 090318 11(2 row(s) affected)
*/