如何在case语句使用子查询与MSSQL 2008
我需要的,如果else语句来更改以下程序到使用与子查询的select case语句一个查询...如何在case语句使用子查询与MSSQL 2008
if((select COUNT(*) from pseb.dbo.meterattributedetails where [email protected])=0)
select @emf=EMF from pseb.dbo.METERMASTER where [email protected]
else if((select COUNT(*) from pseb.dbo.meterattributedetails where [email protected] and dateadd(day,1,@fromdate)<DateTime)>0)
select top 1 @emf=oldvalue from pseb.dbo.meterattributedetails where [email protected] and dateadd(day,1,@fromdate)<datetime order by DateTime
else
select top 1 @emf=newvalue from pseb.dbo.meterattributedetails where [email protected] and DateTime<@fromdate order by DateTime desc
meterattribute表结构是这样的:
AttributeID AttributeName Oldvalue newvalue DateTime meterid
1 EMF 2.00000 4.00000 2012-07-05 4756
1 EMF 4.00000 6.00000 2012-07-10 4756
1 EMF 6.00000 8.00000 2012-07-15 4756
1 EMF 8.00000 10.00000 2012-07-25 4756
在该表的日期时间
指电动势改变日期,如果我的输入为“2012-07-17”意味着我要取他NEWVALUE针对特定日期,,,在之间7月15日至25日,电动势值为8.00000。
我格式化使用http://www.dpriver.com/pp/sqlformat.htm你的代码 - 迈克尔
IF((SELECT Count(*)
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid) = 0)
SELECT @emf = emf
FROM pseb.dbo.metermaster
WHERE meterid = @meterid
ELSE IF((SELECT Count(*)
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid
AND Dateadd(day, 1, @fromdate) < datetime) > 0)
SELECT TOP 1 @emf = oldvalue
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid
AND Dateadd(day, 1, @fromdate) < datetime
ORDER BY datetime
ELSE
SELECT TOP 1 @emf = newvalue
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid
AND datetime < @fromdate
ORDER BY datetime DESC
@迈克尔 - 感谢链接http://www.dpriver.com/pp/sqlformat.htm。
试试这个(未经测试,当然)
SELECT @emf = CASE
WHEN ((SELECT Count(*)
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid) = 0) THEN
(SELECT emf
FROM pseb.dbo.metermaster
WHERE meterid = @meterid)
WHEN ((SELECT Count(*)
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid
AND Dateadd(day, 1, @fromdate) < datetime) > 0)
THEN (
SELECT
TOP 1 oldvalue
FROM
pseb.dbo.meterattributedetails
WHERE
meterid = @meterid
AND Dateadd(day, 1, @fromdate) < datetime
ORDER
BY datetime)
ELSE (SELECT TOP 1 newvalue
FROM pseb.dbo.meterattributedetails
WHERE meterid = @meterid
AND datetime < @fromdate
ORDER BY datetime DESC)
END
@Saranya:如果这不起作用,怎么样在http://sqlfiddle.com/
好运建立测试用例!
@michael谢谢你的联系..... – 2012-07-26 04:52:59
Thanq u Dan ...它的工作:) – 2012-07-26 04:53:46
与语法检查没有尝试,我认为你在寻找这样的事情:
select @emf = case
when not exists (select 42 from pseb.dbo.meterattributedetails where meterid = @meterid)
then (select emf from pseb.dbo.metermaster where meterid = @meterid)
when exists (select 42 from pseb.dbo.meterattributedetails where meterid = @meterid and
Dateadd(day, 1, @fromdate) < datetime)
then (select top 1 oldvalue from pseb.dbo.meterattributedetails where meterid = @meterid and
Dateadd(day, 1, @fromdate) < datetime order by datetime)
else (select 1 newvalue from pseb.dbo.meterattributedetails where meterid = @meterid and
datetime < @fromdate order by datetime desc)
end
编辑:在一般情况下,如果你获得计数要检查你为零可以取代它合适的EXISTS
具有更好的性能。
你甚至读过这些? – 2012-07-25 14:25:39
编辑:)。希望你现在可以阅读它。 – Narendra 2012-07-25 14:30:35
+1 @AaronBertrand ...我不应该鼓励你 – whytheq 2012-07-25 17:45:51