mysql - MYSQL中以列为行的表获取数据

Question

任何人都可以帮我完成一个将列转换为行的查询，然后相应地查找数据。

问题如下。我尝试过一个查询，将列名获取到行中，但我无法获取值，或者有任何想法来计算第 3 列和第 4 列。我是 SQL 方面的新手。有人可以帮我从这里出去吗。我听 friend 建议使用 Pivot，当我浏览时，我发现 MYSQL 不支持 PIVOT

Input Table: 

E1           E2           E3           E4           E5           E6

Null         1234         234          12           A            B
123          Null         Null         Null         12           B
Null         Null         Null         Null         Null         Null
123          2            1            A            1            2


Output Table:

C1           C2           Count           TotalC           percent           

E1           123          2               2                1
E2           1234         1               2                0.5
E2           2            1               2                0.5
E3           234          1               2                0.5
E3           1            1               2                0.5
E4           12           1               2                0.5
E4           A            1               2                0.5
E5           A            1               3                0.3
E5           12           1               3                0.3
E5           1            1               3                0.3
E6           B            2               3                0.6
E6           2            1               3                0.3

问题的解释:

1. C1 是输入表的列名称。
2. C2 是这些列中的数据。
3. 计数是每列中相似商品和单品的数量。
4. TotalC 是非空值的总数。
5. 百分比是计数/总计。

我工作的查询:SELECT (COLUMN_NAME)AS Column1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'inputtable';

注意:在MYSQL中查询。

Answer 1

正如我在上面的评论中提到的，您可能应该以不同的方式处理这个问题。请参阅What is the XY problem?

但是，如果必须的话，您可以使用 UNION 解决此问题:

SELECT *, Count/TotalC AS percent
FROM (
  SELECT   'E1' AS C1, E1 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E1 IS NOT NULL
  GROUP BY C2
UNION ALL
  SELECT   'E2' AS C1, E2 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E2 IS NOT NULL
  GROUP BY C2
UNION ALL
  SELECT   'E3' AS C1, E3 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E3 IS NOT NULL
  GROUP BY C2
UNION ALL
  SELECT   'E4' AS C1, E4 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E4 IS NOT NULL
  GROUP BY C2
UNION ALL
  SELECT   'E5' AS C1, E5 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E5 IS NOT NULL
  GROUP BY C2
UNION ALL
  SELECT   'E6' AS C1, E6 AS C2, COUNT(*) AS Count
  FROM     Input
  WHERE    E6 IS NOT NULL
  GROUP BY C2
) t1 NATURAL JOIN (
  SELECT 'E1' AS C1, COUNT(E1) AS TotalC FROM Input
UNION ALL
  SELECT 'E2' AS C1, COUNT(E2) AS TotalC FROM Input
UNION ALL
  SELECT 'E3' AS C1, COUNT(E3) AS TotalC FROM Input
UNION ALL
  SELECT 'E4' AS C1, COUNT(E4) AS TotalC FROM Input
UNION ALL
  SELECT 'E5' AS C1, COUNT(E5) AS TotalC FROM Input
UNION ALL
  SELECT 'E6' AS C1, COUNT(E6) AS TotalC FROM Input
) t2

查看 sqlfiddle .