工作中点滴记录

永远保持学徒心态

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

因为 根据需求要对查询出的数据情况进行按照条件显示,所以利用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

 

posted on 2012-02-08 14:29  梦里故乡  阅读(433)  评论(0编辑  收藏  举报