MySQL query help (involving joins?)
Although I've figured out several queries that almost do this, I can't quite get it perfectly and I'm getting frustrated. Here is the setup:
Table: Issue
| id | name | value |
+-------------------+
| 1 | a | 10 |
| 2 | b | 3 |
| 3 | c | 4 |
| 4 | d | 9 |
Table: Link
| source | dest |
+---------------+
| 1 | 2 |
| 1 | 3 |
The link table sets up a source/dest relationship between rows in the issue table. Yes, I know this is normalized terribly, but I did not create this schema even though I now have to write queries against it :(.
What I want is results that look like this:
| name | value |
+--------------+
| a | 17 |
| d | 9 |
The values in the results should be the sum of the values in the issue table when you aggregate together a source with all its dests along with the name of the source.
Some notes (1) A source->dest is a 开发者_JAVA百科one->many relationship. (2) The best answer will not have any hardcoded id's or names in the query (meaning, it will be generalized for all setups like this). (3) This is in MySQL
Thank you and let me know if I should include any more information
Its fairly simple, but the stickler is the fact that A is not a destination of A yet it is included in the table. The robust solution would involve modifying the data to add
Table: Link
| source | dest |
+---------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
Then a simple
SELECT a.name, SUM(d.value) FROM
Issues as a
JOIN Link as b on a.id=b.source
JOIN Issues AS d on b.dest=d.id;
GROUP BY a.name;
If you can't modify the data.
SELECT a.name, SUM(d.value)+a.value FROM
Issues as a
JOIN Link as b on a.id=b.source
JOIN Issues AS d on b.dest=d.id;
GROUP BY a.name,a.value;
MAY work.
SELECT S.name, S.value + SUM(D.value) as value
FROM Link AS L
LEFT JOIN Issue AS S ON L.source = S.id
LEFT JOIN Issue AS D ON L.dest = D.id
GROUP BY S.name
You could use a double join to find all linked rows, and add the sum
to the value of the source row itself:
select src.name, src.value + sum(dest.value)
from Issue src
left join Link l
on l.source = src.id
left join Link dest
on dest.id = l.dest
group by src.name, src.value
This one should return the SUM of both source and dests, and only return items which are source.
SELECT s.name, COALESCE( SUM(d.value), 0 ) + s.value value
FROM Issue s
LEFT JOIN Link l ON ( l.source = s.id )
LEFT JOIN Issue d ON ( d.id = l.dest )
WHERE s.id NOT IN ( SELECT dest FROM Link )
GROUP BY s.name, s.value
ORDER BY s.name;
精彩评论