gpt4 book ai didi

MySql 条件分组依据

转载 作者:行者123 更新时间:2023-11-29 04:28:15 25 4
gpt4 key购买 nike

请考虑我的表(这只是一个简化版本,在我的项目中,我有 600,000+ 条记录):

Id  TransactionId   TransactionTypeId   Description
1 1 1 Description1
2 1 1 Description2
3 1 2 Description3
4 1 2 Description4
5 1 1 Description5
6 1 2 Description6
7 2 1 Description7
8 2 1 Description8
9 2 2 Description9
10 2 2 Description10

我需要做的是什么时候TransactionTypeId = 1 ,我需要从该表中获取最新数据。否则,当 TransactionTypeId <> 1 , 我需要把它们全部拿走。

对于这个例子,我有这个查询:

SELECT MAX(T.Id)
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionId ORDER BY T.Id DESC), ',', 1) AS TransactionId
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionTypeId ORDER BY T.Id DESC), ',', 1) AS TransactionTypeId
, SUBSTRING_INDEX(GROUP_CONCAT(T.Description ORDER BY T.Id DESC), ',', 1) AS Description
FROM Transactions T
GROUP BY T.TransactionId
, CASE WHEN T.TransactionTypeId = 1 THEN T.TransactionTypeId END
ORDER BY T.TransactionId, T.TransactionTypeId

我想要的结果是:

Id  TransactionId   TransactionTypeId   Description
5 1 1 Description5
3 1 2 Description3
4 1 2 Description4
6 1 2 Description6
8 2 1 Description8
9 2 2 Description9
10 2 2 Description10

但我的问题是,即使 TransactionTypeId <> 1 ,查询仍然对它们进行分组。

我的查询返回:

Id  TransactionId   TransactionTypeId   Description
5 1 1 Description5
6 1 2 Description6
8 2 1 Description8
10 2 2 Description10

我知道我可以使用 UNION在这里,分离对 TransactionTypeId 的查询列,但我不能,我花了 5 分钟多才得到结果。是否有任何可能(如果没有,我别无选择,只能使用 UNION )的方法来解决这个问题?

谢谢:)

UPDATE #1

这是我使用 UNION 时的查询.

SELECT Transaction.Id
, Transaction.TransactionId
, Transaction.TransactionTypeId
, Transaction.Description
FROM (
SELECT MAX(T.Id)
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionId ORDER BY T.Id DESC), ',', 1) AS TransactionId
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionTypeId ORDER BY T.Id DESC), ',', 1) AS TransactionTypeId
, SUBSTRING_INDEX(GROUP_CONCAT(T.Description ORDER BY T.Id DESC), ',', 1) AS Description
FROM Transactions T
WHERE T.TransactionTypeId = 1
GROUP BY T.TransactionId

UNION

SELECT T.Id
, T.TransactionId
, T.TransactionTypeId
, T.Description
FROM Transactions T
WHERE T.TransactionTypeId <> 1
) Transaction
ORDER BY Transaction.TransactionId, Transaction.TransactionTypeId

最佳答案

试试这个

    SELECT MAX(T.Id)
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionId ORDER BY T.Id DESC), ',', 1) AS TransactionId
, SUBSTRING_INDEX(GROUP_CONCAT(T.TransactionTypeId ORDER BY T.Id DESC), ',', 1) AS TransactionTypeId
, SUBSTRING_INDEX(GROUP_CONCAT(T.Description ORDER BY T.Id DESC), ',', 1) AS Description
FROM Transactions T
GROUP BY CASE WHEN T.TransactionTypeId = 1 THEN CONCAT(T.TransactionId,'-',T.TransactionTypeId) ELSE T.Id END
ORDER BY T.TransactionId, T.TransactionTypeId

关于MySql 条件分组依据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7037543/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com