现有
表Ribaobiao2 (日志表) ：
Id UserId add_date
1 20 2014-12-01
2 20 2014-12-02
3 21 2014-12-01
4 22 2014-12-02
表Sys_Userinfo (用户表) ：
UserId Username
20 张三
21 李四
22 王二
怎么通过用户信息去日志表查询此人的考勤信息得到下面这样一个月报表
姓名 1 2 3 4 5 ---------------------------31到月末最后一天
张三 出勤 出勤 出勤 出勤 出勤
李四 出勤 缺勤 出勤 出勤 出勤
王二 缺勤 缺勤 缺勤 出勤 出勤
13 个解决方案
#1
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<32
SET @SQL=@SQL+'FROM Ribaobiao2 GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'
EXEC(@SQL)
#2
请问这个条件怎么加到 Sys_Userinfo T1 表的筛选上
t1.UserDept='1014' OR t1.UserDept='1015' OR t1.UserDept='1016' OR t1.UserDept='1017' OR t1.UserDept='1019' OR t1.UserDept='1020'
还有就是在Ribaobiao2 表上加上 月份的赛选条件呢
#3
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-11-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CAST(@MONTH AS VARCHAR(30))+'''AND add_date<'''+CAST(DATEADD(MONTH,1,@MONTH)AS VARCHAR(30))
+''' GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#4
大侠，为什么我把 add_date 换成 bfdate 就不好使了呢，bfdate（nvarchar 50） 是这个类型 数据格式是 2014-12-01
add_date是 （datetime 8） 这类型 时间格式是 2014-12-01 8:34
请大侠指教
#5
如果是这样，可以这样，既然你只查一个月份，我把调优了一下
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01然后，建议日期还是存日期格式吧，精确到3.33毫秒，而且只需要8个字节的存储空间
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#6
Ky_min 的写法已经很好了，但你也可以这样写：
create table Sys_userinfo
(
Username varchar(10),
add_date varchar(10)
)
insert into Sys_userinfo
select '张三','2014-12-01' union all
select '张三','2014-12-02' union all
select '李四','2014-12-01' union all
select '王二','2014-12-02'
select Username,
sum(case when datepart(dd,add_date)=1 then 1
-- 当然，你可以从这里排除节假日的判断 ，when 节假日 then -1 *
else 0 end) as '1',
sum(case when datepart(dd,add_date)=2 then 1 else 0 end) as '2',
sum(case when datepart(dd,add_date)=3 then 1 else 0 end) as '3'
from Sys_Userinfo as u
join Ribaobiao2 as r on r.UserId=u.UserId
group by Username
-- 1 表示出勤，0 表示缺勤
#7
嘿嘿，我又有个要求！我自己都不好意思说了 能不能在每个人的最后面 如30号的后面加上个 合计缺勤天数 合计出勤天数
#8
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01没什么关系，你试下
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(SUM(1),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#9
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#10
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#11
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#12
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01试这个
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(COUNT(DISTINCT add_date),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#13
该回复于2015-04-22 13:42:53被管理员删除
#1
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<32
SET @SQL=@SQL+'FROM Ribaobiao2 GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'
EXEC(@SQL)
#2
请问这个条件怎么加到 Sys_Userinfo T1 表的筛选上
t1.UserDept='1014' OR t1.UserDept='1015' OR t1.UserDept='1016' OR t1.UserDept='1017' OR t1.UserDept='1019' OR t1.UserDept='1020'
还有就是在Ribaobiao2 表上加上 月份的赛选条件呢
#3
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-11-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CAST(@MONTH AS VARCHAR(30))+'''AND add_date<'''+CAST(DATEADD(MONTH,1,@MONTH)AS VARCHAR(30))
+''' GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#4
大侠，为什么我把 add_date 换成 bfdate 就不好使了呢，bfdate（nvarchar 50） 是这个类型 数据格式是 2014-12-01
add_date是 （datetime 8） 这类型 时间格式是 2014-12-01 8:34
请大侠指教
#5
如果是这样，可以这样，既然你只查一个月份，我把调优了一下
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01然后，建议日期还是存日期格式吧，精确到3.33毫秒，而且只需要8个字节的存储空间
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#6
Ky_min 的写法已经很好了，但你也可以这样写：
create table Sys_userinfo
(
Username varchar(10),
add_date varchar(10)
)
insert into Sys_userinfo
select '张三','2014-12-01' union all
select '张三','2014-12-02' union all
select '李四','2014-12-01' union all
select '王二','2014-12-02'
select Username,
sum(case when datepart(dd,add_date)=1 then 1
-- 当然，你可以从这里排除节假日的判断 ，when 节假日 then -1 *
else 0 end) as '1',
sum(case when datepart(dd,add_date)=2 then 1 else 0 end) as '2',
sum(case when datepart(dd,add_date)=3 then 1 else 0 end) as '3'
from Sys_Userinfo as u
join Ribaobiao2 as r on r.UserId=u.UserId
group by Username
-- 1 表示出勤，0 表示缺勤
#7
嘿嘿，我又有个要求！我自己都不好意思说了 能不能在每个人的最后面 如30号的后面加上个 合计缺勤天数 合计出勤天数
#8
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01没什么关系，你试下
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(SUM(1),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#9
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#10
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#11
您给我的代码我执行后回出现下面图片的情况 30天会出现考勤数31天 缺勤会出现 -1
#12
--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01试这个
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(COUNT(DISTINCT add_date),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)
#13
该回复于2015-04-22 13:42:53被管理员删除