求一sql~~~
FROM TO NO
A B 123
A B 1234
A B 123
B B 1234
A B 123
C B 1234
这是一个发送明细表
用户A将NO发送给B
求将同样的NO发送给不同2个以上用户的 用户数量。。。
[解决办法]
declare @t table([FROM] varchar(1),[TO] varchar(1),[NO] int)
insert @t
select 'A','B',123 union all
select 'A','B',1234 union all
select 'A','B',123 union all
select 'B','A',123 union all
select 'A','B',123 union all
select 'C','B',1234 union all
select 'D','C',1234 union all
select 'C','A',1234 union all
select 'C','D',1234 union all
select 'B','C',123 union all
select 'B','D',123 UNION ALL
select 'B','A',777 union all
select 'B','C',777 union all
select 'B','D',777
;WITH MACO AS
(
SELECT [FROM],COUNT(1) AS CNT FROM
(
SELECT [FROM],[NO] FROM @T GROUP BY [FROM],[NO] HAVING (COUNT(DISTINCT [TO])>2)
) A GROUP BY [FROM]
)
SELECT COUNT(1) FROM MACO
select COUNT(distinct [FROM]) from(
select [NO],[FROM] from tb group by [NO],[FROM] having COUNT(distinct [TO])>2) a
