mysql - 用于比较的子查询

Question

我的聚合查询中有以下数据:

name        release_year    iTunes                    Google                Microsoft
10 to Mid   2003            SDBUY,HDRENT,SDRENT       NULL                  NULL
100 Girls   2001            HDBUY                     HDBUY,HDRENT,SDRENT   SDBUY,SDRENT
100 Rifles  NULL            NULL                      HDBUY                 HDBUY,HDRENT

由此我想构建以下结果:

name         release_year    offers_on_itunes      top_platform    top_platform_offers
10 to Mid    2003            SDBUY,HDRENT,SDRENT   iTunes          SDBUY,HDRENT,SDREN
100 Girls    2001            HDBUY                 Google          SDBUY,SDRENT
100 Rifles   NULL            NULL                  Microsoft       HDBUY,HDRENT           

换句话说，我们可以通过以下方式获得结果中的前三列:

SELECT name, release_year, itunes AS offers_on_itunes FROM (subquery)

但是获得顶级平台和顶级平台优惠的最佳方式是什么？也许基于 LEN() 执行 CASE 会获得 top_platform 但我不太确定另一个。如何最好地构建它？

Answer 1

您可以使用 if 函数，如下所示。我知道比较三列可能会变得更加困惑，但希望这能让您走上正确的轨道...

SELECT name, release_year, itunes as offers_on_itunes,Google,
IF (itunes is null,0,
    ROUND (   
    (
        LENGTH(itunes)
        - LENGTH( REPLACE ( itunes, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
) as numItunesOfferings,

IF (Google is null,0,
ROUND (   
    (
        LENGTH(Google)
        - LENGTH( REPLACE ( Google, ",", "") ) 
    ) / LENGTH(",")  
)  +1
) as numGoogleOfferings,
if (
    IF (itunes is null,0,
    ROUND (   
    (
        LENGTH(itunes)
        - LENGTH( REPLACE ( itunes, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
) > IF (Google is null,0,
ROUND (   
    (
        LENGTH(Google)
        - LENGTH( REPLACE ( Google, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
),'itunes','Google') as top_platform
FROM (subquery)