各位我有两张表 a (id,title,important) , b (id,uid,content,newdate);
a table 数据如下:
1 test1 1
2 test2 0
3 test3 1
4 test1 0b table 数据如下:
1 jason content1 2009/1/1
1 ally content1 2009/10/1
1 jason content2 2009/10/4
2 alan content1 2009/2/1
2 tony content1 2009/12/25
2 jason content1 2009/11/29我想要得到这样一个结果:统计b表中相同的id有多少个,同时把a表中的title和important两个字段也显示出来.想要的是这样一个效果:
id title important qty
1 test1 1 3
2 test2 0 3如何写这个SQL语句?????
a table 数据如下:
1 test1 1
2 test2 0
3 test3 1
4 test1 0b table 数据如下:
1 jason content1 2009/1/1
1 ally content1 2009/10/1
1 jason content2 2009/10/4
2 alan content1 2009/2/1
2 tony content1 2009/12/25
2 jason content1 2009/11/29我想要得到这样一个结果:统计b表中相同的id有多少个,同时把a表中的title和important两个字段也显示出来.想要的是这样一个效果:
id title important qty
1 test1 1 3
2 test2 0 3如何写这个SQL语句?????
select c.id,a.title,a.important,c.qty
from
(
select id,count(*) as qty
from b
group by id
) c left join a on c.id=a.id
group by a.id,a.title,a.important
加点 。不短了
--> Author : Wufeng4552
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (id int,title nvarchar(10),important int)
insert into [ta]
select 1,'test1',1 union all
select 2,'test2',0 union all
select 3,'test3',1 union all
select 4,'test1',0
--> Title : Generating test data [tb]
--> Author : Wufeng4552
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,uid nvarchar(10),content nvarchar(16),newdate datetime)
insert into [tb]
select 1,'jason','content1','2009/1/1' union all
select 1,'ally','content1','2009/10/1' union all
select 1,'jason','content2','2009/10/4' union all
select 2,'alan','content1','2009/2/1' union all
select 2,'tony','content1','2009/12/25' union all
select 2,'jason','content1','2009/11/29'
select a.*,
b.qty
from ta a,
(select count(*)qty,ID from tb group by ID)b
where a.id=b.id
/*
id title important qty
----------- ---------- ----------- -----------
1 test1 1 3
2 test2 0 3(2 個資料列受到影響)
*/
a.*,count(b.id) as num
from
a join b
on
a.id=b.id
group by
a.id,a.title,a.important