日期:2014-05-18  浏览次数:20462 次

问一条sql语句应该怎么写
Company表

id Cname Cstate

Userinfo表

id Uname 

现在我要查询一个这样的信息,用一条SQL语句查询出 b表 Uname=admin的数量有多少条 另外还要查询出a表Cname=admin 的Cstate信息

能够用一条语句就能实现?

------解决方案--------------------
SQL code

--试下
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)
)
go

insert 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'
go

create table Userinfo(
    id int,
    Uname varchar(10)
)
go

insert 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'
go

drop table Company,Userinfo
go

/*
no    3

------解决方案--------------------
SQL code

IF OBJECT_ID (N'Company') IS NOT NULL
DROP TABLE Company
go
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 select 3,'cc','no'
union all select 4,'admin','no'
UNION ALL SELECT 5,'admin','YEAH'
go

IF OBJECT_ID(N'Userinfo') IS NOT NULL
DROP TABLE Userinfo
GO
create table Userinfo(
    id int,
    Uname varchar(10)
)
go

insert 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

SELECT 'A表的Cstate信息'=A.Cstate,'Uname=admin的数量'=B.CC
FROM 
(SELECT Cstate 
 FROM Company WHERE Cname = 'admin') A
CROSS JOIN 
(SELECT CC=COUNT(*) FROM Userinfo
WHERE 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……