带有条件的MySQL中Row的值的总和

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了带有条件的MySQL中Row的值的总和，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3996字，纯文字阅读大概需要6分钟。

内容图文

我有下表.我想通过在日期上放置条件来对行中存在的值求和.

Table - a
  ID   Entry_date     weight  height   ID1      start_date  end_date
 111   2001-03-31      43      187
 111   2001-04-30      23      165
 111   2001-05-31      34      172     111      2001-04-30  2001-05-31 
 112   2001-06-30      54      183
 112   2001-07-31      26      188     112      2001-06-30  2001-07-31
 113   2001-04-30      23      165
 113   2001-05-31      34      172     113      2001-04-30  2001-05-31 
 114   2001-05-31      46      177
 114   2001-06-30      54      183
 114   2001-07-31      26      188     114      2001-06-30  2001-07-31

如果Entry_date> = start_date和Entry_date< = end_date,那么它应该对这些entry_dates和ID的权重和高度求和.我不知道我是否正确解释了.结果表应该是这样的：

Table - 

      ID   Entry_date     weight  height   ID1      start_date  end_date
     111   2001-03-31      43      187    
     111   2001-05-31      57      337     111      2001-04-30  2001-05-31 
     112   2001-07-31      80      371     112      2001-06-30  2001-07-31 
     113   2001-05-31      57      337     113      2001-04-30  2001-05-31 
     114   2001-05-31      46      177
     114   2001-07-31      80      371     114      2001-06-30  2001-07-31

我试过以下：

Select ID, Entry_date, sum(weight) as weight, sum(height) as height, ID1, start_date, end_date from table a 
where Entry_date >= start_date and Entry_date <= end_date group by ID, Entry_date;

但它没有给我想要的结果.它给了我以下结果：

  Table - a
          ID   Entry_date     weight  height   ID1      start_date  end_date
         111   2001-05-31      34      172     111      2001-04-30  2001-05-31 
         112   2001-07-31      26      188     112      2001-06-30  2001-07-31
         113   2001-05-31      34      172     113      2001-04-30  2001-05-31 
         114   2001-07-31      26      188     114      2001-06-30  2001-07-31

有谁能告诉我如何处理这种情况？

解决方法:

这有点复杂.这是您可以尝试的查询：

select
  id, entry_date,

  (select sum(weight) from test 
   where entry_date between a.start_date and a.end_date
   and id = a.id) as weight,

  (select sum(height) from test
   where entry_date between a.start_date and a.end_date
   and id = a.id) as height,

  id1, start_date, end_date
from test a
where start_date is not null and end_date is not null

union all

select a.* 
from test a
left join test b 
  on a.id = b.id
where 
  (a.start_date is null and a.end_date is null)
  and (b.start_date is not null and b.end_date is not null)
  and not a.entry_date between b.start_date and b.end_date

order by id, entry_date

UNION ALL子句上方的查询将提取对重量和高度进行数学运算的记录. UNION ALL子句下面的查询仅关注不在所需范围内且具有空日期的记录.您可以轻松调整它.

示例：http://sqlfiddle.com/#!9/f9b54a/6

查询相同信息的备用方法

select e.id, 
    max(entry_date) as entry_date, sum(weight) as weight, sum(height) as height,
    em.start_date, em.end_date
from test e
inner join (
  select distinct id, start_date, end_date
  from test
  where start_date is not null and end_date is not null) em
  on em.id = e.id
  and e.entry_date between em.start_date and em.end_date
group by e.id, em.start_date, em.end_date

union all

select e.id, entry_date, weight, height, em.start_date, em.end_date
from test e
inner join (
  select distinct id, start_date, end_date
  from test
  where start_date is not null and end_date is not null) em
    on em.id = e.id
    and not e.entry_date between em.start_date and em.end_date

order by id, entry_date

示例：http://sqlfiddle.com/#!9/f9b54a/8

更好的存储数据的方法

create table entrymaster (
  id int,
  start_date date,
  end_date date
);

create table entries (
  id int,
  entry_date date,
  weight int,
  height int
);

select e.id, 
    max(entry_date) as entry_date, sum(weight) as weight, sum(height) as height,
    em.start_date, em.end_date
from entries e
inner join entrymaster em
  on em.id = e.id
  and e.entry_date between em.start_date and em.end_date
group by e.id, em.start_date, em.end_date

union all

select e.id, entry_date, weight, height, em.start_date, em.end_date
from entries e
inner join entrymaster em
    on em.id = e.id
    and not e.entry_date between em.start_date and em.end_date

order by id, entry_date

示例：http://sqlfiddle.com/#!9/7eaaa/3

内容总结

以上是互联网集市为您收集整理的带有条件的MySQL中Row的值的总和全部内容，希望文章能够帮你解决带有条件的MySQL中Row的值的总和所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端