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 类型的类别列让我想知道......

像这样尝试：

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

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是一个字符串。

最后，SQL 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);

产品组也是如此。