gpt4 book ai didi

mysql查询需要不同的结构

转载 作者:行者123 更新时间:2023-11-29 23:10:10 27 4
gpt4 key购买 nike

我希望我能够正确地解释这一点。以下 SQL 生成如下表

+++++++++++++++++++++++++++++++++++++
FQ | <0 | 8-14 | 15-21 | 22-28 | >28
+++++++++++++++++++++++++++++++++++++
A | 4 | 25 | 65 | 65 | 0
____________________________________
B | 4 | 25 | 65 | 65 | 0
____________________________________
C | 4 | 25 | 65 | 65 | 0
____________________________________

不,这就是我需要做的,但不确定是否可能。

+++++++++++++++++++++++++
A | B | C |
+++++++++++++++++++++++++
<0 | 4 | 4 | 4 |
_________________________
8-14 | 25 | 25 | 25 |
_________________________
15-21 | 65 | 65 | 65 |
_________________________
22-28 | 65 | 65 | 65 |
_________________________
>28 | 0 | 0 | 0 |

这种替代方案也有效。

++++++++++++++++++++++++++++++++
FQ | OverDueRange | DaysOverDue
++++++++++++++++++++++++++++++++
A | <0 | 4 |
_________________________
A | 8-14 | 25 |
_________________________
A | 15-21 | 65 |
_________________________
A | 22-28 | 65 |
_________________________
A | >28 | 0 |
_________________________
B | <0 | 4 |
_________________________
B | 8-14 | 25 |
_________________________
B | 15-21 | 65 |
_________________________
B | 22-28 | 65 |
_________________________
C | >28 | 0 | etc etc etc

SELECT
m.`FQ`,
COUNT(a.`ID`) AS `< 0`,
COUNT(b.`ID`) AS `0-7`,
COUNT(c.`ID`) AS `8-14`,
COUNT(d.`ID`) AS `15-21`,
COUNT(e.`ID`) AS `22-28`,
COUNT(f.`ID`) AS `> 28`
FROM
`rhi_sap`.`dispatch report` m
LEFT JOIN
`rhi_sap`.`dispatch report` a ON a.`RHI_TargetShip_Date` < CURDATE()
AND m.`ID` = a.`ID`
LEFT JOIN
`rhi_sap`.`dispatch report` b ON b.`RHI_TargetShip_Date` > ADDDATE(CURDATE(), - 1)
AND b.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 8)
AND m.`ID` = b.`ID`
LEFT JOIN
`rhi_sap`.`dispatch report` c ON c.`RHI_TargetShip_Date` > ADDDATE(CURDATE(), 7)
AND c.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 15)
AND m.`ID` = c.`ID`
LEFT JOIN
`rhi_sap`.`dispatch report` d ON d.`RHI_TargetShip_Date` > ADDDATE(CURDATE(), 14)
AND d.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 22)
AND m.`ID` = d.`ID`
LEFT JOIN
`rhi_sap`.`dispatch report` e ON e.`RHI_TargetShip_Date` > ADDDATE(CURDATE(), 21)
AND e.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 29)
AND m.`ID` = e.`ID`
LEFT JOIN
`rhi_sap`.`dispatch report` f ON e.`RHI_TargetShip_Date` > ADDDATE(CURDATE(), 28)
AND m.`ID` = f.`ID`
WHERE
m.`RHI_Freq_Class` IS NOT NULL
GROUP BY m.`RHI_Freq_Class`;

最佳答案

如果你可以在没有累积值(value)的情况下生活,那么我认为这样的事情会起作用:

SELECT (case when m.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 28) then '28+'
when m.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 22) then '22-28'
when m.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 15) then '15-21'
when m.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 8) then '8-14'
when m.`RHI_TargetShip_Date` < ADDDATE(CURDATE(), 1) then '1-8'
else '0'
end) as grp,
sum(FQ = 'A') as A,
sum(FQ = 'B') as B,
sum(FQ = 'C') as C
FROM rhi_sap.dispatch report m
WHERE m.`RHI_Freq_Class` IS NOT NULL
GROUP BY grp
ORDER BY MIN(m.`RHI_TargetShip_Date`)

关于mysql查询需要不同的结构,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28116967/

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