我有一个表,结构如下:send recive
-----------------------
1 2
1 2
2 1
1 3
4 1
-------------------------------------
我想写一个语句得到send为1 或者recive为1 的所有的send或recive值,结果应该如下result
----------------
2
3
4
------------------
用临时表可以实现,有没有简单的语句就能达到以上的结果,更进一步要求的话,能不能同时把他们关联的数据条数得到
result count
---------------------
2 3
3 1
4 1
---------------------
先谢谢各位了,求指点。
-----------------------
1 2
1 2
2 1
1 3
4 1
-------------------------------------
我想写一个语句得到send为1 或者recive为1 的所有的send或recive值,结果应该如下result
----------------
2
3
4
------------------
用临时表可以实现,有没有简单的语句就能达到以上的结果,更进一步要求的话,能不能同时把他们关联的数据条数得到
result count
---------------------
2 3
3 1
4 1
---------------------
先谢谢各位了,求指点。
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (send int,recive int)
insert into #tb
select 1,2 union all
select 1,2 union all
select 2,1 union all
select 1,3 union all
select 4,1select recive,count(*) as [count]
from
(
select * from #tb where send=1
union all
select recive,send from #tb where recive=1
)t
group by recive
(
SELECT DISTINCT recive as value FROM Table where send=1
UNION
SELECT DISTINCT send as value FROM Table where recive=1
) T
(
SELECT DISTINCT recive as value FROM TT where send=1
UNION
SELECT DISTINCT send as value FROM TT where recive=1
) T
SELECT value,COUNT(*) value1 FROM
(
SELECT recive as value FROM TT where send=1
UNION ALL
SELECT send as value FROM TT where recive=1
) T
GROUP BY value
insert into #
select 1,2 union all
select 1,2 union all
select 2,1 union all
select 1,3 union all
select 4,1select [result]=case when send=1 then recive
when recive=1 then send end ,[count]=SUM( case when (send =1 or recive=1) then 1 else 0 end)
from # group by case when send=1 then recive when recive=1 then send end result count
----------- -----------
2 3
3 1
4 1(3 行受影响)
Select
a.result,
(select count(*) from (select send+recive-1 as result from test1 where send=1 or recive=1) b where b.result=a.result) as counter
From (select distinct send+recive-1 as result from test1 where send =1 or recive=1) a