开发者

How to add row values based on indicator within single row

Quick example to get the question started:

Temporary Table Example

ID |  value   |  Flag  | parent
-------------------------------
1  |  12.50   |   1    |   19
2  |  13.50   |   1    |   20
3  |  14.50   |   2    |   1 
4  |  15.50   |   1    |   21   
5  |  16.50   |   1    |   22
6  |  17.50   |   1    |   23
7  |  18.50   |   2    |   2
8  |  19.50   |   1    |   24 

ultimately, I want to search a开发者_StackOverflow table, for any ID with a flag of 2, take that value and add it to the associated parent ID.

The ideal results would return:

ID |  value   |  Flag  | parent
-------------------------------
1  |    27    |   1    |   19
2  |    32    |   1    |   20
4  |  15.50   |   1    |   21   
5  |  16.50   |   1    |   22
6  |  17.50   |   1    |   23
8  |  19.50   |   1    |   24 

where 3 and 7 were removed, with their values added to their parent value (1 and 2 respectively). Keep in mind the parent field is already being pulled from an Inner JOIN with another table (the table above is generated based on two tables).

The table above is created as a temporary table so no updates or table modifications can or will be made. I am looking to create a single select statement.


Updated.

Assuming there the only possible values for Flag are 1 and 2:

INSERT temp_table (ID, Value, Flag, Parent)
SELECT MAX(IF(Flag = 1, ID, 0)), SUM(Value), 1, MAX(IF(Flag = 1, ParentID, 0))
FROM generated_query
GROUP BY IF(Flag = 1, ID, ParentID)

Depending on how you generate your query, you might get better performance using a LEFT JOIN instead.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜