我需要一个基于排他性或语句的查询，为此我尝试使用这种情况，但我不会在Null的情况下得到结果…
...
and b.[U_Periode] = CASE
when (b.U_Periode= @period) then @period
when (b.U_Periode is NULL ) then null
end
...
不会被捕获的情况是……B.U_Status为空，b. u_period为空。如果var period匹配Value且U_Status = 0或1
让它为我工作的唯一方法是:
...
and
ISNULL( b.[U_Status],'0') = CASE
when (b.U_Status= '1') then '1'
when (isnull( b.U_Status,'0')= '0') then '0'
end
and
ISNULL (b.[U_Periode],'01.01.1901') = CASE
when (b.U_Periode= @period) then @period
when (ISNULL (b.U_Periode,'01.01.1901') = '01.01.1901' ) then '01.01.1901'
end
是否有其他更好的解决方案?致以最亲切的问候奥利弗
好吧…这是我的问题
表1InsID ContractID12 1表二
ID insid Period Status Count
1 1 null null 100
2 1 30.09.2015 1 500
3 2 null null 100
4 2 30.09.2015 1 500
Case '31.08.2015'
in total Value should be 200
in case of '30.09.2015'
the Value should be 1.000
XOR /OR will do the same in this case.
Value case '31.08.2015' = 200
value Case ' 30.09.2015 = 2200
这有点像子查询
left join (
[dbo].[Table2]b
inner join [dbo].[Table 3]K on k.DocEntry = b.DocEntry and CAST( k.U_CSetID as int) >0
)
on b.[U_InsID] in(select... but here I should have an if statement...
- 如果有与Date匹配的结果，则加入此
- 如果不是，则join结果NULL与周期匹配
好的…下面是完整的查询
日期为31.08.2015的表2应该有一个记录，包括B_STATUS = Null, B.Preiode = Null，没有可用的u_period '31.08.2015'和状态的记录…
，日期为30.09.2015有一个匹配U_Period= '30.09.2015'的记录，在这种情况下，U_Period=null的记录不应该影响结果…
Declare @period as varchar(20)= '31-08-2015 00:00:00'
declare @Customer as Varchar(15)='12345'
declare @Contract as varchar(30) = '123'
declare @test as varchar(1) = null
select SUM(cast(K.U_Count as decimal))as counter, K.U_CounterTyp
from [dbo].[Table1] a
left join (
[dbo].[Table2]b
inner join [dbo].[Table 3]K on k.DocEntry = b.DocEntry and CAST( k.U_CSetID as int) >0
)
on b.[U_InsID]=a.[insID] and b.[U_ObjectType]in ('5','1') and
ISNULL( b.[U_Status],'0') = CASE
when (b.U_Status= '1') then '1'
when (isnull( b.U_Status,'0')= '0') then '0'
end
and
ISNULL (b.[U_Periode],'01.01.1901') = CASE
when (b.U_Periode= @period) then @period
when (ISNULL (b.U_Periode,'01.01.1901') = '01.01.1901' ) then '01.01.1901'
end
where a.[customer] =@Customer and a.[Status]='A' and a.[U_ContrCount]='1'
and a.[manufSN] in(
select c.[ManufSN] from [dbo].[Table4] c
inner join [dbo].[OCTR]d on d.[ContractID] = c.[ContractID]
where c.[ManufSN]=a.[manufSN]
and d.[CstmrCode] = a.[customer] and d.[ContractID]=@Contract
)
group by K.U_CounterTyp
必须使用"^"操作数，这是TSQL中的异或操作数
expression ^ expression
表达式必须返回0或1…
愚蠢的例子:
WHERE (name like "stackoverflow") ^ (age > 10)
字体:https://msdn.microsoft.com/en-us/library/ms190277 (v = sql.105) . aspx
更新您的检查
WHERE (CONVERT(VARCHAR(10), a.[U_Periode], 104) = '30.08.2015') != (a.U_Periode IS NULL)
Olay这是我的函数，检查日期结果是否存在。
唯一的问题是，如果我运行数百个句子，性能不是最好的…foreach记录(高达app. 1000)我需要查询每个子表…有很多很多的记录在…
总之，这是函数
CREATE FUNCTION fn_GetInsCounters(@InsId as Varchar(30), @Date as datetime)
returns datetime
as
begin
declare @count as int = 0
declare @Retruns as Datetime
set @count =
(
select count( b.Docentry)
from [dbo].[Table2]b
where b.U_Insid =@InsID
and b.U_Status <> '2'
and b.[U_ObjectType]in ('5','1')
and b.U_Periode=@Date
)
if(@count>0)
begin
set @Retruns = @date
end
else
begin
set @Retruns = '01.01.1901'
end
return @Retruns
end