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

如何生成sql矩阵

matrix sql-server sql

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 年前

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

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。每次添加玩具时,都必须更改查询。这不是关系数据库应该如何工作的。

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)

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