我有二表,表1 有姓名,单位ID,ID
表2 有单位,ID
表3 有ID 标题, 姓名ID,添加时间
我想找出 表3中,我想找出一个单位2008年6月份有多少条数据,用Count(*)就行了 其中表2和表1是主从表,表3和表1是主从表~
大家即兴发挥,有点急,大家帮下,前几天我还给灾区献了血地,相信有不少朋友也会帮帮我~~现在加一个条件,无论各大单位有没有数据,都要显示出来,没有数据就用0来代替,效果如下单位ID 数量1 0
3 0
8 3 这是我也前的SQL,他的缺点是,当某一单位没有数据时,就不显示了Select U_unit,count(*) as TotalNum from (
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
inner join sys_User b on a.Article_User=b.u_Name
inner join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5) aa
group by U_unit
表2 有单位,ID
表3 有ID 标题, 姓名ID,添加时间
我想找出 表3中,我想找出一个单位2008年6月份有多少条数据,用Count(*)就行了 其中表2和表1是主从表,表3和表1是主从表~
大家即兴发挥,有点急,大家帮下,前几天我还给灾区献了血地,相信有不少朋友也会帮帮我~~现在加一个条件,无论各大单位有没有数据,都要显示出来,没有数据就用0来代替,效果如下单位ID 数量1 0
3 0
8 3 这是我也前的SQL,他的缺点是,当某一单位没有数据时,就不显示了Select U_unit,count(*) as TotalNum from (
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
inner join sys_User b on a.Article_User=b.u_Name
inner join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5) aa
group by U_unit
sqlserver都有帮助!!
用查询分析器就行!!
{
if(Dt.Rows[i][1].ToString() == "")
{
Dt.Rows[i][1] = "0";
}
}
在他删除或者增加人的时候,数据库中的数量也跟着改变......
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
right join sys_User b on a.Article_User=b.u_Name
right join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5 ) aa
group by U_unit
sum(case when U_unit is null then 0 else 1 end) as TotalNum from (
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
left join sys_User b on a.Article_User=b.u_Name
left join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5
) aa
group by U_unit
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
inner join sys_User b on a.Article_User=b.u_Name
inner join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5) aa
group by U_unit
改成sum(isnull(Article_ID,0))
sum(case when U_unit is null then 0 else 1 end) as TotalNum from (
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
left join sys_User b on a.Article_User=b.u_Name
left join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5
) aa
group by U_unit 14楼帖错了,如果统计U_unit,应该把sys_jggl c表做主表,其他表 left join sys_jggl表
insert into @a select 1
insert into @a select 2declare @b table (id int)
insert into @b select 1select a.id,
sum(case when b.id is null then 0 else 1 end) as con
from @a a left join @b b on a.id=b.id
group by a.idid con
1 1
2 0类似这样写,把主表放前面,left
Select U_unit,count(*) as TotalNum from (
Select a.Article_ID,c.j_id as U_unit from article a
right join sys_User b on a.Article_User=b.u_Name
right join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5
) aa
group by U_unit
inner join sys_jggl c on b.u_unit=c.j_id
改成
right join sys_jggl c on b.u_unit=c.j_id
试试,其它的不变
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
inner join sys_User b on a.Article_User=b.u_Name
inner join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5 ) aa
group by U_unit
union
select j_id as U_unit,TotalNum=0 from sys_jggl具体是用Union还是Union All楼主查一下。
(
Select a.Article_ID,c.j_id as U_unit,a.Article_TimeDate
from article a
FULL join sys_User b on a.Article_User=b.u_Name
FULL join sys_jggl c on b.u_unit=c.j_id
where year(a.Article_TimeDate )=2008 and month(a.Article_TimeDate )=5 ) aa
group by U_unit
oracle 的语句:
select a.* , nvl(d.num, 0) from 单位表 a left join (select a.id id, count(a.id) num from 文件表 c left join 用户表 b on c.用户id =b.id left join 单位表 a on a.id= b.单位id where 时间 < to_date('2008-2-1', 'yyyy-mm-dd')
group by a.id) d on a.id = d.id
create table #table1
(
ID int,
[name] varchar(100),
unitID int
)create table #table2
(
ID int,
U_unit varchar(100)
)create table #table3
(
ID int,
Article varchar(100),
nameID varchar(100),
Article_TimeDate datetime
)
--init table1
insert into #table1(ID,[name],unitID)
values(1,'name1',1)
insert into #table1(ID,[name],unitID)
values(2,'name2',2)
insert into #table1(ID,[name],unitID)
values(3,'name3',3)
insert into #table1(ID,[name],unitID)
values(4,'name3',1)
select * from #table1
--init table2
insert into #table2(ID,U_unit)
values(1,'U_unit1')
insert into #table2(ID,U_unit)
values(2,'U_unit2')
insert into #table2(ID,U_unit)
values(3,'U_unit3')
select * from #table2
--init table3
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(1,'Article1',1,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(2,'Article2',1,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(3,'Article3',3,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(4,'Article4',3,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(5,'Article5',3,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(6,'Article6',4,'2008/05/05')
insert into #table3(ID,Article,nameID,Article_TimeDate)
values(7,'Article7',4,'2008/05/05')
select * from #table3
--我的答案
select unitID as '单位ID' ,count(Article) as '数量' from #table1 left join
(select * from #table3
where datediff(month,#table3.Article_TimeDate,'2008/05/01')= 0
)as temptable3
on temptable3.nameID = #table1.ID
group by unitID
select #table2.ID as '单位ID' ,count(Article) as '数量' from #table2 left join
(select #table3.*,unitID from #table3 left join #table1
on #table3.nameID = #table1.ID
where datediff(month,#table3.Article_TimeDate,'2008/05/01')= 0
)as temptable3
on temptable3.unitID = #table2.ID
group by #table2.ID