我有两个表,第一个表保存有关患者的数据,看起来像这样。
id patient sex impact
------------------------------------------
1 Bill Jones male .1
2 Sarah Smith female .4
第二个持有“乘数”。这些乘数将用于乘以上表中的影响。
id type type_value multiplier
-----------------------------------------------
1 patient Bill Jones .5
2 sex male .3
3 sex male .8
4 sex female .7
我正在尝试运行一个查询,该查询将返回以下内容:
patient patient_total sex_total new_impact
-------------------------------------------------------------------------------
Bill Jones .5 1.1 .16
Sarah Smith 0 .7 .28
新影响是每个患者的
(patient_total + sex_total) * impact
。这是create语句:
--
-- Table structure for table `impact`
--
CREATE TABLE IF NOT EXISTS `impact` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient` varchar(20) NOT NULL,
`sex` varchar(7) NOT NULL,
`impact` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `impact`
--
INSERT INTO `impact` (`id`, `patient`, `sex`, `impact`) VALUES
(1, 'Bill Jones', 'male', 0.1),
(2, 'Sarah Smith', 'female', 0.4);
--
-- Table structure for table `multipliers`
--
CREATE TABLE IF NOT EXISTS `multipliers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(20) NOT NULL,
`type_value` varchar(60) NOT NULL,
`multiplier` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `multipliers`
--
INSERT INTO `multipliers` (`id`, `type`, `type_value`, `multiplier`) VALUES
(1, 'patient', 'Bill Jones', 0.5),
(2, 'sex', 'male', 0.3),
(3, 'sex', 'male', 0.8),
(13, 'sex', 'female', 0.7);
我已经尝试了以下查询的多次迭代,但无法使其工作:/
更新的查询-适用于sex_total列,不适用于Patient_total列:/
select p.patient, ifnull(sum(ipatient.multiplier), 0) as patient_total, ifnull(sum(isex.multiplier), 0) as sex_total, (ifnull(sum(ipatient.multiplier), 0) + ifnull(sum(isex.multiplier), 0) * p.impact) as new_impact
from impact p
left outer join multipliers ipatient
on ipatient.type = 'patient' and ipatient.type_value = p.patient
left outer join multipliers isex
on isex.type = 'sex' and isex.type_value = p.sex
group by p.patient
有人可以帮忙吗?
谢谢
最佳答案
您只需要加入影响行,然后汇总结果即可。以下是两个连接,每种影响类型一个:
select p.patient, sum(ipatient.multiplier) as patient_total,
sum(isex.multiplier) as sex_total,
(sum(ipatient.multiplier) * sum(isex.multiplier) * p.imact
) as new_impact
from impact p left outer join
multipliers ipatient
on ipatient.type = 'patient' and ipatient.type_value = p.patient left outer join
multipliers isex
on isex.type = 'sex' and isex.type_value = p.sex
group by p.patient;
如果某些行可能与患者姓名或性别不匹配,则可能需要
coalesce()
。编辑:
哑。哑。哑。上面的操作失败,因为
isex
将行相乘,因此会影响i Patient的sum()
。此版本适用:select p.patient,
sum(case when m.type = 'patient' then m.multiplier else 0 end) as patient_total,
sum(case when m.type = 'sex' then m.multiplier else 0 end) as sex_total,
(sum(m.multiplier)* p.impact
) as new_impact
from impact p left outer join
multipliers m
on m.type = 'patient' and m.type_value = p.patient or
m.type = 'sex' and m.type_value = p.sex
group by p.patient;
您可以看到它here。
如果您不喜欢
float
中的所有小数位,则可以切换为数字/小数数据类型。关于mysql - MySQL,有条件地添加列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22516995/