where后面加判断

get_D 2017-10-20 03:16:09
select [AttendanceCollectId]=newid()
,[MachineId]=[Machine].MachineId
,[MachineCode]=cardinfo.clockid
,[CardId]=[Card].CardId
,[CardCode]=[Card].CardNo
,[EmployeeName]=[Employee].CnName
,[EmployeeCode]=[Employee].Code
,[EmployeeId]=[Employee].EmployeeId
,[ownerId]=[Employee].EmployeeId
,[DepartmentName]=[Department].Name
,[DepartmentId]=[Department].DepartmentId
,[Date] = cardinfo.date+' '+cardinfo.time
,[Time] = cardinfo.time
,[IsManual] = 0
,[Flag] = '1'
,ApproveResultId='OperatorResult_001'
,StateId ='PlanState_003'
,IsForAttendance='1'
--,AttendanceCollectLogId=@AttCollectLogId
from
(select * from (
select right(CONVERT(varchar(16),DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) time,
CONVERT(varchar(100),a.KqDate, 23) date,
'5'+cast(CardId as varchar) carid,
right(CONVERT(varchar(16), DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) as checktime,
a.DevID as clockid,
b.employeeid
from [192.168.3.101

].[HsSmartHR170921].dbo.KQ_KqData a,employee b
where b.code=case when @[DepartmentId]=‘NB001000’then '50'+cast(CardId as varchar) else '5'+cast(CardId as varchar) end

and a.EmpSysID is not null ) as info
where not exists (
select top 1 * from
(select [CardCode],[MachineCode],[DateTime]=CONVERT(varchar(100), Date, 23)+' '+CONVERT(varchar(100), Time, 24),employeeid
from [AttendanceCollect])
as collect
where collect.employeeid=info.employeeid
and substring(CONVERT(varchar(100), collect.[DateTime], 20),1,16)=info.date+' '+info.time)
) as cardinfo
left join [Machine] on [Machine].Code = cardinfo.clockid
left join [Card] on [Card].employeeid = cardinfo.employeeid
and [Card].UseTypeId='UseType_001' and ([card].RevokeDate is null or [card].RevokeDate>=GETDATE()-2)
left join [Employee] on [Employee].employeeid= [Card].EmployeeId
left join [Department] on [Department].DepartmentId=[Employee].DepartmentId
where [Card].CardNo is not null and cardinfo.date >='2017-03-01'
...全文
292 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhouyuehai1978 2017-10-24
  • 打赏
  • 举报
 回复
@DepartmentId这个变量是uniqueidentifier类型的，你的@DepartmentId='NB001000' 这句是不是要转换一下类型
zhouyuehai1978 2017-10-24
  • 打赏
  • 举报
 回复
看一下这个例子 http://www.cnblogs.com/chyg/p/5404591.html
get_D 2017-10-20
  • 打赏
  • 举报
 回复
declare @AttCollectLogId uniqueidentifier declare @DepartmentId uniqueidentifier set @AttCollectLogId=newid() --1、导入刷卡数据 INSERT INTO [AttendanceCollect] ([AttendanceCollectId] ,[MachineId] ,[MachineCode] ,[CardId] ,[CardCode] ,[EmployeeName] ,[EmployeeCode] ,[EmployeeId] ,[OwnerId] ,[DepartmentName] ,[DepartmentId] ,[Date] ,[Time] ,[IsManual] ,[Flag] ,ApproveResultId ,[StateId] ,IsForAttendance ,AttendanceCollectLogId) select [AttendanceCollectId]=newid() ,[MachineId]=[Machine].MachineId ,[MachineCode]=cardinfo.clockid ,[CardId]=[Card].CardId ,[CardCode]=[Card].CardNo ,[EmployeeName]=[Employee].CnName ,[EmployeeCode]=[Employee].Code ,[EmployeeId]=[Employee].EmployeeId ,[ownerId]=[Employee].EmployeeId ,[DepartmentName]=[Department].Name ,[DepartmentId]=[Department].DepartmentId ,[Date] = cardinfo.date+' '+cardinfo.time ,[Time] = cardinfo.time ,[IsManual] = 0 ,[Flag] = '1' ,ApproveResultId='OperatorResult_001' ,StateId ='PlanState_003' ,IsForAttendance='1' ,AttendanceCollectLogId=@AttCollectLogId from (select * from ( select right(CONVERT(varchar(16),DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) time, CONVERT(varchar(100),a.KqDate, 23) date, '5'+cast(CardId as varchar) carid, right(CONVERT(varchar(16), DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) as checktime, a.DevID as clockid, b.employeeid from [192.168.3.101].[HsSmartHR170921].dbo.KQ_KqData a,employee b where b.code=case when @DepartmentId='NB001000' then '50'+cast(CardId as varchar) else '5'+cast(CardId as varchar) end and a.EmpSysID is not null ) as info where not exists ( select top 1 * from (select [CardCode],[MachineCode],[DateTime]=CONVERT(varchar(100), Date, 23)+' '+CONVERT(varchar(100), Time, 24),employeeid from [AttendanceCollect]) as collect where collect.employeeid=info.employeeid and substring(CONVERT(varchar(100), collect.[DateTime], 20),1,16)=info.date+' '+info.time) ) as cardinfo left join [Machine] on [Machine].Code = cardinfo.clockid left join [Card] on [Card].employeeid = cardinfo.employeeid and [Card].UseTypeId='UseType_001' and ([card].RevokeDate is null or [card].RevokeDate>=GETDATE()-2) left join [Employee] on [Employee].employeeid= [Card].EmployeeId left join [Department] on [Department].DepartmentId=[Employee].DepartmentId where [Card].CardNo is not null and cardinfo.date >='2017-03-01' --2写刷卡日志 Insert into AttendanceCollectlog( IsManual, AttendanceCollectlogId, MachineTypeId, FileSize, CollectDate, CollectEmployeeId, CollectOperationDate, CollectUserId, CreateDate, LastModifieddate, Flag, Remark ) select IsManual=1, AttendanceCollectlogId=@AttCollectLogId, MachineTypeId='90DE5165-151A-46A0-8FB2-C3C77DC82B05', FileSize=0, CollectDate=getdate(), CollectEmployeeId='7F9A68EC-1C41-48DB-9D67-FED82A4EECBC', CollectOperationDate=getdate(), CollectUserId='b7e83f6c-f854-44d9-a9eb-de0878d5d82c', CreateDate=getdate(), LastModifieddate=getdate(), Flag=1, Remark='同步时间:'+ convert(varchar(100),getdate(),120) 这是全部的 但是又报这个错 已以用户 NT AUTHORITY\SYSTEM 的身份执行。 将字符串转换为 uniqueidentifier 时失败。 [SQLSTATE 42000] (错误 8169). 该步骤失败。
吉普赛的歌 版主 2017-10-20
  • 打赏
  • 举报
 回复
引用 11 楼 get_D 的回复:
[quote=引用 10 楼 yenange 的回复:] [quote=引用 9 楼 get_D 的回复:] [quote=引用 7 楼 yenange 的回复:] 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的[/quote] 报什么错， 你要贴个图出来呀[/quote]
引用 10 楼 yenange 的回复:
[quote=引用 9 楼 get_D 的回复:] [quote=引用 7 楼 yenange 的回复:] 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的[/quote] 报什么错， 你要贴个图出来呀[/quote] 已以用户 NT AUTHORITY\SYSTEM 的身份执行。 必须声明标量变量 "@DepartmentId"。 [SQLSTATE 42000] (错误 137) ')' 附近有语法错误。 [SQLSTATE 42000] (错误 102). 该步骤失败 这个错误提示[/quote] 这提示得还不清楚？ 你没有用 declare 定义这个变量啊
get_D 2017-10-20
  • 打赏
  • 举报
 回复
引用 10 楼 yenange 的回复:
[quote=引用 9 楼 get_D 的回复:] [quote=引用 7 楼 yenange 的回复:] 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的[/quote] 报什么错， 你要贴个图出来呀[/quote]
引用 10 楼 yenange 的回复:
[quote=引用 9 楼 get_D 的回复:] [quote=引用 7 楼 yenange 的回复:] 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的[/quote] 报什么错， 你要贴个图出来呀[/quote] 已以用户 NT AUTHORITY\SYSTEM 的身份执行。 必须声明标量变量 "@DepartmentId"。 [SQLSTATE 42000] (错误 137) ')' 附近有语法错误。 [SQLSTATE 42000] (错误 102). 该步骤失败 这个错误提示
吉普赛的歌 版主 2017-10-20
  • 打赏
  • 举报
 回复
引用 9 楼 get_D 的回复:
[quote=引用 7 楼 yenange 的回复:] 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的[/quote] 报什么错， 你要贴个图出来呀
get_D 2017-10-20
  • 打赏
  • 举报
 回复
引用 7 楼 yenange 的回复:
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
改过了，还是不行的
get_D 2017-10-20
  • 打赏
  • 举报
 回复
引用 4 楼 sinat_28984567 的回复:
@DepartmentId='NB001000'
SELECT  [AttendanceCollectId] = NEWID() ,
        [MachineId] = [Machine].MachineId ,
        [MachineCode] = cardinfo.clockid ,
        [CardId] = [Card].CardId ,
        [CardCode] = [Card].CardNo ,
        [EmployeeName] = [Employee].CnName ,
        [EmployeeCode] = [Employee].Code ,
        [EmployeeId] = [Employee].EmployeeId ,
        [ownerId] = [Employee].EmployeeId ,
        [DepartmentName] = [Department].Name ,
        [DepartmentId] = [Department].DepartmentId ,
        [Date] = cardinfo.date + ' ' + cardinfo.time ,
        [Time] = cardinfo.time ,
        [IsManual] = 0 ,
        [Flag] = '1' ,
        ApproveResultId = 'OperatorResult_001' ,
        StateId = 'PlanState_003' ,
        IsForAttendance = '1'  
       --,AttendanceCollectLogId=@AttCollectLogId
FROM    ( SELECT    *
          FROM      ( SELECT    RIGHT(CONVERT(VARCHAR(16), DATEADD(ss,
                                                              a.KqTime,
                                                              CAST('00:00:00' AS DATETIME)), 121),
                                      5) time ,
                                CONVERT(VARCHAR(100), a.KqDate, 23) date ,
                                '5' + CAST(CardId AS VARCHAR) carid ,
                                RIGHT(CONVERT(VARCHAR(16), DATEADD(ss,
                                                              a.KqTime,
                                                              CAST('00:00:00' AS DATETIME)), 121),
                                      5) AS checktime ,
                                a.DevID AS clockid ,
                                b.employeeid
                      FROM      [192.168.3.101 

].[HsSmartHR170921].dbo.KQ_KqData a ,
                                employee b
                      WHERE     b.code = CASE WHEN @DepartmentId = 'NB001000'
                                              THEN '50'
                                                   + CAST(CardId AS VARCHAR)
                                              ELSE '5'
                                                   + CAST(CardId AS VARCHAR)
                                         END
                                AND a.EmpSysID IS NOT NULL
                    ) AS info
          WHERE     NOT EXISTS ( SELECT TOP 1
                                        *
                                 FROM   ( SELECT    [CardCode] ,
                                                    [MachineCode] ,
                                                    [DateTime] = CONVERT(VARCHAR(100), Date, 23)
                                                    + ' '
                                                    + CONVERT(VARCHAR(100), Time, 24) ,
                                                    employeeid
                                          FROM      [AttendanceCollect]
                                        ) AS collect
                                 WHERE  collect.employeeid = info.employeeid
                                        AND SUBSTRING(CONVERT(VARCHAR(100), collect.[DateTime], 20),
                                                      1, 16) = info.date + ' '
                                        + info.time )
        ) AS cardinfo
        LEFT JOIN [Machine] ON [Machine].Code = cardinfo.clockid
        LEFT JOIN [Card] ON [Card].employeeid = cardinfo.employeeid
                            AND [Card].UseTypeId = 'UseType_001'
                            AND ( [card].RevokeDate IS NULL
                                  OR [card].RevokeDate >= GETDATE() - 2
                                )
        LEFT JOIN [Employee] ON [Employee].employeeid = [Card].EmployeeId
        LEFT JOIN [Department] ON [Department].DepartmentId = [Employee].DepartmentId
WHERE   [Card].CardNo IS NOT NULL
        AND cardinfo.date >= '2017-03-01'
不行啊！！报这个错！！已以用户 NT AUTHORITY\SYSTEM 的身份执行。 必须声明标量变量 "@DepartmentId"。 [SQLSTATE 42000] (错误 137) ')' 附近有语法错误。 [SQLSTATE 42000] (错误 102). 该步骤失败。
吉普赛的歌 版主 2017-10-20
  • 打赏
  • 举报
 回复 
--红色部分修改如下：
--1. @[DepartmentId]=> @DepartmentId
--2. 中文 ‘ => '
WHERE b.code = 
CASE WHEN @DepartmentId = 'NB001000' THEN '50' + CAST(CardId AS VARCHAR)
     ELSE '5' + CAST(CardId AS VARCHAR) END
顺势而为1 2017-10-20
  • 打赏
  • 举报
 回复
@DepartmentId 为什么要加中括号 ? 


where b.code=case when @DepartmentId=‘NB001000’then '50'+cast(CardId as varchar) else '5'+cast(CardId as varchar) end
get_D 2017-10-20
  • 打赏
  • 举报
 回复
引用 2 楼 get_D 的回复:
引用 1 楼 z10843087 的回复:
没说明想做什么啊？？
红色的地方报错，怎么解决？
where b.code=case when @[DepartmentId]=‘NB001000’then '50'+cast(CardId as varchar) else '5'+cast(CardId as varchar) end 这个地方
二月十六 版主 2017-10-20
  • 打赏
  • 举报
 回复 
@DepartmentId='NB001000'
SELECT  [AttendanceCollectId] = NEWID() ,
        [MachineId] = [Machine].MachineId ,
        [MachineCode] = cardinfo.clockid ,
        [CardId] = [Card].CardId ,
        [CardCode] = [Card].CardNo ,
        [EmployeeName] = [Employee].CnName ,
        [EmployeeCode] = [Employee].Code ,
        [EmployeeId] = [Employee].EmployeeId ,
        [ownerId] = [Employee].EmployeeId ,
        [DepartmentName] = [Department].Name ,
        [DepartmentId] = [Department].DepartmentId ,
        [Date] = cardinfo.date + ' ' + cardinfo.time ,
        [Time] = cardinfo.time ,
        [IsManual] = 0 ,
        [Flag] = '1' ,
        ApproveResultId = 'OperatorResult_001' ,
        StateId = 'PlanState_003' ,
        IsForAttendance = '1'  
       --,AttendanceCollectLogId=@AttCollectLogId
FROM    ( SELECT    *
          FROM      ( SELECT    RIGHT(CONVERT(VARCHAR(16), DATEADD(ss,
                                                              a.KqTime,
                                                              CAST('00:00:00' AS DATETIME)), 121),
                                      5) time ,
                                CONVERT(VARCHAR(100), a.KqDate, 23) date ,
                                '5' + CAST(CardId AS VARCHAR) carid ,
                                RIGHT(CONVERT(VARCHAR(16), DATEADD(ss,
                                                              a.KqTime,
                                                              CAST('00:00:00' AS DATETIME)), 121),
                                      5) AS checktime ,
                                a.DevID AS clockid ,
                                b.employeeid
                      FROM      [192.168.3.101 

].[HsSmartHR170921].dbo.KQ_KqData a ,
                                employee b
                      WHERE     b.code = CASE WHEN @DepartmentId = 'NB001000'
                                              THEN '50'
                                                   + CAST(CardId AS VARCHAR)
                                              ELSE '5'
                                                   + CAST(CardId AS VARCHAR)
                                         END
                                AND a.EmpSysID IS NOT NULL
                    ) AS info
          WHERE     NOT EXISTS ( SELECT TOP 1
                                        *
                                 FROM   ( SELECT    [CardCode] ,
                                                    [MachineCode] ,
                                                    [DateTime] = CONVERT(VARCHAR(100), Date, 23)
                                                    + ' '
                                                    + CONVERT(VARCHAR(100), Time, 24) ,
                                                    employeeid
                                          FROM      [AttendanceCollect]
                                        ) AS collect
                                 WHERE  collect.employeeid = info.employeeid
                                        AND SUBSTRING(CONVERT(VARCHAR(100), collect.[DateTime], 20),
                                                      1, 16) = info.date + ' '
                                        + info.time )
        ) AS cardinfo
        LEFT JOIN [Machine] ON [Machine].Code = cardinfo.clockid
        LEFT JOIN [Card] ON [Card].employeeid = cardinfo.employeeid
                            AND [Card].UseTypeId = 'UseType_001'
                            AND ( [card].RevokeDate IS NULL
                                  OR [card].RevokeDate >= GETDATE() - 2
                                )
        LEFT JOIN [Employee] ON [Employee].employeeid = [Card].EmployeeId
        LEFT JOIN [Department] ON [Department].DepartmentId = [Employee].DepartmentId
WHERE   [Card].CardNo IS NOT NULL
        AND cardinfo.date >= '2017-03-01'
OwenZeng_DBA 2017-10-20
  • 打赏
  • 举报
 回复
手机看不到红色部分
get_D 2017-10-20
  • 打赏
  • 举报
 回复
引用 1 楼 z10843087 的回复:
没说明想做什么啊？？
红色的地方报错，怎么解决？
OwenZeng_DBA 2017-10-20
  • 打赏
  • 举报
 回复
没说明想做什么啊？？
SQL where后面加条件判断
DATE_FORMAT(now(), ‘%H:%i’) 输出16:16。当系统时间为8:00到20:00 查询条件为 0 反之 则为 1。它用于按照指定的格式对日期或时间值进行格式化输出。条件判断则用case…%H：24小时制的小时，两位数字。%i：分钟，两位数字。
Oracle WHERE判断语句后跟括号 or
参考了下以下的网址： 关于Oracle WHERE后面跟判断语句问题 - 开发者知识库q 工作遇到一个问题：如果传入的分区变量 P_REPORT_TYPE 是’ALL‘时，那么就要将所有的公司编码 COMPANY_CODE 输出，但如果不是'ALL'，那么就需要根据传入的另一个变量 P_COMPANY_CODE 来只输出这个公司编码(COMPANY_CODE = P_COMPANY_CODE ) 找了半天发现，有个where ( or )这个方法，根据上面的参考总结了下： where 条件1 .
oracle在sql语句where后加条件判断
加粗样式 有两种方式： 第一种，使用decode函数 SELECT * FROM prj_project pp WHERE (case when (case when pp.business_type = ‘LEASE’ then 1 else 0 end) = 1 then pp.business_type else pp.document_type end) = (case when (case when pp.business_type = ‘LEASE’ then 1 else 0 end) = 1
sql里在where中添加条件判断语句
SQL中where添加条件判断
mysql where后面if_mysql查询语句where后面加if判断
mysql查询语句where后面加if判断 说明：WHERE IF(条件, true执行条件, false执行条件 )SELECT * FROM book WHERE IF(price > 120, btypeid=10, btypeid=11); 以上sql查询语句，意思是查price字段大于120，当price大于120为真时，再找btypeid等于10的行记录，当price大...
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章