两个基础实用有关问题。请问上上

两个基础实用问题。请教下下。

SQL code

--有两个问题请教.1.create  table #teb(Send varchar(10),OrderId varchar(12),Rece varchar(10))goinsert into #teb select '111','ssss','999' union allselect '222','vvvv','111' union allselect '111','tttt','222' union allselect '333','aaaa','444' union allselect '999','bbbb','333' union allselect '222','cccc','111' union allselect '444','dddd','666' union allselect '111','eeee','444' goselect * from #tebgo--想要的结果如下:就是统计出左右两侧的数据，分别在左侧及右侧出现次数。--比如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

SQL code

SELECT name,SUM(countleft)countleft,SUM(countright)countrightFROM (select send name,COUNT(1) countleft ,0 countrightfrom #tebGROUP BY sendUNION ALL select Rece name,0,COUNT(1) from #tebGROUP BY Rece) aGROUP BY name go/*name       countleft   countright---------- ----------- -----------111        3           2222        2           1333        1           1444        1           2666        0           1999        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) countrightFROM    (         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        ) tGROUP BY name/*name    countleft    countright111    3    2222    2    1333    1    1444    1    2666    0    1999    1    1*/