A表
数据
001 A1 B1 C1
001 A1 B1 C1
002 A2 B2 C3
003 A3 B3 C3
004 A4 B4 C4
004 A4 B4 C4
sql执行结果为: 001 A1 B1 C1 2
001 A1 B1 C1 2
002 A2 B2 C3 1
003 A3 B3 C3 1
004 A4 B4 C4 2
004 A4 B4 C4 2就是将001 分组并统计数量
求教 sql???
数据
001 A1 B1 C1
001 A1 B1 C1
002 A2 B2 C3
003 A3 B3 C3
004 A4 B4 C4
004 A4 B4 C4
sql执行结果为: 001 A1 B1 C1 2
001 A1 B1 C1 2
002 A2 B2 C3 1
003 A3 B3 C3 1
004 A4 B4 C4 2
004 A4 B4 C4 2就是将001 分组并统计数量
求教 sql???
select z.*,[num]=b.c
from a z cross apply
(select c=count(*) from a where z.字段1=字段1 and z.字段2=字段2 and z.字段3=字段3 and z.字段4=字段4 )b
from a z cross apply
(select c=count(*) from a where z.字段1=字段1 )b
where a.id= b.id
from A表 t
select a.*,b.count from A a inner join (select id count(1) as count from A group by id) b
on a.id= b.id
能不能通过gruop实现呢~
不想多一次查询
--插入数值
insert into test......
select test.*,f.count from test left join (select a, count(a) as count from test group by a,b,c,d) as f on test.a=f.a
if object_id('t') is not null drop table t
go
create table t([a] varchar(50),[b] varchar(50),[c] varchar(50),[d] varchar(50))
insert into t
select '001','A1','B1','C1' union all
select '001','A1','B1','C1' union all
select '002','A2','B2','C3' union all
select '003','A3','B3','C3' union all
select '004','A4','B4','C4' union all
select '004','A4','B4','C4'
go
select t.*,t2.cn from t join (select a,count(1) as cn from t group by a) t2 on t.a = t2.aselect * from t t1 cross apply (select count(1) as cn from t where a = t1.a) p
/*
a b c d cn
---------- ----------- -------- ------- -----------
001 A1 B1 C1 2
001 A1 B1 C1 2
002 A2 B2 C3 1
003 A3 B3 C3 1
004 A4 B4 C4 2
004 A4 B4 C4 2*/
select test.*,count(a)over(partition by a) from test
所以 用GROUP BY 想一句得出你要的结果不可能