如何使用模型在mysql中透视表 [英] how to pivot a table in mysql using model

问题描述

我需要在mysql中透视表并在将表建模的过程中对其进行建模Mysql中的围绕日期"列，这几乎是我需要做的一个完美示例.但是运行它时没有任何结果，也看不到我在做什么错. 我的桌子是这样的:

I need to pivot a table in mysql and am modeling it on PIvoting table around date column in Mysql, which is almost a perfect example of what I need to do. But I'm getting no result when I run it and can't see what I'm doing wrong. The table I have is like this:

mediaID q_short_name start_time stop_time audio_file
ee  CVV Number       208        210       j.mp3
ee  Expiration Date  308        310       j.mp3
ff  CVV Number       124        127       k.mp3
ff  Expiration Date  166        169       k.mp3

我希望它像这样:

mediaID CVVstart_T CVVstop_T Exp_start_time Exp_stop_time audio_file
ee      208      210      308            310            j.mp3
ff      124      127      166            169            k.mp3

因此，我首先尝试了此操作:

so I tried this as a first step:

Create view my_test_extended as (select my_test.mediaID, case when 
q_short_name = 'CVV Number' then my_test.start_time end as CVVstart_T 
from my_test);

这给了我查询确定，受影响的0行.如何调整查询以获取所需的结果?

This is giving me Query OK, 0 rows affected. How can I adjust the query so I get the result I want?

推荐答案

让我们谈谈自我加入"而不是枢纽".

Let's talk about a "self-join" instead of "pivot".

SELECT  c.mediaID,
        c.start_time AS CVVstart,
        c.end_time   AS CVVstop,
        e.start_time AS ExpStart,
        e.stop_time AS ExpStop,
        c.audio_file
    FROM  my_test AS c
    JOIN  my_test AS e  USING(mediaID)
    WHERE  c.q_short_name = 'CVV Number'
      AND  e.q_short_name = 'Expiration Date';

这篇关于如何使用模型在mysql中透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！