寻求一常见MSSQL存储过程,现在卡住了,不知如何写好,效率要高点!

[asimplefire]

数据库里存在如下一表:
member order date belongid
lww aaaa 090317 10
lww bbbb 090317 10
lww cccc 090317 10
lww dddd 090317 10
lww eeee 090318 11
lww ffff 090318 11
lww gggg 090318 11
lww hhhh 090318 11
lww iiii 090318 11
lww jjjj 090318 11

moh kkkk 090318 11
moh llll 090318 11
moh mmmm 090318 11
moh nnnn 090319 12
moh oooo 090319 12
现在需要写一个存储过程,要求输入@member来查询,查询出来的表如下:
当输入"lww"时:

成员 数目 时间 所属
lww 4 090317 10
lww 6 090318 11

当输入"moh"时:

成员 数目 时间 所属
moh 3 090318 11
moh 2 090319 12

各们MSSQL高手们,帮忙看一下吧!
谢谢啦!

[阿非]

declare @table table([member] varchar(20),[order] varchar(20),[date] varchar(20),[belongid] int)

insert into @table 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

select * from @table


select t.[member] 成员,(select count(belongid) from @table where [date]=t.[date] and [member]=t.[member]) 数目,t.[date] 时间,t.[belongid] 所属  from @table t where member='lww'
group by t.[belongid],t.[member],t.[date]

你自己写个存储过程 没问题吧 ，我用的是临时表 就不帮你写了。

[cppfaq]

发到SQL区，那里SQL高手多.

[阿非]

select count(belongid) from @table where [date]=t.[date] and [member]=t.[member]

你也可以把它 拿出来 写 在函数里，传两个参数。

[hongqi162]

create proc sp_query(@member varchar) as
select member as 成员,count(*) as 数目,date as 时间,belongid as 所属 from 表 where member=@member group by member,date,belongid

[asimplefire]

[Quote=引用 1 楼 Sandy945 的回复:]
SQL code

declare @table table([member] varchar(20),[order] varchar(20),[date] varchar(20),[belongid] int)

insert into @table 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 'l…
[/Quote]

非常感谢这们仁兄,可是我的记录只是列举了几十条而已,按照这种写法,记录多了,可不是太复杂了?

[cceon]

哇，今天看到了传说中的牛人了

[阿非]

4楼 已经修正了~

[cppfaq]

alter proc testproc
@member nchar(3)
as
select member as N'成员', count(member) as N'数目', date as N'时间', belongid as N'所属'
from testx
where testx.member = @member
group by member, date, belongid