代码之家  ›  专栏  ›  技术社区  ›  Emma W.

如何生成sql矩阵

  •  0
  • Emma W.  · 技术社区  · 7 年前

    如何从sql查询中的表创建此矩阵?我有一张桌子,上面有卖了多少货。我想显示商品名称,而不是商品id。但重要的是,字段toy1、toy2和toy3的数据类型为decimal(20,8)

    Table 1
    
    id   year   toy1   toy2   toy3
    -------------------------------
    -1           1      3      2
     1   2016    100    20     50
     2   2017    150    120    40
    
    Table 2
    
    id     toy
    ---------------
    1      doll
    2      car
    3      bear
    
    Result matrix
    
     id   year   toy1   toy2   toy3
    -------------------------------
    -1           doll   bear   car
     1   2016    100    20     50
     2   2017    150    120    40
    
    4 回复  |  直到 7 年前
        1
  •  1
  •   Yogesh Sharma    7 年前

    对于 SQL Server 我会做一些 left joins

    SELECT T.id,
           T.year,
           CASE
               WHEN T1.TOY IS NULL THEN CAST(T.[toy1] AS VARCHAR) ELSE T1.TOY
           END [toy1],
          CASE
               WHEN T2.TOY IS NULL THEN CAST(T.[toy2] AS VARCHAR) ELSE T2.TOY
           END [toy2],
          CASE
               WHEN T3.TOY IS NULL THEN CAST(T.[toy3] AS VARCHAR) ELSE T3.TOY
           END [toy3]
    
    FROM <Table1> T
         LEFT JOIN <Table2> T1 ON T1.ID = T.toy1
         LEFT JOIN <Table2> T2 ON T2.ID = T.toy2
         LEFT JOIN <Table2> T3 ON T3.ID = T.toy3; 
    

    结果:

    id  year    toy1    toy2    toy3
    -1  NULL    doll    bear    car
    1   2016    100     20      50
    2   2017    150     120     40
    
        2
  •  1
  •   Thorsten Kettner    7 年前

    这是一个非常糟糕的数据模型。

    表格应该代表一个实体,例如玩具、订单、年销售额。。。另一侧的一列表示该实体的属性,例如玩具的名称或价格、订单日期、销售年份。在你的模型中,你混合了这个。什么是“玩具1”?玩具ID?金额?这似乎是两者的结合。然后你的表2可以放很多很多玩具,而你的表1只能放3个。这不匹配。

    你应该拥有什么:

    桌上玩具

    toy_id | name
    -------+-----
    1      | doll
    2      | car
    3      | bear
    

    表销售

    year | toy_id | amount
    2016 | 1      |  100
    2016 | 2      |   20
    2016 | 3      |   50
    2017 | 1      |  150
    2017 | 2      |  120
    2017 | 3      |   40
    

    您也不会编写查询来提供矩阵。您可以选择数据(例如。 select * from sales order by year, toy_id )并让你的应用程序处理显示。使用任何编程语言(无论是C#、Visual Basic、Java、PHP等)在循环中获取数据并将其显示在网格中都很容易。

    在SQL查询中,您必须事先知道列,但矩阵输出并非如此,因为在运行甚至编写查询之前,您必须知道toys表中有多少toys。每次添加玩具时,都必须更改查询。这不是关系数据库应该如何工作的。

        3
  •  0
  •   Daniel E.    7 年前

    我不确定,但大致是这样的:

    SELECT T1.id,
       year, 
       CASE WHEN T1.Toy1 = T2.id AND T1.id=-1 THEN T2.toy
       ELSE T1.Toy1 
       END AS Toy1,
       CASE WHEN T1.Toy2 = T2.id AND T1.id=-1THEN T2.toy
       ELSE T1.Toy2 
       END AS Toy1,
       CASE WHEN T1.Toy3 = T2.id AND T1.id=-1 THEN T2.toy
       ELSE T1.Toy3 
       END AS Toy3
    FROM Table1 T1
    LEFT JOIN Table2 T2 ON T1.id=-1 AND T2.id IN (T1.Toy1, T1.Toy2, T1.Toy3)
    
        4
  •  0
  •   Zorkolot    7 年前

    一种方法是只选择-1行并应用算法来获得玩具名称。那可能是 UNION ALL 表的其余部分不是-1行。

    您必须将输出转换为varchar,以混合文字和数字:

    DECLARE @table1 TABLE (id int, [year] int, toy1 int, toy2 int, toy3 int)
    DECLARE @table2 TABLE (id int, toy varchar(100))
    
    INSERT INTO @table1 VALUES (-1, null, 1, 3, 2), (1, 2016, 100,20,50), (2,2017,150,120,40)
    INSERT INTO @table2 VALUES (1,'doll'), (2,'car'), (3,'bear')
    
    -- for the -1 row
    SELECT T1.id, COALESCE(CAST(year as varchar), '') AS year
          ,(SELECT toy FROM @table2 T2 WHERE T2.id = T1.toy1) AS toy1
          ,(SELECT toy FROM @table2 T2 WHERE T2.id = T1.toy2) AS toy2
          ,(SELECT toy FROM @table2 T2 WHERE T2.id = T1.toy3) AS toy3
    
      FROM @table1 T1
     WHERE T1.id = -1
    
    UNION ALL
    
    --all other rows
    SELECT T3.id
          ,CAST(T3.year as varchar) 
          ,CAST(T3.toy1 as varchar)
          ,CAST(T3.toy2 as varchar)
          ,CAST(T3.toy3 as varchar)
    
      FROM @table1 T3
     WHERE T3.id <> -1
    

    提供输出:

    id  year    toy1    toy2    toy3
    -1          doll    bear    car
    1   2016    100     20      50
    2   2017    150     120     40