22,210
社区成员
发帖
与我相关
我的任务
分享
--区别很大吗
select id,value,COUNT(*)over(partition by id,value)as cis
from dbo.tb;
select ID
,[BL1QY]
,[BL2QY]
,COUNT(*)over(partition by [BL1QY],[BL2QY])as SAME
from [DBO].[TB22]
ORDER BY ID
select id,value,COUNT(*)as cis
from dbo.tb
group by id,value;
--统计tb1中 a,b 在tb2中重复的次数
use tempdb;
go
create table dbo.tb1
(
a int,
b int
);
create table dbo.tb2
(
a int,
b int
);
insert into dbo.tb2
select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 2,3
union all select 3,2
union all select 4,1
union all select 1,3
union all select 3,3
union all select 2,1
union all select 3,4
union all select 4,2
union all select 2,2
union all select 3,1
union all select 4,4
union all select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 3,2
union all select 4,1
union all select 1,3
union all select 2,1
union all select 3,4
union all select 2,2
union all select 4,4
select a,b,(select count(*)
from dbo.tb2
where CAST(a as varchar(2))+CAST(b as varchar(2))=
CAST(t1.a as varchar(2))+CAST(t1.b as varchar(2)))as cs
from dbo.tb1 as t1;
/*
a b cs
----------- ----------- -----------
4 3 2
1 4 2
1 2 2
2 4 2
2 3 1
3 2 2
4 1 2
1 3 2
3 3 1
2 1 2
3 4 2
4 2 1
2 2 2
3 1 1
4 4 2
4 3 2
1 4 2
1 2 2
(18 行受影响)
*/
select id,value,COUNT(*)as cis
from dbo.tb
group by id,value;
use tempdb;
go
create table dbo.tb
(
id int,
value int
);
insert into dbo.tb
select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 2,3
union all select 3,2
union all select 4,1
union all select 1,3
union all select 3,3
union all select 2,1
union all select 3,4
union all select 4,2
union all select 2,2
union all select 3,1
union all select 4,4
union all select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 3,2
union all select 4,1
union all select 1,3
union all select 2,1
union all select 3,4
union all select 2,2
union all select 4,4
select id,value,COUNT(*)over(partition by id,value)as cis
from dbo.tb;
/*
id value cis
----------- ----------- -----------
1 2 2
1 2 2
1 3 2
1 3 2
1 4 2
1 4 2
2 1 2
2 1 2
2 2 2
2 2 2
2 3 1
2 4 2
2 4 2
3 1 1
3 2 2
3 2 2
3 3 1
3 4 2
3 4 2
4 1 2
4 1 2
4 2 1
4 3 2
4 3 2
4 4 2
4 4 2
(26 行受影响)
*/
select A+'*'+B
from(
select case when bl1qy>bl2qy then bl1qy else bl2qy end A,
case when bl1qy>bl2qy then bl2qy else bl1qy end B
from tb
)T
group by A+'*'+B