T-SQL中的比较实用技巧,处理数字为小数点后两位和判断类型显示不同文字

原创于 2008-05-28 13:44:00 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

学习小记同时被 2 个专栏收录

37 篇文章

订阅专栏

Other

2 篇文章

订阅专栏

本文介绍了一个SQL存储过程sp_GetListPortfolio，该过程用于从Portfolio表中检索特定AccountId的所有投资组合信息，包括组合名称、创建日期、总成本、市场价值等，并计算收益百分比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ALTER PROCEDURE [dbo].[sp_GetListPortfolio]
@AccountId uniqueidentifier
as
select PortfolioId,
       AccountId,
       PortfolioName,
       CreateDate,
       CAST(TotalCost as decimal(38,2)) as TotalCost,
       CAST(TotalMarket as decimal(38,2)) as TotalMarket,
       CAST(EarnMoney as decimal(38,2)) as EarnMoney,
       CAST((TotalMarket-TotalCost) as decimal(38,2))  as EarnNow,
       CAST((case when TotalCost<0.1 then 0 else 100*(TotalMarket-TotalCost)/TotalCost end) as decimal(38,2)) as EarnNowPercent,
       (case when type=0 then N'观察组合' else N'实际组合' end) as [Type]
        from Portfolio with (nolock)
where AccountId=@AccountId