DECLARE @T TABLE (商品编号 VARCHAR(10), 会员价 NUMERIC(6,1), 零售价 NUMERIC(6,1))
INSERT INTO @T
SELECT '100', 0, 5.0
UNION ALL SELECT '101', 2.8, 3.0
UNION ALL SELECT '200', 3.2, 3.5
UNION ALL SELECT '300', 0 , 5.0
SELECT 商品编号,(CASE 会员价 WHEN 0 THEN (SELECT 零售价)
ELSE 会员价 END) AS 價格 FROM @T WHERE 商品编号='200'SELECT 商品编号,(CASE 会员价 WHEN 0 THEN (SELECT 零售价)
ELSE 会员价 END) AS 價格 FROM @T WHERE 商品编号='100'
INSERT INTO @T
SELECT '100', 0, 5.0
UNION ALL SELECT '101', 2.8, 3.0
UNION ALL SELECT '200', 3.2, 3.5
UNION ALL SELECT '300', 0 , 5.0
SELECT 商品编号,(CASE 会员价 WHEN 0 THEN (SELECT 零售价)
ELSE 会员价 END) AS 價格 FROM @T WHERE 商品编号='200'SELECT 商品编号,(CASE 会员价 WHEN 0 THEN (SELECT 零售价)
ELSE 会员价 END) AS 價格 FROM @T WHERE 商品编号='100'
set @i=商品编号select 商品编号,(case 会员价 when 0 then 零售价 else 会员价 end) as 价格 from 你的表 where 商品编号=@i
CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是使用可读性更强的值替换代码或缩写。下面的查询使用 CASE 函数重命名书籍的分类,以使之更易理解。USE pubs
SELECT
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1
select 商品编号,case isnull(会员价,0) when 0 then 零售价 else 会员价 end as 价格 from 表 where 商品编号='条件'
case when [会员价]=0 then [零售价] else [会员价] end [会员价],
[零售价]
from table
where [商品编号]='输入的编号'
(CASE ISNULL(会员价,0) WHEN 0 THEN 零售价 ESLE 会员价 END ) AS 价格
FROM TABLE