我的tablea表里面有两个主键companyid和bookid,
tableb里面有companyid,companyname,
tablec里面companyid,bookid,date,主键也是companyid和bookid,
所有的companyid是对应的 tablea
如companyid bookid
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
现在我要汇总tablea里面每个company有多少个book,并且是在某个日期之间(2008-01-01到2008-12-31)如下面的结果 A公司 3
B公司 4
C公司 2 请问这个语句怎么写呀
tableb里面有companyid,companyname,
tablec里面companyid,bookid,date,主键也是companyid和bookid,
所有的companyid是对应的 tablea
如companyid bookid
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
现在我要汇总tablea里面每个company有多少个book,并且是在某个日期之间(2008-01-01到2008-12-31)如下面的结果 A公司 3
B公司 4
C公司 2 请问这个语句怎么写呀
b.commpanyname,count(bookid)AS 个数
from
tablea a ,table b
where a.companyid = b.companyid AND [date] BETWEEN '2008-01-01' AND '2008-12-31'
group by b.commpanyname
b.commpanyname,count(bookid)AS 个数
from
tablea a ,table b
where a.companyid = b.companyid AND DATEDIFF(YY,[date],'2008-1-1')=0
group by b.commpanynameOR YEAR([DATE])='2008'
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
and
t2.date between '2008-01-01' and '2008-12-31'
from (
select a.community, b.companyname 公司名,count(1) cnt
from tablea a
join tableb b
on a.companyid=b.companyid
group by b.companyname
)tableb t1,tablec t2
where
t1.companyid=t2.companyid
and
t2.date between '2008-01-01' and '2008-12-31'
select companyname,cnt from tableb a,
(select companyid,cnt=count(*) from tablec where date between '2008-01-01' and '2008-12-31' group by companyid)b
where a.companyid=b.companyid
create table tb(id1 int,id2 int)
insert tb
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
select id1,MAX(id2) as id2 from tb group by id1
/*
id1 id2
1 3
2 4
3 2
*/
create table tb(name varchar(10),id1 int,id2 int)
insert tb
select 'A公司',1 , 1
union all select 'A公司', 1 , 2
union all select 'A公司', 1 , 3
union all select 'B公司',2 , 1
union all select 'B公司', 2 , 2
union all select 'B公司',2 , 3
union all select 'B公司', 2 , 4
union all select 'C公司', 3 , 1
union all select 'C公司', 3 , 2
select NAME,id1,MAX(id2) as id2 from tb group by NAME,id1
NAME id1 id2
A公司 1 3
B公司 2 4
C公司 3 2
from tablea a inner join tableb b on a.companyid=b.companyid inner join tablec c on a.companyid=c.companyid and a.bookid=c.bookid
where c.date between '2008-01-01' and '2008-12-31'
group by b.Companyname
from tablea a inner join tableb b on a.companyid=b.companyid inner join tablec c on a.companyid=c.companyid and a.bookid=c.bookid
where c.date between '2008-01-01' and '2008-12-31'
group by b.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
IF OBJECT_ID('tablec') IS NOT NULL
DROP TABLE tablec
GO
CREATE TABLE tablec( companyid int,bookid int,[date] datetime )
go
insert tablec
SELECT 1,1,'2008-01-01' union all
SELECT 1,1,'2008-07-01' union all
SELECT 1,2,'2008-09-01' union all
SELECT 2,1,'2008-10-01' union all
SELECT 2,2,'2008-03-01' union all
SELECT 2,3,'2009-01-01' union all
SELECT 2,4,'2009-01-01' union all
SELECT 3,1,'2008-12-3'select companyname,
数量=count(bookid)
from(
select companyname ,tablea.bookid ,date
from tablea join tableb on
tablea.companyid=tableb.companyid
join tablec on tablea.companyid=tablec.companyid) k
where convert(varchar(10),k.date,120) between '2008-01-01' and '2008-12-31'
group by companyname
/*
companyname 数量
----------- -----------
A公司 9
B公司 8
C公司 2*/
select companyname,
数量=count(bookid)
from(
select companyname ,tablea.bookid ,date
from tablea join tableb on
tablea.companyid=tableb.companyid
join tablec on tablea.companyid=tablec.companyid) k
where DATEDIFF(YY,[DATE],'2008-1-1')=0
group by companyname