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

I have the following table. I want to sum the values present in rows by putting condition on dates.

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

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

If Entry_date >= start_date and Entry_date <= end_date then it should sum weight and height for those entry_dates and ID. I don't know if i explained it right way or not. Resulting table should be like this:

如果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

I tried following:

我试过以下:

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;

But it is not giving me desired result. It is giving me following results:

但它没有给我想要的结果。它给了我以下结果:

  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

Can anyone please tell me how to proceed in this case?

任何人都可以告诉我如何处理这种情况?

2 个解决方案

#1

This was a little complicated. Here's the query you could try:

这有点复杂。这是您可以尝试的查询:

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

The query above the UNION ALL clause will pull records that does math on weight and height. The query below the UNION ALL clause only focuses on records that do not fall within the desired range and has null dates. You can tweak it to your comfort.

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

Example: http://sqlfiddle.com/#!9/f9b54a/6

Alternate method to query the same information

查询相同信息的备用方法

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

Example: http://sqlfiddle.com/#!9/f9b54a/8

Better method to store data

更好的存储数据的方法

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

Example: http://sqlfiddle.com/#!9/7eaaa/3

2 个解决方案

#1

更多相关文章

随机推荐