求一个查询的不同写法 。。
两张表 表结构如下
表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
-->生成表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
-->生成表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'马六'
-->生成表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
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 行受影响)
