mysql 动态日期_mySQL pivot动态日期表

朴灿烈づ我的快乐病毒、 2023-01-12 07:41 150阅读 0赞

我正在运行我的查询

SELECT

DATE\_FORMAT(FROM\_DAYS(TO\_DAYS(st\_day\_request) -MOD(TO\_DAYS(st\_day\_request) -2, 7)), '%d-%m-%Y') as DATE,

tx\_report\_id AS 'BUSINESS',

CONCAT(ROUND(AVG(DATEDIFF(dt\_dossier\_sent,st\_day\_request) > -5 AND (main.dt\_dossier\_sent IS NOT NULL AND main.dt\_dossier\_sent <> '0000-00-00')) \* 100),'%') as DISPATCHED\_5\_AND\_MORE,

CONCAT(ROUND(AVG(DATEDIFF(dt\_dossier\_sent,st\_day\_request) < -5 AND DATEDIFF(dt\_dossier\_sent,st\_day\_request) <= 1 AND (main.dt\_dossier\_sent IS NOT NULL AND main.dt\_dossier\_sent <> '0000-00-00')) \* 100),'%') as DISPATCHED\_4\_TO\_1,

CONCAT(ROUND(AVG(DATEDIFF(dt\_dossier\_sent,st\_day\_request) > 1 AND DATEDIFF(dt\_dossier\_sent,st\_day\_request) <= 10 AND (main.dt\_dossier\_sent IS NOT NULL AND main.dt\_dossier\_sent <> '0000-00-00')) \* 100),'%') as DISPATCHED\_2\_TO\_10,

CONCAT(ROUND(AVG(DATEDIFF(dt\_dossier\_sent,st\_day\_request) > 10 AND (main.dt\_dossier\_sent IS NOT NULL AND main.dt\_dossier\_sent <> '0000-00-00')) \* 100),'%') as DISPATCHED\_10\_AND\_MORE,

CONCAT(ROUND(AVG(main.dt\_dossier\_sent IS NULL OR main.dt\_dossier\_sent = '0000-00-00') \* 100),'%') as OUTSTANDING FROM

(

SELECT

app\_contract.id\_app,

app\_contract.dt\_dossier\_sent,

contract.tx\_report\_id,

contract.st\_day\_request

FROM

contract

INNER JOIN app\_contract ON app\_contract.id\_contract = contract.id\_contract AND contract.st\_day\_request > '2017-09-01' AND contract.id\_client = 657

INNER JOIN app\_hist ON app\_hist.id\_app=app\_contract.id\_app

GROUP BY app\_contract.id\_app

) as main GROUP BY DATE ORDER BY DATE\_FORMAT(st\_day\_request, '%y') ASC, DATE\_FORMAT(st\_day\_request, '%m') ASC并在下面的图片中获取结果：

![OoxX1.png][]

到此为止，一切都是正确的。我遇到的问题是，我想更改(透视/交叉表)结果，并将我获得的日期列为Columns，如下图所示：

![wwnRl.png][]

我尝试了几天，但不幸的是没有结果。有任何想法吗？

PS。如果您无法正常看到图像，请右键单击并在新标签中打开。然后你可以放大它们。对不起，对于图片，我会复制并粘贴结果到我的下一篇文章中。

[OoxX1.png]: https://i.stack.imgur.com/OoxX1.png
[wwnRl.png]: https://i.stack.imgur.com/wwnRl.png