SQL 中Case的用法

因为 根据需求要对查询出的数据情况进行按照条件显示，所以利用case来实现这个要点：

条件是如果名字为空且id为0，这个用户就是报价用户

　　　　如果是名字为空且id不为0，这个用户名字不存在

　　　　如果名字不为空，那么就显示此用户名

View Code

1 USE [xb_quotation_dev]
 2 GO
 3 
 4 /****** Object:  StoredProcedure [dbo].[usp_GetHistory_Original_Price]    Script Date: 02/08/2012 11:07:35 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 -- =============================================
12 -- Author:        <Author,,Name>
13 -- Create date: <2012-02-08>
14 -- Description:    <获得钢材，板材，线材，炉料 原报价>
15 --@typeid=1 代表钢材，板材，线材；@typeid=2 代表炉料
16 -- =============================================
17 CREATE PROCEDURE [dbo].[usp_GetHistory_Original_Price]
18 @StartDate    DATETIME,
19 @EndDate    DateTime,
20 @TypeId    INT
21 AS
22 BEGIN
23     SET NOCOUNT ON;
24         IF(@TypeId=1)
25             BEGIN
26                 WITH tm AS(
27                             SELECT CONVERT(VARCHAR(10),PriceDate,120) AS PriceDate,AdminUserId FROM MD_HistoryRawPrice 
28                                 WHERE  CONVERT(VARCHAR(10),PriceDate,120)>=CONVERT(VARCHAR(10),@StartDate,120)
29                                 AND CONVERT(VARCHAR(10),PriceDate,120)<=CONVERT(VARCHAR(10),@EndDate,120)
30                                 GROUP BY CONVERT(VARCHAR(10),PriceDate,120),AdminUserId
31                         )
32                 SELECT tm.PriceDate,tm.AdminUserId,
33                         CASE WHEN(m.J_TrueName is null and tm.AdminUserId=0)    THEN '报价系统'
34                         WHEN (m.J_TrueName IS null and tm.AdminUserId<>0)    THEN '名字不存在'
35                         ELSE m.J_TrueName 
36                         END AS J_TrueName
37                         FROM tm LEFT JOIN Member_Info m on tm.AdminUserId = m.UNID ORDER BY tm.PriceDate DESC            
38             END
39         ELSE
40             BEGIN
41                 WITH tm AS(
42                             SELECT CONVERT(VARCHAR(10),PriceDate,120) AS PriceDate,AdminUserId FROM charge_HistoryRawPrice 
43                             WHERE  CONVERT(VARCHAR(10),PriceDate,120)>=CONVERT(VARCHAR(10),@StartDate,120) 
44                             AND CONVERT(VARCHAR(10),PriceDate,120)<=CONVERT(VARCHAR(10), @EndDate,120) 
45                             GROUP BY CONVERT(VARCHAR(10),PriceDate,120),AdminUserId
46                             )
47                 SELECT tm.PriceDate,tm.AdminUserId,
48                         CASE WHEN(m.J_TrueName is null and tm.AdminUserId=0)    THEN '报价系统'
49                         WHEN (m.J_TrueName IS null and tm.AdminUserId<>0)    THEN '名字不存在'
50                         ELSE m.J_TrueName 
51                         END AS J_TrueName
52                         FROM tm LEFT JOIN Member_Info m on tm.AdminUserId = m.UNID ORDER BY tm.PriceDate DESC
53             END
54 END
55 
56 GO