查询的不同写法。

求一个查询的不同写法。。
两张表表结构如下

表A（公司表）
cid companyname
1 百度
2 新浪

表B (员工表）
id cid name
1 1 张三
2 2 李四
3 1 王五
4 2 马六

我现在想要这样一个结构

cid companyname personCount
1 百度 2
2 新浪 2

我写的一个查询如下
select cid,companyname,(select count(1) from b where b.cid=A.cid) personcount from A

我现在想知道这种需求共有几种写法每种写法的效率如何
那种最好

我实际的需求是有表A 另外有十张表都引用表A的id
然后查出这样一个结构

表AID 表1总数表2总数。。。。
1 20 30 。。。
2 200 3000 。。。
[解决办法]





create table T_A
(
Cid int,
CompanyName varchar (30)
)

create Table T_B
(
id int ,
Cid int,
Name varchar(50)
)


insert into T_A values ('1','百度')
insert into T_A values ('2','新浪')

insert into T_B values ('1','1','张三')
insert into T_B values ('2','2','李四')
insert into T_B values ('3','1','王五')
insert into T_B values ('4','2','赵六')


select distinct A.Cid,A.CompanyName,
COUNT(B.id) over (partition by A.Cid) 
from T_A A inner join T_B B on A.Cid=B.Cid

select A.*,B.number 
from T_A A,
(select Cid,COUNT(1) as number from T_B group by Cid ) B
where A.Cid=B.Cid

[解决办法]

USE test
GO

--&gt;生成表tb_Master

if object_id('tb_Master') is not null 
drop table [tb_Master]
Go
Create table [tb_Master](id smallint,[companyname] nvarchar(2))
Insert into [tb_Master]
Select 1,N'百度'
Union all Select 2,N'新浪'
Go


--&gt;生成表tb_s1

if object_id('tb_s1') is not null  
 
drop table [tb_s1]
Go
Create table [tb_s1]([id] smallint,[cid] smallint,[name] nvarchar(2))
Insert into [tb_s1]
Select 1,1,N'张三'
Union all Select 2,2,N'李四'
Union all Select 3,1,N'王五'
Union all Select 4,2,N'马六'


--&gt;生成表tb_s2

if object_id('tb_s2') is not null 
drop table [tb_s2]
Go
Create table [tb_s2]([id] smallint,[cid] smallint,[name] nvarchar(2))
Insert into [tb_s2]
Select 1,1,N'张三'
Union all Select 2,2,N'李四'
Union all Select 3,1,N'王五'
Union all Select 4,2,N'马六'
Union all Select 5,2,N'赵七'
Union all Select 6,2,N'陈八'

Go

DECLARE @sql NVARCHAR(MAX)

SELECT @sql=ISNULL(@sql,'')+NCHAR(13)+NCHAR(10)+' Cross apply '+N'(Select Count(1) As ['+name+N'总数] From '+name+' Where ['+name+'].cid=tb_Master.id Group by cid) As ['+name+']' FROM sys.sysobjects WHERE type='U' AND name LIKE 'tb_s%'

Exec (N'Select * From tb_Master '+@sql)

/*
Print:

Select * From tb_Master 
 Cross apply (Select Count(1) As [tb_s1总数] From tb_s1 Where [tb_s1].cid=tb_Master.id Group by cid) As [tb_s1]
 Cross apply (Select Count(1) As [tb_s2总数] From tb_s2 Where [tb_s2].cid=tb_Master.id Group by cid) As [tb_s2]

Result:

id     companyname tb_s1总数     tb_s2总数
------ ----------- ----------- -----------
1      百度          2           2
2      新浪          2           4


*/
GO

[解决办法]
select a.cid,a.companyname,count(1)personCount from tba a,tbb b where a.cid=b.cid group by a.cid,a.companyname
[解决办法]


if object_id('TableA','u') is not null
drop table TableA

go
create table TableA
(
cid int primary key,
companyname nvarchar(20)
)

go
insert into TableA values
(1,'百度'),
(2,'新浪'),
(3,'腾讯')

if object_id('TableB','u') is not null
drop table TableB

go
create table TableB
(
id int primary key,
cid int,
name nvarchar(20)
)

go
insert into TableB values
(1,1,'张三'),
(2,2,'李四'),
(3,1,'王五'),
(4,2,'马六')
 
select *From TableA
select *From TableB

--SQL
--注意，没有时对应为0
select A.cid,A.companyname,count(B.id) personCount From TableA A left join TableB B
on A.cid = B.cid
group by A.cid,A.companyname

--结果集
/*
cidcompanynamepersonCount
1百度2
2新浪2
3腾讯0
*/

[解决办法]

create table #company
(
    cid int not null,
    companyname varchar(50) not null
)
insert into #company
select 1,'百度' union all
select 2,'新浪'
create table #per
(
   id  int not null,
   cid int not null,
   name varchar(50) not null
)
insert into #per
select 1,1,'张三' union all
select 2,2,'李四' union all
select 3,1,'王五' union all
select 4,2,'马六'

select a.cid,a.companyname,COUNT(b.cid) as personcount
from #company as a
left join #per as b
on a.cid=b.cid
group by a.cid,a.companyname
cid         companyname             personcount
----------- ----------------        -----------
1           百度                         2
2           新浪                         2

(2 行受影响)

查询的不同写法 。

热点推荐

查询的不同写法。