1. 根据条件进行数据表的字段更新:
UPDATE bp_pre_and_true_value
SET is_check = (
CASE WHEN DBP>70 AND DBP<90 THEN
1
ELSE 0
END
)
不要忘记加end结束
2. 根据条件联表更新数据表的一列
UPDATE bp_pre_and_true_value AS a,
bp_feature_data AS b
SET a.id = b.id
WHERE
a.Age = b.Age
AND a.AS_ = b.AS_
AND a.BMI = b.BMI
AND a.DS = b.DS
AND a.H = b.H
AND a.Hr = b.Hr
AND a.info = b.info
AND a.rate_A=b.rate_A
AND a.T =b.T
AND a.t1=b.t1
AND a.Sex=b.Sex
3.让自增的id列重新从1开始计算
truncate table bp_pre_and_true_value
4.批量插入数据
INSERT INTO bp_feature_data_20 (
SBP,
DBP,
Hr,
Time,
Sex,
Age,
BMI,
Height,
Weight,
T,
H,
t1,
rate_A,
AS_,
DS,
info,
id,
insert_time,
id_index
) SELECT
SBP,
DBP,
Hr,
Time,
Sex,
Age,
BMI,
Height,
Weight,
T,
H,
t1,
rate_A,
AS_,
DS,
info,
id,
insert_time,
id_index
FROM
bp_feature_data AS c
WHERE
c.id_index IN (
SELECT DISTINCT
b.id_index AS A
FROM
bp_feature_data AS a,
bp_feature_data AS b
WHERE
a.id = b.id
AND a.SBP - b.SBP > 20
)
5. MYSQL将查询的结果加上编号,并把时间戳转换为日期:
SELECT
(@i :=@i + 1) i,
a.heartrate,
FROM_UNIXTIME(
a.insert_time,
'%Y-%m-%d %H:%i:%S'
)
FROM
jl_t3_data AS a,
(SELECT @i := 0) AS it
WHERE
a.user_id = '12844'
AND a.heartrate != 0;