SQL-对没有表数据的营销类别进行排序

Question

百货公司使用下表跟踪销售情况。数据模型不包括有关地板和所销售货物类别的信息。

CREATE TABLE A1
(    
  date      datetime,
  category  nvarchar(30),
  turnover     money  
);

INSERT INTO A1  VALUES   (SYSDATETIME(), 100, 1000);   
INSERT INTO A1  VALUES   (SYSDATETIME(), 201, 1700);
...

数据模型/表不能更改或编辑。有关产品组和楼层的信息可以从类别中转移。

CREATE VIEW sort_department_productgroups_view
AS
   WITH sort_department_productgroups
        AS (SELECT date,
                   category,
                   turnover,
                   CASE category
                      WHEN 100 THEN 1
                      WHEN 201 THEN 1
                      WHEN 303 THEN 1
                      WHEN 101 THEN 2
                      WHEN 102 THEN 2
                      ELSE 9
                   END
                      floor,
                   CASE category
                      WHEN 100 THEN 'sport'
                      WHEN 102 THEN 'sport'
                      WHEN 201 THEN 'leisure'
                      WHEN 303 THEN 'business'
                      WHEN 101 THEN 'sport'
                      WHEN 202 THEN 'leisure'
                      ELSE 'unknown'
                   END
                      productgroup
              FROM a1)
   SELECT *
     FROM sort_department_productgroups;

go

新视图的示例查询：

SELECT * FROM sort_department_productgroups_view where productgroup='sport';

是否有更好的方法来处理这样的任务？这能在一个大数据库上工作吗？

Answer 1

这实在是太想要一张查找表了。但我必须承认，一个nvarchar类型的类别列充满了数值，我想知道.

试着这样做：

编辑:如OP所述，将相关列更改为NVARCHAR(30)，这是不可更改的.

CREATE TABLE CATEGORY(ID INT IDENTITY CONSTRAINT PK_CATEGORY PRIMARY KEY
                     ,category NVARCHAR(30)
                     ,CategoryName VARCHAR(100)
                     ,[Floor] INT );
INSERT INTO  CATEGORY VALUES
                      (100,'sport',1)
                     ,(101,'sport',2)
                     ,(102,'sport',2)
                     ,(201,'leisure',1)
                     ,(202,'leisure',9)
                     ,(303,'business',9)
                     --add more...

CREATE TABLE A1
(  
  date      datetime,
  category  NVARCHAR(30),
  turnover     money  
);

INSERT INTO A1  VALUES   (SYSDATETIME(), 100, 1000);   
INSERT INTO A1  VALUES   (SYSDATETIME(), 201, 1700);
GO

CREATE VIEW sort_department_productgroups_view
AS
SELECT A1.date
      ,a1.turnover
      ,ISNULL(CATEGORY.Floor,9) AS [Floor]
      ,ISNULL(CATEGORY.CategoryName,'unknown') AS [productgroup]
FROM A1
LEFT JOIN CATEGORY ON CATEGORY.category=A1.category;
GO

SELECT * FROM sort_department_productgroups_view 

Answer 2

我同意Shnugo的观点，即参考表更可取。

我将更简单地将这一观点定义为：

CREATE VIEW sort_department_productgroups_view AS
     SELECT date, category, turnover,
            (CASE WHEN category IN ('100', '201', '202') THEN 1
                  WHEN category IN ('101', '102') THEN 2
                  ELSE 9
             END) as floor,
            (CASE WHEN category IN ('100', '101', '102') THEN 'sport'
                  WHEN category IN ('201', '202') THEN 'leisure'
                  WHEN category IN ('303') THEN 'business'
                  ELSE 'unknown'
             END) as productgroup
     FROM a1;

两个重要的区别：

• 这不再使用搜索的情况，因此它将每个组的定义保持在一个条件下。
• 这些值以引号表示，因为category是一个字符串。

最后，Server允许计算列，因此可以将此“列”合并到表定义中：

alter table a1
    add floor as (CASE WHEN category IN ('100', '201', '202') THEN 1
                       WHEN category IN ('101', '102') THEN 2
                       ELSE 9
                  END);

产品组也是如此。