是否可以在一个 GROUP BY 中有多个 CASE，使其根本不分组？

Question

我正在尝试查询数据，在特殊情况下，我可能不得不按多个值对数据进行分组。但大多数时候，它根本不需要分组。所以我在 GROUP BY 中使用了多个 CASE WHEN {...}，并且所有 WHEN 基本上都有相同的条件。问题是，如果满足条件，一切正常。但如果它为 false，则 GROUP BY 部分为空，查询仅返回第一行。

我基本上试图以我想到的各种方式重新组织问题，似乎没有任何效果，我在互联网上也没有找到任何结论。

我正在使用MySql 5.7。

SELECT
{element I want to select}
FROM
{tables}
WHERE
{conditions}
GROUP BY
CASE WHEN (condition) THEN [table].[column] END,
CASE WHEN (condition) THEN [table].[column] END,
CASE WHEN (condition) THEN [table].[column] END
ORDER BY
{...}

完整查询 ：

SELECT
tx.code,
IFNULL(hr.label,'')                         AS rh_label,
IFNULL(cli.label,'')                        AS client_label,
DATE(FROM_UNIXTIME(created.value / 1000))   AS Created,
IFNULL(item_enfant.label,'')                As Parasite,
IFNULL(item_parent.label,'')                As Zone,
CASE 
WHEN :perWeek = 'week' THEN SUM(qte.value)
ELSE qte.value
END AS Quantite,
CEILING(DATEDIFF(DATE(FROM_UNIXTIME(created.value / 1000)), DATE(FROM_UNIXTIME(:from / 1000))) / 7) AS Weeks
FROM tx
LEFT JOIN tx_type           AS tt ON tt.id = tx.tx_type_id
LEFT JOIN human_resource    AS hr ON hr.id = tx.human_resource_id
LEFT JOIN client            AS cli ON cli.id = tx.client_id
LEFT JOIN tx_state          AS ts ON ts.id = tx.current_tx_state_id
LEFT JOIN workflow_step     AS ws ON ws.id = ts.workflow_step_id
LEFT JOIN item              AS item_enfant ON item_enfant.item_list_id = tx.item_list_id
JOIN item_type              AS ite ON ite.id = item_enfant.item_type_id
LEFT JOIN item_meta         AS qte ON qte.item_id = item_enfant.id AND qte.name = 'qtePourRapport'
LEFT JOIN item_prop         AS created ON created.item_id = item_enfant.id AND created.name = 'visite.timestamp'
JOIN item                   AS item_parent ON item_parent.id = item_enfant.parent_item_id 
JOIN item_type              AS itp ON itp.id = item_parent.item_type_id
WHERE
ite.name = 'parasite' AND
item_enfant.product_id IN (:parasiteIds) AND
itp.name = 'zone' AND
item_parent.product_id IN (:zoneIds) AND
cli.id = (:clientId) AND 
ws.logic_id = 600 AND 
created.value BETWEEN :from AND :to AND 
created.value IS NOT NULL AND qte.value IS NOT NULL
GROUP BY
CASE WHEN :perWeek = 'week' THEN item_enfant.label END, #Parasite
CASE WHEN :perWeek = 'week' THEN item_parent.label END, #Zone
CASE WHEN :perWeek = 'week' THEN CEILING(DATEDIFF(DATE(FROM_UNIXTIME(created.value / 1000)), DATE(FROM_UNIXTIME(:from / 1000))) / 7) END #Weeks
ORDER BY 
Created;

我只得到第一行的数据。而且我实际上不知道如果不满足条件，如何获得它只是不分组。

Answer 1

聚合需要一个唯一值或两个单独的查询。 最简单的方法可能是union all：

select . . .
from t
where <conditions not to group by>
union all
select . . .
from t
where <conditions to group by>
group by . . .;

您需要确保每个子查询都返回兼容的列。

Answer 2

SELECT
{element I want to select}
FROM
{tables}
WHERE
{conditions}
GROUP BY
CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END,
CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END,
CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END
ORDER BY
{...}

我想缺少ELSE条款的评估结果会NULL.这是常量，因此所有行将在同一组中，因此该组将只返回一行。为了避免分组，您需要在分组元素组合中所有返回的行上具有唯一值(而不是在每个分组元素中，如 erlier 所述(。

编辑

因此，评论中的灵魂可能更容易：只需添加另一个分组元素CASE WHEN !(condition) THEN CONCAT([different elements making it unique]) END