使用Select或PL SQL转置 [英] Use Select or PL SQL to Transpose

问题描述

我在MySQL中有一个选择查询，如下所示: 该查询以表格形式执行并产生结果

I have a select query in MySQL shown below: This query executes and produces results in the form of a table

Current | Past1  | Past2  | Past 3  |  Past4
   200     600      800      000         88

我想转置这些结果以获取以下形式的信息:因此，我希望结果(转置)

I would like to transpose these results to get information in the form: Therefore I would like the reults to be (transposed)

Current    200
Past1      600
Past2      800
Past3      000
Past4       88

这可以在oracle中使用SQL语句完成吗，或者我需要执行PL SQL过程吗?如果可以使用PL SQL，您可以提供一些示例参考.

Can this be done using a SQL statement in oracle or do I need a PL SQL procedure for this. If a PL SQL can you provide some sample reference to it.

select
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),30) THEN 1 else 0
    end) as Current,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),60) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),30) THEN 1 else 0
    end) as Past1,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),90) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),60) THEN 1 else 0
    end) as Past2,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),120) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),90) THEN 1 else 0
    end) as Past3,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),150) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),120) THEN 1 else 0
    end) as Past4
from `cpt-prod`
where CPT_STATE <> 'Closed'

推荐答案

我将以其他方式进行查询，以便数据以您想要的方式显示.

I'd do your query in a different way so that the data comes out the way you want.

1. 创建一个表格(例如年龄)来定义您的间隔(名称，lowerBound，upperBound)
   如果想花哨的话，只需要边界值"，就可以使用自联接构造相应的upperBound.
2. 然后使用此表并将其连接到事实"表:

1. Create a table (e.g. Ages) to define your intervals (name, lowerBound, upperBound)
   If you want get fancy, all you need is the "border values" and the corresponding upperBound can be constructed with a self-join.
2. Then use this table and join it to your "facts" table:

(似乎无法弄清楚如何正确格式化以下政治家，使其在没有此行的情况下显示为代码...)

(Can't seem to figure out how to properly format the following statemen to show up as code without this line ...)

SELECT name, COUNT(*)
FROM cpt-prod JOIN Ages on cpt_originated 
    between Subdate(now - upperBound) And Subdate (now - lowerBound)
GROUP BY name

根据您的需求和dbms，您也许可以在实际的SQL语句中定义Ages表. (AFAIK，这在Oracle中无法完成，但是也许有人可以在MySQL中添加有关如何执行此操作的注释.)

Depending on your needs and dbms, you may be able to define your Ages table in the actual SQL statement. (AFAIK, this can't be done in Oracle, but maybe someone can add a comment on how to do this in MySQL).

这篇关于使用Select或PL SQL转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！