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.
精彩评论