求一句增加统计次数列的sql～

[paulayo911]

表结构如下：

world id goods date
------ ---- ----- ---------
1001 11 aa 09-07-01
1001 11 bb 09-07-01
1001 12 aa 09-07-01
1001 12 bb 09-07-01
1002 13 aa 09-07-01
1002 13 bb 09-07-01
1002 12 aa 09-07-02
1002 12 bb 09-07-02

需要通过sql查询增加一列统计当前id的相同goods出现次数，结果如下：

world id goods date num
------ ---- ----- --------- -----
1001 11 aa 09-07-01 1
1001 11 bb 09-07-01 1
1001 12 aa 09-07-01 2
1001 12 bb 09-07-01 2
1002 13 aa 09-07-01 1
1002 13 bb 09-07-01 1
1002 12 aa 09-07-02 2
1002 12 bb 09-07-02 2

同时当where给定world、goods、date等条件时也能同样统计出num列，求高手指教～

[--小F--]

首先创建测试表、添加数据。 
create table #t(a int,b int,c int,d int,e int) 
insert into #t values(1,2,3,4,5) 
insert into #t values(1,2,3,4,6) 
insert into #t values(1,2,3,4,7) 
insert into #t values(1,2,3,4,8) 
insert into #t values(1,3,3,4,5) 
insert into #t values(1,3,3,4,6) 
insert into #t values(1,3,3,4,8) 
insert into #t values(1,3,3,4,7) 

insert into #t values(2,2,2,4,5) 
insert into #t values(2,2,3,4,6) 
insert into #t values(2,2,4,4,7) 
insert into #t values(2,2,5,4,8) 
insert into #t values(2,3,6,4,5) 
insert into #t values(2,3,3,4,6) 
insert into #t values(2,3,3,4,8) 
insert into #t values(2,3,3,4,7)
情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup 
情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    b, 
sum(c),sum(d),sum(e) from #t 
group by a,b with rollup 
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    b, 
    c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(c)=0 or grouping(a)=1 
情况三：有多个分类汇总列，需要全部的小计和合计。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, 
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
另外一种显示小计的方式 
select case when grouping(a)=1 then '合计' 
    when grouping(b)=1 then cast(a as varchar)+'小计' 
    else cast(a as varchar) end a, 
    case when grouping(b)=0 and grouping(c)=1 
    then cast(b as varchar)+'小计' else cast(b as varchar) end b, 
    case when grouping(c)=1 and grouping(b)=0 
    then '' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
情况四：有多个分类汇总列，需要部分的小计和合计 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, 
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(a)=1 or grouping(b)=0 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(a)=1 or grouping(b)=1 or grouping(c)=

[feixianxxx]

select *,
num=count(*)
from tb 
group by world , id , date   

[昵称被占用了]

select *,(select count(1) from tb where id =a.id  and goods=a.goods) as num
from tb a

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([world] int,[id] int,[goods] varchar(2),[date] datetime)
insert [tb]
select 1001,11,'aa','09-07-01' union all
select 1001,11,'bb','09-07-01' union all
select 1001,12,'aa','09-07-01' union all
select 1001,12,'bb','09-07-01' union all
select 1002,13,'aa','09-07-01' union all
select 1002,13,'bb','09-07-01' union all
select 1002,12,'aa','09-07-02' union all
select 1002,12,'bb','09-07-02'
 
---查询---
select 
  a.*,
  b.num
from
  tb a
left join
  (select id,goods,count(1) as num from tb group by id,goods) b
on
  a.id=b.id and a.goods=b.goods

---结果---
world       id          goods date                                                   num         
----------- ----------- ----- ------------------------------------------------------ ----------- 
1001        11          aa    2009-07-01 00:00:00.000                                1
1001        11          bb    2009-07-01 00:00:00.000                                1
1001        12          aa    2009-07-01 00:00:00.000                                2
1001        12          bb    2009-07-01 00:00:00.000                                2
1002        13          aa    2009-07-01 00:00:00.000                                1
1002        13          bb    2009-07-01 00:00:00.000                                1
1002        12          aa    2009-07-02 00:00:00.000                                2
1002        12          bb    2009-07-02 00:00:00.000                                2

（所影响的行数为 8 行）

[olddown]

select a.world,a.id,a.goods,a.date,b.num from 表 a, (select goods,count(1) as num from 表 where world=@world and goods=@goods and date=@date group by goods) b where a.goods=b.goods and world=@world and goods=@goods and date=@date

[--小F--]

汗 理解错误
看树哥的
撤退睡觉
丢人

[--小F--]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([world] int,[id] int,[goods] varchar(2),[date] datetime)
insert [tb]
select 1001,11,'aa','09-07-01' union all
select 1001,11,'bb','09-07-01' union all
select 1001,12,'aa','09-07-01' union all
select 1001,12,'bb','09-07-01' union all
select 1002,13,'aa','09-07-01' union all
select 1002,13,'bb','09-07-01' union all
select 1002,12,'aa','09-07-02' union all
select 1002,12,'bb','09-07-02'

select 
world,
[id],
goods,
convert(varchar(10),[date],120) as [date],
(select count(1) from tb where id =a.id  and goods=a.goods) as num
from tb a
/*world       id          goods date       num         
----------- ----------- ----- ---------- ----------- 
1001        11          aa    2009-07-01 1
1001        11          bb    2009-07-01 1
1001        12          aa    2009-07-01 2
1001        12          bb    2009-07-01 2
1002        13          aa    2009-07-01 1
1002        13          bb    2009-07-01 1
1002        12          aa    2009-07-02 2
1002        12          bb    2009-07-02 2

（所影响的行数为 8 行）
*/

[feixianxxx]

bei 写掉了。。。顶

[paulayo911]

SQL codeifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([world]int,[id]int,[goods]varchar(2),[date]datetime)insert[tb]select1001,11,'aa','09-07-01'unionallselect1001,11,'bb','09-07-01'unionallselect1001,12,'aa','09-07-01'unionallselect1001,12,'bb','09-07-01'unionallselect1002,13,'aa','09-07-01'unionallselect1002,13,'bb','09-07-01'unionallselect1002,12,'aa','09-07-02'unionallselect1002,12,'bb','09-07-02'select
world,[id],
goods,convert(varchar(10),[date],120)as[date],
(selectcount(1)from tbwhere id=a.idand goods=a.goods)as numfrom tb a/*world id goods date num
----------- ----------- ----- ---------- -----------
1001 11 aa 2009-07-01 1
1001 11 bb 2009-07-01 1
1001 12 aa 2009-07-01 2
1001 12 bb 2009-07-01 2
1002 13 aa 2009-07-01 1
1002 13 bb 2009-07-01 1
1002 12 aa 2009-07-02 2
1002 12 bb 2009-07-02 2

（所影响的行数为 8 行）*/

原来这么简单。。。大脑严重缺氧了～