表二里面有个add_date 字段 请问加载什么位置进行日期筛选了 我 测试了几次都不起作用,以下我现根据你的代码修改的,时间赛选加上没有起到作用select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计] from [Sys_Userinfo] a left join [Ribaobiao_Record] b on a.[id]=b.[luruname_userid] WHERE a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020' AND b.add_date>='2014-10-01' AND b.Add_date<='2014-10-31' group by a.[id],a.[xingming]
条件的逻辑问题,应该这样,给or们加个括号select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计] from [Sys_Userinfo] a left join [Ribaobiao_Record] b on a.[id]=b.[luruname_userid] WHERE (a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020')AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01' group by a.[id],a.[xingming]日期如果有包含时间的话,建议弄成小于下一天
为什么加上时间条件以后 合计为0的数据就不显示了呢 把时间条件去掉就可以了select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计] from [Sys_Userinfo] a left join [Ribaobiao_Record] b on a.[id]=b.[luruname_userid] AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01' WHERE (a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020') group by a.[id],a.[xingming]因为,需要加到左连接上才行
解决了 select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计] from [Sys_Userinfo] a left join [Ribaobiao_Record] b on (a.[id]=b.[luruname_userid] AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01' ) WHERE a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020' group by a.[id],a.[xingming]
表二里面有个add_date 字段 请问加载什么位置进行日期筛选了 我 测试了几次都不起作用,以下我现根据你的代码修改的,时间赛选加上没有起到作用select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计]
from [Sys_Userinfo] a
left join [Ribaobiao_Record] b
on a.[id]=b.[luruname_userid]
WHERE
a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020' AND b.add_date>='2014-10-01' AND b.Add_date<='2014-10-31'
group by a.[id],a.[xingming]
from [Sys_Userinfo] a
left join [Ribaobiao_Record] b
on a.[id]=b.[luruname_userid]
WHERE
(a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020')AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01'
group by a.[id],a.[xingming]日期如果有包含时间的话,建议弄成小于下一天
为什么加上时间条件以后 合计为0的数据就不显示了呢 把时间条件去掉就可以了select a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计]
from [Sys_Userinfo] a
left join [Ribaobiao_Record] b
on a.[id]=b.[luruname_userid] AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01'
WHERE
(a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020')
group by a.[id],a.[xingming]因为,需要加到左连接上才行
from [Sys_Userinfo] a
left join [Ribaobiao_Record] b
on (a.[id]=b.[luruname_userid] AND b.add_date>='2014-10-01' AND b.Add_date<'2014-11-01' )
WHERE a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020'
group by a.[id],a.[xingming]