Mysql 数据库列转行
BEGIN
if MINUTE(NEW.Date_Time) % 5 = 0 and SECOND(NEW.Date_Time) = 0
then
if (NEW.Factor_Name = "PH") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("PH"),
round(avg(case when Factor_Name='PH' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "水温") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("水温"),
round(avg(case when Factor_Name='水温' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "浊度") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("浊度"),
round(avg(case when Factor_Name='浊度' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "溶解氧") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("溶解氧"),
round(avg(case when Factor_Name='溶解氧' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "电导率") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("电导率"),
round(avg(case when Factor_Name='电导率' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "COD") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("COD"),
round(avg(case when Factor_Name='COD' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
if (NEW.Factor_Name = "氨氮") then
insert into pwkdatamin (
select CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time/5)) * 5) AS CHAR),2,'0')) as 日期,
max("氨氮"),
round(avg(case when Factor_Name='氨氮' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag
from pwkdata where Date_Time >= DATE_SUB(NEW.Date_Time,INTERVAL 5 MINUTE) and Date_Time < NEW.Date_Time
GROUP BY CONCAT(DATE_FORMAT(NEW.Date_Time,'%Y-%m-%d %H:'), LPAD(CAST( (floor(MINUTE(NEW.Date_Time)/5) * 5) AS CHAR),2,'0')));
end if;
end if;
END
select DATE_FORMAT(Date_Time,'%Y-%m-%d %H') as 日期,
round(avg(case when Factor_Name='PH' then Factor_Data end),2) as PH,
case
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='PH' then Factor_Flag end) like "%N%" then "N"
end as PH_Flag,
round(avg(case when Factor_Name='水温' then Factor_Data end),2) as 水温,
case
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='水温' then Factor_Flag end) like "%N%" then "N"
end as 水温_Flag,
round(avg(case when Factor_Name='浊度' then Factor_Data end),2) as 浊度,
case
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='浊度' then Factor_Flag end) like "%N%" then "N"
end as 浊度_Flag,
round(avg(case when Factor_Name='溶解氧' then Factor_Data end),2) as 溶解氧,
case
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='溶解氧' then Factor_Flag end) like "%N%" then "N"
end as 溶解氧_Flag,
round(avg(case when Factor_Name='电导率' then Factor_Data end),2) as 电导率,
case
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='电导率' then Factor_Flag end) like "%N%" then "N"
end as 电导率_Flag,
round(avg(case when Factor_Name='COD' then Factor_Data end),2) as COD,
case
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='COD' then Factor_Flag end) like "%N%" then "N"
end as COD_Flag,
round(avg(case when Factor_Name='氨氮' then Factor_Data end),2) as 氨氮,
case
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%B%" then "B"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%T%" then "T"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%C%" then "C"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%D%" then "D"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%S%" then "S"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%M%" then "M"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%F%" then "F"
when GROUP_CONCAT(distinct case when Factor_Name='氨氮' then Factor_Flag end) like "%N%" then "N"
end as 氨氮_Flag
from pwkdata
GROUP BY DATE_FORMAT(Date_Time,'%Y-%m-%d %H')
ACCESS数据库 列转行
SELECT DataTime ,MAX(iif(Factor = "PH值", Avg)) AS kn1 from db_1Minute202403 group by DataTime