从本质上讲，我试图从一个表中添加一些字段，然后重新生成另一个表，但没有成功。
初始表格如下所示；
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分钟递增一次。我正试图在第一个表的基础上创建一个新的表，以便为每个时间戳添加Motor1和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和motor2的单个合计没有问题，但在每个时间戳中添加两个字段我没有成功
任何想法或相关链接都非常感谢，因为我正试图建立在我的知识库上
我是mysql 的新手
感谢
附加信息：
马达的状态是非常独立和静态的，所以我不能分解它。如前所述，数据透视表失去了我所需要的灵活性。我提出了一个基于给定想法的零件解决方案，该解决方案总共适用于最小
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
你可以看到，如果你分解了电机和状态，你可以更灵活地查询。
如果你绝对不能把状态分解为马达和状态，那么我可能会做以下事情：
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