问一条sql语句应该如何写

--试下select Cstate,总数=(select count(*) from Userinfo where Uname=a.Cname) from Company a where Cname='admin'
------解决方案--------------------
SQL code
select (select count(*) from Company where Cname='admin'),       (select count(*) from Userinfo where Cname='admin')
------解决方案--------------------
SQL code
create table Company(    id int,    Cname varchar(10),    Cstate varchar(10))goinsert into Company(id,Cname,Cstate)select 1,'aa','yes'union all select 2,'bb','yes'union all select 3,'cc','no'union all select 4,'admin','no'gocreate table Userinfo(    id int,    Uname varchar(10))goinsert into Userinfo(id,Uname)select 1,'aa'union all select 2,'bb'union all select 3,'cc'union all select 4,'dd'union all select 5,'admin'union all select 6,'admin'union all select 7,'admin'go--用一条SQL语句查询出 b表 Uname=admin的数量有多少条 另外还要查询出a表Cname=admin 的Cstate信息select Cstate,总数=(select count(*) from Userinfo where Uname=a.Cname) from Company a where Cname='admin'godrop table Company,Userinfogo/*no    3
------解决方案--------------------
SQL code
IF OBJECT_ID (N'Company') IS NOT NULLDROP TABLE Companygocreate table Company(    id int,    Cname varchar(10),    Cstate varchar(10))goinsert into Company(id,Cname,Cstate)select 1,'aa','yes'union all select 2,'bb','yes'union all select 3,'cc','no'union all select 4,'admin','no'UNION ALL SELECT 5,'admin','YEAH'goIF OBJECT_ID(N'Userinfo') IS NOT NULLDROP TABLE UserinfoGOcreate table Userinfo(    id int,    Uname varchar(10))goinsert into Userinfo(id,Uname)select 1,'aa'union all select 2,'bb'union all select 3,'cc'union all select 4,'dd'union all select 5,'admin'union all select 6,'admin'union all select 7,'admin'goSELECT 'A表的Cstate信息'=A.Cstate,'Uname=admin的数量'=B.CCFROM (SELECT Cstate  FROM Company WHERE Cname = 'admin') ACROSS JOIN (SELECT CC=COUNT(*) FROM UserinfoWHERE Uname='admin' GROUP BY Uname ) B
------解决方案--------------------
select Cstate, sum_count
 from Company a,
      (select sum(1) as sum_count
         from Userinfo
        where Uname = a.Cname
        group by Uname)
  where a.Cname = 'admin'

------解决方案--------------------
2楼的基本对，但是一点，太粗心了，Userinfo中是Uname
------解决方案--------------------
我很费解你们看题了吗？？？
探讨

2楼的基本对，但是一点，太粗心了，Userinfo中是Uname

------解决方案--------------------
探讨


SQL code

create table Company(
    id int,
    Cname varchar(10),
    Cstate varchar(10)
)
go

insert into Company(id,Cname,Cstate)
select 1,'aa','yes'
union all select 2,'bb','yes'
union all selec……