如何编辑mysql请求max case输出？

kmpatx3s 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(308)

当我运行以下查询时。

SELECT tblhostingaddons.id as id, 
MAX(CASE WHEN (tblcustomfields.fieldname = 'boyut') THEN tblcustomfields.fieldoptions ELSE NULL END) AS quota,
MAX(CASE WHEN (tblcustomfields.fieldname = 'adet') THEN tblcustomfields.fieldoptions ELSE NULL END) AS pc,
MAX(CASE WHEN (tblcustomfields.fieldname = 'gonderimlimiti') THEN tblcustomfields.fieldoptions ELSE NULL END) AS send,
MAX(CASE WHEN (tblcustomfields.fieldname = 'turu') THEN tblcustomfields.fieldoptions ELSE NULL END) AS type 
FROM `tblhostingaddons` 
LEFT JOIN tbladdons ON tbladdons.id = tblhostingaddons.addonid 
LEFT JOIN tblcustomfields ON tblcustomfields.relid = tbladdons.id 
WHERE tblhostingaddons.hostingid = '88'
AND tblhostingaddons.status = 'Active' 
GROUP BY tblhostingaddons.id 
ORDER BY tblhostingaddons.id

结果如下。但我只想让它的类型是epostapro。

id quota pc     send type
74 5120   1     200  epostapro
75 NULL   NULL  NULL NULL

mysql

来源：https://stackoverflow.com/questions/52440459/how-do-i-edit-my-mysql-request-max-case-outputs

1条答案

按热度按时间

ctrmrzij1#

尝试使用having子句：

SELECT tblhostingaddons.id as id, 
MAX(CASE WHEN (tblcustomfields.fieldname = 'boyut') THEN tblcustomfields.fieldoptions ELSE NULL END) AS boyut,
MAX(CASE WHEN (tblcustomfields.fieldname = 'adet') THEN tblcustomfields.fieldoptions ELSE NULL END) AS adet,
MAX(CASE WHEN (tblcustomfields.fieldname = 'gonderimlimiti') THEN tblcustomfields.fieldoptions ELSE NULL END) AS gonderimlimiti,
MAX(CASE WHEN (tblcustomfields.fieldname = 'turu') THEN tblcustomfields.fieldoptions ELSE NULL END) AS turu 
FROM `tblhostingaddons` 
LEFT JOIN tbladdons ON tbladdons.id = tblhostingaddons.addonid 
LEFT JOIN tblcustomfields ON tblcustomfields.relid = tbladdons.id 
WHERE tblhostingaddons.hostingid = '88'
AND tblhostingaddons.status = 'Active' 
GROUP BY tblhostingaddons.id 
having MAX(CASE WHEN (tblcustomfields.fieldname = 'turu') THEN tblcustomfields.fieldoptions ELSE NULL END)='epostapro'
ORDER BY tblhostingaddons.id

或者您可以从选择列表中删除id，也可以按以下方式分组：

SELECT  
MAX(CASE WHEN (tblcustomfields.fieldname = 'boyut') THEN tblcustomfields.fieldoptions ELSE NULL END) AS boyut,
MAX(CASE WHEN (tblcustomfields.fieldname = 'adet') THEN tblcustomfields.fieldoptions ELSE NULL END) AS adet,
MAX(CASE WHEN (tblcustomfields.fieldname = 'gonderimlimiti') THEN tblcustomfields.fieldoptions ELSE NULL END) AS gonderimlimiti,
MAX(CASE WHEN (tblcustomfields.fieldname = 'turu') THEN tblcustomfields.fieldoptions ELSE NULL END) AS turu 
FROM `tblhostingaddons` 
LEFT JOIN tbladdons ON tbladdons.id = tblhostingaddons.addonid 
LEFT JOIN tblcustomfields ON tblcustomfields.relid = tbladdons.id 
WHERE tblhostingaddons.hostingid = '88'
AND tblhostingaddons.status = 'Active'

赞(0）回复(0）举报 2021-06-20

我来回答

如何编辑mysql请求max case输出？

1条答案

相关问题

热门标签

最新问答