我有一个表,criteria
其结构是这样的:
+----------+------+-------+
| criteria | user | total |
+----------+------+-------+
| xxxxxxxx | xx | xxx |
+----------+------+-------+
我在创建查询时遇到问题。我想从单个表进行数据透视查询,并且我有以下数据:
+----------+------+-------+
| criteria | user | total |
+----------+------+-------+
| F1 | A1 | 17 |
| F2 | A1 | 9 |
| F3 | A1 | 6 |
| F4 | A1 | 4 |
| F1 | A2 | 19 |
| F2 | A2 | 6 |
| F3 | A2 | 2 |
| F4 | A2 | 8 |
+----------+------+-------+
我想使查询枢轴是这样的:
+----------+------+-------+------+-------+
| criteria | user | total | user | total |
+----------+------+-------+------+-------+
| F1 | A1 | 17 | A2 | 19 |
| F2 | A1 | 9 | A2 | 6 |
| F3 | A1 | 6 | A2 | 2 |
| F4 | A1 | 4 | A2 | 8 |
+----------+------+-------+------+-------+
有人可以帮我写一个查询以获得上述结果吗?
您的问题似乎可以通过自连接而不是枢轴来很好地解决。您可以使用以下列JOIN
将criteria
表合并在一起criteria
:
SELECT c1.criteria AS criteria, c1.user AS user, c1.total AS total,
c2.user AS user, c2.total AS total
FROM criteria c1 INNER JOIN criteria c2
ON c1.criteria = c2.criteria
WHERE c1.user < c2.user
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句