语句:
DECLARE @A001 FLOAT,@A002 FLOATSELECT
@A001=(CASE A01 WHEN 10 THEN A02 END),
@A002=(CASE A01 WHEN 40 THEN A02 END)
FROM (
SELECT '' AS A01,''A02 UNION ALL
SELECT 10,51 UNION ALL
SELECT 20,52 UNION ALL
SELECT 30,53 UNION ALL
SELECT 40,54) BSELECT @A001
SELECT @A002结果:
@A001=NULL,
@A002=54
问题:
为什么@A001没有被赋值?多行数据时总是最后一条被赋值,为什么?怎么写才能都赋值?
DECLARE @A001 FLOAT,@A002 FLOATSELECT
@A001=(CASE A01 WHEN 10 THEN A02 END),
@A002=(CASE A01 WHEN 40 THEN A02 END)
FROM (
SELECT '' AS A01,''A02 UNION ALL
SELECT 10,51 UNION ALL
SELECT 20,52 UNION ALL
SELECT 30,53 UNION ALL
SELECT 40,54) BSELECT @A001
SELECT @A002结果:
@A001=NULL,
@A002=54
问题:
为什么@A001没有被赋值?多行数据时总是最后一条被赋值,为什么?怎么写才能都赋值?
DECLARE @A001 FLOAT,@A002 FLOATSELECT
@A001=max(CASE B.A01 WHEN 10 THEN A02 END),
@A002=max(CASE B.A01 WHEN 40 THEN A02 END)
FROM (
SELECT '' AS A01,'' A02 UNION ALL
SELECT 10,51 UNION ALL
SELECT 20,52 UNION ALL
SELECT 30,53 UNION ALL
SELECT 40,54) BSELECT @A001
SELECT @A002这样就可以了
DECLARE @A001 FLOAT,@A002 FLOATSELECT
@A001=sum((CASE A01 WHEN 10 THEN A02 else 0 END)),
@A002=sum((CASE A01 WHEN 40 THEN A02 else 0 END))
FROM (
SELECT '' AS A01,''A02 UNION ALL
SELECT 10,51 UNION ALL
SELECT 20,52 UNION ALL
SELECT 30,53 UNION ALL
SELECT 40,54) B
SELECT @A001
SELECT @A002--无列名--
51
--无列名--
54
你第一个Case 判断取得的值是
--无列名--
NULL
51
NULL
NULL
NULL