同志们我问个问题,我有两个表,分别是排班表和记录表,然后我要查询某月每一天的旷工次数,即排班表里排班了,但是记录表里没有这个人的记录,就在次数上+1,
CREATE proc [dbo].[ooo]
@year int,
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp1(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=
case
when @month<12
then
datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year as varchar)+'-'+cast((@month+1) as varchar)+'-01',120))
else
datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year+1 as varchar)+'-'+cast(1 as varchar)+'-01',120))
end
while(@num<=@count)
begin
insert into #temp1 select convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
select a.date,COALESCE(count(b.pid),0)[count] from #temp1 a,Arg_Class b
full join record c on b.pid=c.pid where
b.pid not in (select pid from record where datediff (day,inwell,a.date)=0)
group by a.date order by a.date
end我是这么写的,我现在能统计出来次数,但是比方说几天所有人都上班,次数为0,我查出来的记录就自动忽略了,这是怎么回事啊
CREATE proc [dbo].[ooo]
@year int,
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp1(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=
case
when @month<12
then
datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year as varchar)+'-'+cast((@month+1) as varchar)+'-01',120))
else
datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year+1 as varchar)+'-'+cast(1 as varchar)+'-01',120))
end
while(@num<=@count)
begin
insert into #temp1 select convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
select a.date,COALESCE(count(b.pid),0)[count] from #temp1 a,Arg_Class b
full join record c on b.pid=c.pid where
b.pid not in (select pid from record where datediff (day,inwell,a.date)=0)
group by a.date order by a.date
end我是这么写的,我现在能统计出来次数,但是比方说几天所有人都上班,次数为0,我查出来的记录就自动忽略了,这是怎么回事啊
]
from #temp1 a, Arg_Class b
left join record c on b.pid=c.pid
where b.pid not in
(select pid from record where datediff(day,inwell,a.date)=0)
group by a.date
order by a.date
你这个语句过滤掉了你说的数据
as
(
select a.date,b.*
from
#temp1 a left join Arg_Class b on a.date=b.inwell
)
select a.time,COUNT(b.pid)
from
cte a
left join record b on b.pid=c.pid
group by a.date
order by a.date
你的时间表要和排班表进行左连接,然后在左连接你的记录表,这样的话才方便你的统计
b.pid not in (select pid from record where datediff (day,record.m_time,a.m_time)=0)
你的这句话过滤了很多数据
as
(
select a.date,b.*
from
#temp1 a left join Arg_Class b on a.date=b.settime
)
select a.time,COUNT(b.pid)
from
cte a
left join record b on a.date=b.inWell and b.pid=c.pid
group by a.date
order by a.date
无法绑定由多个部分组成的标识符 "c.pid"。