--有两个问题请教.1.
create  table #teb(
Send varchar(10),
OrderId varchar(12),
Rece varchar(10)
)
go
insert into #teb 
select '111','ssss','999' union all
select '222','vvvv','111' union all
select '111','tttt','222' union all
select '333','aaaa','444' union all
select '999','bbbb','333' union all
select '222','cccc','111' union all
select '444','dddd','666' union all
select '111','eeee','444' 
go
select * from #teb
go
--想要的结果如下:就是统计出左右两侧的数据，分别在左侧及右侧出现次数。
--比如111左侧第一列出现3次，右侧第三列2次。
-----------------------
/*
'111',3,2
'222',2,1
'333',1,1
'999',1,1
'444',1,2
'666',0,1
*/
-----------------------
--2.问个简单问题：对于普通的用一条语句查询两个表的记录总数，你有几种方法?用Left Join能否实现？
--我现在只想到
select (select count(*) from VO_UserInfor) as 总数一,(select count(*) as 总数二 from VO_Department) from XXX

SELECT name,SUM(countleft)countleft,SUM(countright)countright
FROM (
select send name,COUNT(1) countleft ,0 countright
from #teb
GROUP BY send
UNION ALL 
select Rece name,0,COUNT(1) 
from #teb
GROUP BY Rece
) a
GROUP BY name 
go

/*
name       countleft   countright
---------- ----------- -----------
111        3           2
222        2           1
333        1           1
444        1           2
666        0           1
999        1           1

(6 行受影响)

*/

------解决方案--------------------
1.答案：
select item,SUM(L) as L,SUM(R) as R
from  
(
select send as item,COUNT(*) as L,0 as R
from #teb
group by send
union  
select Rece,0 as L,COUNT(*) as R
from #teb
group by Rece
) as a  
group by item


------解决方案--------------------

--1
select a.*,
      send=(select count(*) from #teb where send=a.send),
      Rece=(select count(*) from #teb where Rece=a.send)
from (select send from #teb union select Rece from #teb) a

--2
  select (select count(*) from VO_UserInfor) as 总数一,
       (select count(*) from VO_Department) as 总数二

------解决方案--------------------

SQL code
SELECT  name , SUM(countleft) countleft , SUM(countright) countright
FROM    (
         SELECT send name , COUNT(1) countleft , 0 countright
         FROM   #teb
         GROUP BY send
         UNION ALL
         SELECT Rece name , 0 countleft , COUNT(1) countright
         FROM   #teb
         GROUP BY Rece
        ) t
GROUP BY name
/*
name    countleft    countright
111    3    2
222    2    1
333    1    1
444    1    2
666    0    1
999    1    1
*/