where后面加判断

2017-10-20 03:16:09 360 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ESP32 Arduino学习篇 (一) 引脚和串口的基本使用简单来讲arduino就是通过源代码控制开发板的信号输入输出从而实现对一些电子元件的控制，例如LED灯，蜂鸣器，按键arduino程序首先来讲arduino程序是基于C语言，而实现类似对于LED灯和蜂鸣器的简单控制只需简单C语言知识即可实现。arduino程序是基于函数的，而在arduino的库有很多函数，我们只需要调用即可。arduino的程序主体是**setup()函数和loop()**函数以及其它在使用过程中需要构建的函数：setup()函数。阅读详情

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'

...全文

当兵心理测试软件,当兵心理测试题及答案心理测试是一种比较先进的测试方法，它是指通过一系列手段，将人的某些心理特征数量化，来衡量个体心理因素水平和个体心理差异差异的一种科学测量方法。小编为你整理了当兵心理测试题及答案，希望对你有所参考帮助。征兵心理测试题(1代表是2代表否)1、我的胃口很好。12、我早上起来的时候，多半觉得睡眠充足，头脑清醒13、我喜欢看报纸上的犯罪新闻。14、有时我常感到困难重重，无法克服。25、我时常遇见一些所谓的专... 阅读详情

get_D

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

吉普赛的歌版主 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

打赏
举报

没说明想做什么啊？？

where后面加判断

相关推荐