SQL查询将列数转换为行数 [英] SQL Query to Transpose Column Counts to Row Counts
本文介绍了SQL查询将列数转换为行数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个类似于以下表格的表格,其中显示了类型计数。我需要并且一直试图将数据显示为1列和7行,但是...没有成功。
I have a table that looks like the following which shows the count of types. I need to and have been trying to display data as 1 column and 7 rows instead though... without success.
__________________________________________________________________________
| col types | win2k | winxp | win2k3 | vista | win7 | win8 | win8.1 |
--------------------------------------------------------------------------
| count of types | 2365 | 65655 | 422445 | 4822 | 482 | 2331 | 485323 |
--------------------------------------------------------------------------
Select
count(case when col1 ~* '5.0.2195' then 1 else null end) as Win2k,
count(case when col1 ~* '5.1.2600' then 1 else null end) as WinXP,
count(case when col1 ~* '5.2.3790' then 1 else null end) as W2k3,
count(case when (col1 ~* '6.0.6000'
or col1 ~* '6.0.6001' or col1 ~* '6.0.6002')
then 1 else null end) as Vista,
count(case when (col1 ~* '6.1.7600'
or col1 ~* '6.1.7601')
then 1 else null end) as Win7,
count(case when col1 ~* '6.2.9200' then 1 else null end) as Win8,
count(case when (col1 ~* '6.3.9200'
or col1 ~* '6.3.9600')
then 1 else null end) as "Win8.1"
From col1
理想情况下,它看起来像这样:
Ideally it would look something like this:
___________________
| types | count |
-------------------
| win2k | 2365 |
| winxp | 65655 |
| win2k3 | 422445 |
| vista | 4822 |
| win7 | 482 |
| win8 | 2331 |
| win8.1 | 485323 |
-------------------
注意:
- 我正在将PostgreSQL 9.3与PGADMIN III一起使用
- 我无法创建任何自定义函数
- 是否有更多的列可以使这项工作无关紧要
推荐答案
以GROUP BY为目标,更容易进行此类查询,例如:
These type of queries are easier to make with an aim of GROUP BY, like this:
Select
case when profile.foo ~* '5.0.2195' then 'Win2k'
when profile.foo ~* '5.1.2600' then 'WinXP'
when profile.foo ~* '5.2.3790' then 'W2k3'
when (profile.foo ~* '6.0.6000'
or profile.foo ~* '6.0.6001'
or profile.foo ~* '6.0.6002')
then 'Vista'
when (profile.foo ~* '6.1.7600'
or profile.foo ~* '6.1.7601')
then 'Win7'
when profile.foo ~* '6.2.9200' then 'Win8'
when (profile.foo ~* '6.3.9200'
or profile.foo ~* '6.3.9600')
then 'Win8.1' ELSE 'Other' END as type,
count(*) as cnt
From profile
GROUP BY 1
如以下注释所述,此查询适用于互斥情况,即当 profile .foo
包含一个代表每行一个操作系统的值
As commented below this query will work for mutually exclusive cases, i.e. when profile.foo
contains a value representing one OS per row
这篇关于SQL查询将列数转换为行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文