如何从已连接的sql表创建报表？

Question

我有三张桌子：

 pindex_photos:        pindex_names:    pindex_link:
 | id  | filename  |   | id | name  |   | photo_id | name_id |
 |-----|-----------|   |----|-------|   |----------|---------|
 | 1   | DSC1.jpg  |   | 1  | Leo   |   | 1        | 3       |
 | 2   | DSC2.jpg  |   | 2  | Liz   |   | 1        | 2       |
 | 3   | DSC5.jpg  |   | 3  | Tom   |   | 3        | 1       |
 | ..  | ..        |   | .. | ..    |   | 2        | 1       |
                                        | ..       | ..      |

在pindex_link中，前两个表与它们的id相关联。现在，我想从这个表中创建一个报告文件，如：

 Report on Images (list only Images that have > 0 connections to names)
 DSC1.jpg (2): Liz, Tom
 DSC2.jpg (1): Leo
 ..

 Report on Names (list only Names that have > 0 connections to images)
 Leo (2): DSC2.jpg, DSC5.jpg
 Liz (1): DSC1.jpg
 ..

有人能帮我吗?或者告诉我如何做到这一点？

Answer 1

select p.filename, group_concat(n.name)
from pindex_photos p
left join pindex_link l on l.photo_id = p.id
left join pindex_names n on l.name_id = n.id
group by p.filename

和

select n.name, group_concat(p.filename)
from pindex_photos p
left join pindex_link l on l.photo_id = p.id
left join pindex_names n on l.name_id = n.id
group by n.name

Answer 2

图片报道：

SELECT p.id,COUNT(pn.name) as Count,group_concat(pn.name)
FROM pindex_link pl LEFT JOIN
pindex_photos p on p.id=pl.photo_id INNER JOIN
pindex_names pn on pn.id=pl.name_id
GROUP BY p.id

结果：

ID  COUNT   GROUP_CONCAT(PN.NAME)
1   2       Liz,Tom
2   1       Leo
3   1       Leo

见SQL Fiddle中的结果。

名称报告：

SELECT pn.name,COUNT(p.filename) as Count,group_concat(p.filename)
FROM pindex_link pl LEFT JOIN
pindex_photos p on p.id=pl.photo_id INNER JOIN
pindex_names pn on pn.id=pl.name_id
GROUP BY pn.name

结果：

NAME    COUNT   GROUP_CONCAT(P.FILENAME)
Leo     2       DSC5.jpg,DSC2.jpg
Liz     1       DSC1.jpg
Tom     1       DSC1.jpg

见SQL Fiddle中的结果。

Answer 3

如果希望将输出格式化为单个列：

SELECT 'Report on Images (list only Images that have > 0 connections to names)';
SELECT concat(filename, ' (', COUNT(pn.name), '): ', group_concat(pn.name)) as fixed_output  
FROM pindex_link pl LEFT JOIN
pindex_photos p on p.id=pl.photo_id INNER JOIN
pindex_names pn on pn.id=pl.name_id
GROUP BY p.id;

SELECT 'Report on Names (list only Names that have > 0 connections to images)';
SELECT concat(pn.name, ' (' , COUNT(p.filename), '): ',  group_concat(p.filename)) as fixed_output
FROM pindex_link pl LEFT JOIN
pindex_photos p on p.id=pl.photo_id INNER JOIN
pindex_names pn on pn.id=pl.name_id
GROUP BY pn.name;