详细信息:
lo means layout
lo_act_id ( activity id ), lo_time ( time spent ) , up_time and down_time , lo_description , lo_client_id
如果活动id<到99意味着的启动时间
如果活动id>99表示停机,但我有停机时间,我想看看它的注释/原因。
这是我使用的查询,它的工作原理很好,但我不知道如何获取和显示lo_description
SELECT * ,
SUM( IF( `lo_act_id` > 99 , lo_time , 0 )) AS down_time,
SUM( IF( `lo_act_id` < 99 , lo_time , 0 ,lo_desc )) AS up_time
FROM tbl_Layout
LEFT JOIN tbl_Client ON lo_client_id=client_code
GROUP BY lo_client_id
这就是我得到的
Client Spent Time Downtime
1 216 3
20 250 0
16 10 16
35 60 0
60 30 0
这就是我想要得到的:
Client Spend Time Downtime Note
1 216 3 Replace Network Card
20 250 0
16 10 16 Replace Main Board
Monthly Maintenance
Hurricane affect facilities
35 60 0
60 30 0
使用CAST
去掉"Min"answers"minutes"(假设此字段始终包含分钟,而不是秒或小时)。
使用GROUP_CONCAT
将描述粘贴在一起。
SELECT
tbl_Client.*,
SUM(IF(`lo_act_id` > 99, CAST(lo_time AS UNSIGNED), 0)) AS down_time,
SUM(IF(`lo_act_id` < 99, CAST(lo_time AS UNSIGNED), 0)) AS up_time,
GROUP_CONCAT(IF(`lo_act_id` > 99, lo_description, NULL) SEPARATOR "'n") AS note
FROM tbl_Layout
LEFT JOIN tbl_Client ON lo_client_id=client_code
GROUP BY lo_client_id