MySQL UNION ALL 从别名创建列

Question

我有一个带有 union all 的查询，它从 3 个不同的表获取数据。该查询工作得很好，但我想要完成的是将 KWH 和 Present Demand 作为实际列及其相应的值，而不是作为标识所引用的值类型的别名过滤器列。

I would like to have the following
name     kwh        Demand   date
meter1   10252.25   25.35    2014-11-06 

这里我附上MySQL查询。

SELECT
ds.name as KWH_Name,
ROUND(pv.pointValue,2) as KWH,
FROM_UNIXTIME(pv.ts/1000) as Date,
'KWH' as Filter

FROM 
mango.pointvalues as pv,
mango.datapoints as dp,
mango.datasources as ds

where
pv.dataPointId = dp.id and
FROM_UNIXTIME(pv.ts/1000) > '2014-10-31' and
dp.dataSourceId = ds.id and
ds.xid in ('DS_151181','DS_495799','DS_432821','DS_067251','DS_896060','DS_056870','DS_975807','DS_144197','DS_851933','DS_092150','DS_581587','DS_683881','DS_654410','DS_778982','DS_484407','DS_618254') and
dp.xid in ('DP_527107','DP_679864','DP_094433','DP_258167','DP_302281','DP_424331','DP_695840','DP_150558','DP_160298','DP_971876','DP_968565','DP_263818','DP_244594','DP_923944','DP_561553','DP_865672')

UNION ALL

SELECT
ds.name as KWH_Name,
ROUND(pv.pointValue,2) as PRESENT_DEMAND,
FROM_UNIXTIME(pv.ts/1000) as Date,
'PRESENT DEMAND' as Filter

FROM 
mango.pointvalues as pv,
mango.datapoints as dp,
mango.datasources as ds

where
pv.dataPointId = dp.id and
FROM_UNIXTIME(pv.ts/1000) > '2014-10-31' and
dp.dataSourceId = ds.id and
ds.xid in ('DS_151181','DS_495799','DS_432821','DS_067251','DS_896060','DS_056870','DS_975807','DS_144197','DS_851933','DS_092150','DS_581587','DS_683881','DS_654410','DS_778982','DS_484407','DS_618254') and
dp.xid in ('DP_156052','DP_330754','DP_785426','DP_803690','DP_948480','DP_463738','DP_970137','DP_464935','DP_522683','DP_143012','DP_840629','DP_166536','DP_271470','DP_272164','DP_828573','DP_221039')

Answer 1

除了 dp.xid 列表之外，您的查询本质上是相同的。您可以使用条件聚合做您想做的事情:

SELECT ds.name as KWH_Name,
       FROM_UNIXTIME(pv.ts/1000) as Date,
       SUM(case when dp.xid in ('DP_527107','DP_679864','DP_094433','DP_258167','DP_302281','DP_424331','DP_695840','DP_150558','DP_160298','DP_971876','DP_968565','DP_263818','DP_244594','DP_923944','DP_561553','DP_865672')
                then pv.pointValue
           end) as KWH,
       SUM(case when dp.xid in ('DP_156052','DP_330754','DP_785426','DP_803690','DP_948480','DP_463738','DP_970137','DP_464935','DP_522683','DP_143012','DP_840629','DP_166536','DP_271470','DP_272164','DP_828573','DP_221039')
                then pv.pointValue
           end) as PresentDemand
FROM mango.pointvalues pv JOIN
     mango.datapoints dp
     ON pv.dataPointId = dp.id JOIN
     mango.datasources ds
     ON dp.dataSourceId = ds.id 
WHERE FROM_UNIXTIME(pv.ts/1000) > '2014-10-31' and
      ds.xid in ('DS_151181','DS_495799','DS_432821','DS_067251','DS_896060','DS_056870','DS_975807','DS_144197','DS_851933','DS_092150','DS_581587','DS_683881','DS_654410','DS_778982','DS_484407','DS_618254')
GROUP BY ds.name, FROM_UNIXTIME(pv.ts/1000);