我的tablea表里面有两个主键companyid和bookid,以及tableb里面有companyid,companyname所有的companyid是对应的
如companyid bookid
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
现在我要汇总一下每个company有多少个book,如下面的结果A公司 3
B公司 4
C公司 2请问这个语句怎么写呀
如companyid bookid
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
现在我要汇总一下每个company有多少个book,如下面的结果A公司 3
B公司 4
C公司 2请问这个语句怎么写呀
from tablea a ,table b
where a.companyid = b.companyid
group by b.commpanyname
from tablea a
join tableb b
on a.companyid=b.companyid
LEFT JOIN TABLEB B ON A.COMPANYID=B.COMPANYID
GROUP BY A.COMPANYID,B.COMPANYNAME
from tablea a
join tableb b
on a.companyid=b.companyid
group by b.companyname手误,补充
数量=count(bookid)
from(
select companyname ,bookid
from tablea join tableb on
tablea.companyid=tableb.companyid) k
group by companyname
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tablea') IS NOT NULL
DROP TABLE tablea
GO
CREATE TABLE tablea( companyid int, bookid int )
go
insert tablea SELECT 1 , 1 UNION ALL SELECT
1 , 2 UNION ALL SELECT
1 , 3 UNION ALL SELECT
2 , 1 UNION ALL SELECT
2 , 2 UNION ALL SELECT
2 , 3 UNION ALL SELECT
2 , 4 UNION ALL SELECT
3 , 1 UNION ALL SELECT
3 , 2
go
IF OBJECT_ID('tableb') IS NOT NULL
DROP TABLE tableb
GO
CREATE TABLE tableb( companyid int, companyname varchar(10) )
go
insert tableb SELECT 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
go
select companyname,
数量=count(bookid)
from(
select companyname ,bookid
from tablea join tableb on
tablea.companyid=tableb.companyid) k
group by companyname
/*
companyname 数量
----------- -----------
A公司 3
B公司 4
C公司 2
*/
select companyname,num from tb b
join(
select companyid,count(bookid) as num from TA
group by companyid)a
on a.companyid=b.companyid
b.commpanyname,count(bookid)AS 个数
from
tablea a ,table b
where a.companyid = b.companyid
group by b.commpanyname
我的list是要tablea里面的数据,因为tablec里面的数据更多,怎么能关联起来 啊
-- 借阿蒂数据
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta( companyid int, bookid int )
go
insert ta SELECT 1 , 1 UNION ALL SELECT
1 , 2 UNION ALL SELECT
1 , 3 UNION ALL SELECT
2 , 1 UNION ALL SELECT
2 , 2 UNION ALL SELECT
2 , 3 UNION ALL SELECT
2 , 4 UNION ALL SELECT
3 , 1 UNION ALL SELECT
3 , 2
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( companyid int, companyname varchar(10) )
go
insert tb SELECT 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
goselect companyname,num from tb b
join(
select companyid,count(bookid) as num from TA
group by companyid)a
on a.companyid=b.companyid
companyname num
----------- -----------
A公司 3
B公司 4
C公司 2(3 行受影响)
select t1.*,t2.*
from (
select a.community, b.companyname 公司名,count(1) cnt
from tablea a
join tableb b
on a.companyid=b.companyid
group by b.companyname
) t1,tablec t2
where t1.companyid=t2.companyid
DROP TABLE tablea
GO
CREATE TABLE tablea( companyid int, bookid int )
go
insert tablea SELECT 1 , 1 UNION ALL SELECT
1 , 2 UNION ALL SELECT
1 , 3 UNION ALL SELECT
2 , 1 UNION ALL SELECT
2 , 2 UNION ALL SELECT
2 , 3 UNION ALL SELECT
2 , 4 UNION ALL SELECT
3 , 1 UNION ALL SELECT
3 , 2
go
IF OBJECT_ID('tableb') IS NOT NULL
DROP TABLE tableb
GO
CREATE TABLE tableb( companyid int, companyname varchar(10) )
go
insert tableb SELECT 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
goselect companyname,(select count(bookid) from tablea where tablea.companyid=tableb.companyid group by tablea.companyid) as 数量
from tablebdrop table tablea,tableb