mysql - 如何用多次出现的模式填充 varchar

Question

我使用 GROUP_CONCAT 在一行中检索每个玩家的前五个交易数据，但由于我的最终目标是以 csv 格式导出此数据，所以我希望在末尾有准确的列数。如何连接特定数量的模式以始终获得 5 列？在我的例子中，我需要 5 列用于金额，5 列用于发行日期，依此类推。

这是原始查询:

SELECT
p.id,  
GROUP_CONCAT(t1.amount SEPARATOR "|") as amounts,  
GROUP_CONCAT(t1.issueDate SEPARATOR "|") as issueDates,   
GROUP_CONCAT((select t2.amountInEuro*100/t1.amountInEuro from Transaction t2 where t2.type = 3 and t2.id = t1.deposit_id) SEPARATOR "|") as bonusAmounts 
FROM Transaction t1 join Player p on p.id = t1.player_id 
WHERE   
t1.type = 0 and   
t1.status = 0 and   
exists (select 1 from Transaction tr where tr.issueDate between '2010-07-03 00:00:00' and '2013-07-03 23:59:59' and tr.player_id = t1.player_id) 
GROUP BY t1.player_id 
HAVING count(*) <= 5

Answer 1

大概是这样

SELECT id,
    CONCAT(amounts, REPEAT('|', 5 - GroupCount)) AS amounts,
    CONCAT(issueDates, REPEAT('|', 5 - GroupCount)) AS issueDates,
    CONCAT(bonusAmounts, REPEAT('|', 5 - GroupCount)) AS bonusAmounts
FROM
(
    SELECT
        p.id,  
        GROUP_CONCAT(t1.amount SEPARATOR "|") as amounts,  
        GROUP_CONCAT(t1.issueDate SEPARATOR "|") as issueDates,   
        GROUP_CONCAT(t2.amountInEuro*100/t1.amountInEuro SEPARATOR "|") as bonusAmounts,
        COUNT(*) AS GroupCount 
    FROM Transaction t1 
    JOIN Player p 
    ON p.id = t1.player_id 
    INNER JOIN (SELECT player_id, COUNT(*) FROM Transaction WHERE issueDate BETWEEN '2010-07-03 00:00:00' AND '2013-07-03 23:59:59' GROUP BY player_id) tr
    ON tr.player_id = t1.player_id
    LEFT OUTER JOIN Transaction t2
    ON t2.id = t1.deposit_id AND type = 3
    WHERE t1.type = 0 
    and t1.status = 0 
    GROUP BY t1.player_id 
    HAVING GroupCount <= 5
) Sub1

基本上按现在的样子返回数据，但加上计数，然后连接重复数量的管道，使其达到 5 组