在条件中使用别名合并：合并（max（大小写为value时为value）作为别名 [英] using alias coalesce in a condtion : coalesce (max(case when value then value ) as alias

问题描述

我在表 ad_params 中有一个交叉表，所以我做了：

I have for the table ad_params the a crosstab so i made:

coalesce（max（CASE when ad_params.name ='model'and ad_params.value ='cordova'the ad_params.value end），NULL）修复它，但是条件是 ad_params.value ='cordova'不起作用

它为我提供了模型为<$ c的所有结果$ c> cordova ，在 model 字段中显示为空白，因此我必须删除所有不在其中的字段

coalesce(max(CASE when ad_params.name='model' and ad_params.value='cordova'then ad_params.value end ),NULL) to fix it ,however the condition is ad_params.value='cordova' not working
it's getting me all the result where the model is cordova it is shown else just a blank in the model field so i have to delete all the fields where it is not

  ************************************************************************
  ad_media_id | ad_id  | name | timestamp| regdate | brand | fuel    |model 
   xxxxxxxxxxx   1      jack     xxxx                                cordova
   xxxxxxxxxxx   2      joe      xxxx                                cordova

   ************************************************************************

所以我必须只有数字1

so i must just have Number 1

coalesce（max（在ad_params.name ='regdate'
THEN ad_params.value END的情况下），NULL）AS regdate，

 select ad_media.ad_media_id
   , ad_media.ad_id
   , ads.name
   , action_states.timestamp
   , max(CASE WHEN ad_params.name = 'regdate' THEN ad_params.value END) 
    AS regdate
   , max(CASE WHEN ad_params.name = 'mileage' THEN ad_params.value END) 
     AS mileage
   , max(CASE WHEN ad_params.name = 'fuel'    THEN ad_params.value END) 
     AS fuel
   , max(CASE WHEN ad_params.name = 'brand'   THEN ad_params.value END) 
     AS brand
   , max(CASE WHEN ad_params.name = 'model'   THEN ad_params.value END) 
     AS model

 from ad_media 
 left join action_states 
 on action_states.ad_id = ad_media.ad_id
 inner join ads 
 on ads.ad_id = action_states.ad_id
 inner join ad_params 
 on  ad_params.ad_id = ad_media.ad_id
 and 
 (
    ad_params.name = 'model' 
    and  ad_params.value = 'cordoba' 
 )


   where action_states.state = 'reg'   
   and action_states.action_id = '1' 
   and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
   and ads.category = '2010'   

   group by ad_media.ad_media_id
  , ad_media.ad_id
  , ads.name
  , action_states.timestamp 
  order by ad_media.ad_id;

推荐答案

每个讨论都在注释中，因为您期望每个讨论 ad_params 中的 ad_id ，使每个<$具有单个值 c $ c> name ，我们可以如下重写您的代码，并轻松添加所需的过滤器：

Per discussion in comments, because you're expecting each ad_id in ad_params to have a single value per name, we can rewrite your code as below, and easily add the required filter:

select ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp
, regdate.value AS regdate
, mileage.value AS mileage
, fuel.value AS fuel
, brand.value AS brand
, model.value AS model

from ad_media 
inner join action_states 
    on action_states.ad_id = ad_media.ad_id
inner join ads 
    on ads.ad_id = action_states.ad_id
left join ad_params as regdate --technically we could do an inner join here since we're using this in the WHERE clause now; but I'll leave as an outer join in case you need more advanced logic...
    on regdate.ad_id = ad_media.ad_id
    and regdate.name = 'regdate' 
left join ad_params as mileage
    on mileage.ad_id = ad_media.ad_id
    and mileage.name = 'mileage' 
left join ad_params as fuel
    on fuel.ad_id = ad_media.ad_id
    and fuel.name = 'fuel' 
left join ad_params as brand
    on brand.ad_id = ad_media.ad_id
    and brand.name = 'brand' 
left join ad_params as model
    on model.ad_id = ad_media.ad_id
    and model.name = 'model' 

where action_states.state = 'reg'   
and action_states.action_id = '1' 
and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
and ads.category = '2010'   
and regdate.value = '2018' --your condition 

/* --this is probably no longer needed; if it is instead consider adding a `distinct`
group by ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp 
order by ad_media.ad_id;
*/

如果您想要更多与原始SQL保持一致的内容，则下面的内容也可以使用：

If you want something more in keeping with your original SQL, the below would also work:

select ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp
, max(CASE WHEN ad_params.name = 'regdate' THEN ad_params.value END) AS regdate
, max(CASE WHEN ad_params.name = 'mileage' THEN ad_params.value END) AS mileage
, max(CASE WHEN ad_params.name = 'fuel'    THEN ad_params.value END) AS fuel
, max(CASE WHEN ad_params.name = 'brand'   THEN ad_params.value END) AS brand
, max(CASE WHEN ad_params.name = 'model'   THEN ad_params.value END) AS model

from ad_media 
left join action_states 
    on action_states.ad_id = ad_media.ad_id
inner join ads 
    on ads.ad_id = action_states.ad_id
inner join ad_params --since we expect this filter to remove results, we now need it to be an inner join
    ad_params.ad_id = on ad_media.ad_id
    and 
    (
        ad_params.name != 'regdate' --\_i.e. if the value is regdate we want 2018; if it's not regdate we'll take any value
        or ad_params.value = '2018' --/
    )

where action_states.state = 'reg'   
and action_states.action_id = '1' 
and action_states.timestamp::DATE BETWEEN '2018-04-17' AND '2018-04-17'
and ads.category = '2010'   

group by ad_media.ad_media_id
, ad_media.ad_id
, ads.name
, action_states.timestamp 
order by ad_media.ad_id;

我们也可以用许多其他方式来重写；但是要选择bset，我们需要更多地了解您的架构和数据。这篇文章提供了一些有用的相关信息： https://stackoverflow.com/a/7449213/361842 （尽管重点MSSQL，而不是Postgres；但类似的想法也适用。）

We can rewrite this many other ways too; but to choose the bset we'd need to know more about your schema & data. This post gives a bit of useful related info: https://stackoverflow.com/a/7449213/361842 (though focusses on MSSQL rather than Postgres; but similar ideas apply).

这篇关于在条件中使用别名合并：合并（max（大小写为value时为value）作为别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！