本质上，我正在尝试从表中添加一些字段并重生另一个没有运气的表。
初始表如下所示：
ID STATUS Min Max Mean TimeStamp
1 Motor1-ON 10 20 15 12/01/2013 05:00
2 Motor1-OFF 5 7 6 12/01/2013 05:00
3 Motor1- HOLD 1 3 2 12/01/2013 05:00
4 Motor2-ON 8 12 10 12/01/2013 05:00
5 Motor2-OFF 4 6 5 12/01/2013 05:00
6 Motor2-HOLD 1 5 3 12/01/2013 05:00
7 Motor1-ON 11 21 16 12/01/2013 05:10
8 Motor1-OFF 5 7 6 12/01/2013 05:10
9 Motor1- HOLD 1 3 2 12/01/2013 05:10
10 Motor2-ON 9 10 9.5 12/01/2013 05:10
11 Motor2-OFF 5 7 6 12/01/2013 05:10
12 Motor2-HOLD 1 5 3 12/01/2013 05:10
. . . . . .
. . . . . .
. . . . . .
有两种类型的状态：电动机1和电动机2，时间戳每10分钟递增一次。我试图在第一个表的基础上创建一个新表，以便为每个时间戳添加Motor 1和Motor2的3种状态。我要实现的表（到目前为止没有运气）应该看起来像；
Timestamp Total_ON Total_ON Total_ON Total_OFF Total_OFF Total_OFF
_Min _Max _Mean _Min _Max _Mean
12/01/2013 05:00 18 32 35 9 13 11
12/01/2013 05:10 20 31 25.5 10 14 12
. . . . . .
. . . . . .
（该表继续包含Total_Hold_Min，Total_Hold_Max，Total_Hold_Mean）
哪里
Total_ON_Min = Motor 1_ON Min + Motor 2_ON Min (For its given Timestamp)
Total_ON_Max = Motor1_ON Max + Motor2_OFF Max (For its given Timestamp)
Total_ON_Mean = Motor1_ON Mean + Motor2_OFF Mean (For its given Timestamp)
等等…。
任何人都可以在正确的mysql语句上提供任何建议，以实现这一目标。我一直在尝试实现CrossTab并没有成功。使用select语句和sum汇总函数分别计算motor1和motor 2的单个总数没问题，但是在每个时间戳中添加两个字段我没有成功
当我尝试建立自己的知识库时，任何想法或相关链接都将不胜感激
我是mysql新手
谢谢
附加信息：
电机状态非常独立且静态，因此我无法对其进行细分。数据透视表失去了我所指出的灵活性。我根据给定的想法提出了一个部分解决方案，该解决方案总共适用于Min。
SELECT
`timestamp`,
status,
SUM(Min) AS `Total_ON_MIN` FROM Tablename where Status IN('Motor1_on','Motor2_on')
FROM table
GROUP BY `timestamp`
我无法使用这种方法来引入“ Total_Max_ON”等。在按时间戳分组时出错。
SELECT
`timestamp`,
status,
(SELECT SUM(Min) AS `Total_ON_MIN` FROM Tablename where Status IN('Motor1_on','Motor2_on')),
(SELECT SUM(Max) AS `Total_ON_MAX` FROM Tablename where Status IN('Motor1_on','Motor2_on')),
(SELECT SUM(Mean) AS `Total_ON_MEAN` FROM Tablename where Status IN('Motor1_on','Motor2_on'))
GROUP BY `timestamp`
有任何进一步指导的想法。
您应该强烈考虑将电动机标识符（即电动机1，电动机2）和状态指示器（开/关/保持）分成两个单独的字段。
然后，您可以轻松查询
SELECT
`timestamp`,
SUM(CASE WHEN status = 'ON' THEN Min ELSE 0 END CASE) AS `ON_Min`,
SUM(CASE WHEN status = 'ON' THEN Max ELSE 0 END CASE) AS `ON_Max`,
SUM(CASE WHEN status = 'ON' THEN Mean ELSE 0 END CASE) AS `ON_Mean`,
SUM(CASE WHEN status = 'OFF' THEN Min ELSE 0 END CASE) AS `OFF_Min`,
SUM(CASE WHEN status = 'OFF' THEN Max ELSE 0 END CASE) AS `OFF_Max`,
SUM(CASE WHEN status = 'OFF' THEN Mean ELSE 0 END CASE) AS `OFF_Mean`
FROM table
GROUP BY `timestamp`
ORDER BY `timestamp` ASC
或者，如果您真的不需要支点，可以做
SELECT
`timestamp`,
status,
SUM(Min) AS `Min_Sum`,
SUM(Max) AS `Max_Sum`,
SUM(Mean) AS `Mean_Sum`
FROM table
GROUP BY `timestamp`, status
ORDER BY `timestamp` ASC, status ASC
或者如果您需要通过电动机分解
SELECT
`timestamp`,
motor,
status,
SUM(Min) AS `Min_Sum`,
SUM(Max) AS `Max_Sum`,
SUM(Mean) AS `Mean_Sum`
FROM table
GROUP BY `timestamp`, motor, status
ORDER BY `timestamp` ASC, motor ASC, status ASC
您会发现查询电动机和状态是否中断时，可以灵活得多地进行查询。
如果您绝对不能将状态分解为Motor和status，那么我可以执行以下操作：
SELECT
`timestamp`,
SUM(CASE WHEN status LIKE '%ON' THEN Min ELSE 0 END CASE) AS `ON_Min`,
SUM(CASE WHEN status LIKE '%ON' THEN Max ELSE 0 END CASE) AS `ON_Max`,
SUM(CASE WHEN status LIKE '%ON' THEN Mean ELSE 0 END CASE) AS `ON_Mean`,
SUM(CASE WHEN status LIKE '%OFF' THEN Min ELSE 0 END CASE) AS `OFF_Min`,
SUM(CASE WHEN status LIKE '%OFF' THEN Max ELSE 0 END CASE) AS `OFF_Max`,
SUM(CASE WHEN status LIKE '%OFF' THEN Mean ELSE 0 END CASE) AS `OFF_Mean`
FROM table
GROUP BY `timestamp`
ORDER BY `timestamp` ASC
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句