有产品大类,大类下面有小类,小类下面直接是产品.表结构如下:tbcolumn:
id name
1 电器
2 化工用品tbcategory:
id name columnid
1 电视 1
tbproduct:
id name categoryid submittime img
id 长虹 1 2009-05-02 323402342.jpg
现在需要在页面上罗列出所有大类(表tbcolumn),但每个大类只读取一张最新产品的图片(在tbproduct中找属于这个大类的最新图片,根据submittime列),
结果如下:
columnid img
1 323402342.jpg
2 343272344.jpg
请大家帮帮忙,自己水平不够,写不出来,先谢过.
id name
1 电器
2 化工用品tbcategory:
id name columnid
1 电视 1
tbproduct:
id name categoryid submittime img
id 长虹 1 2009-05-02 323402342.jpg
现在需要在页面上罗列出所有大类(表tbcolumn),但每个大类只读取一张最新产品的图片(在tbproduct中找属于这个大类的最新图片,根据submittime列),
结果如下:
columnid img
1 323402342.jpg
2 343272344.jpg
请大家帮帮忙,自己水平不够,写不出来,先谢过.
from tbcolumn a
left join tbcategory b on a.id = b.columnid
left join tbproduct c on b.id = c.categoryid
where not exists(select 1 from tbproduct where c.id = id and submittime > a.submittime)
from tbcolumn a
inner join tbcategory b on a.id=b.columnid
inner join tbproduct c on b.id=c.categoryid
select a.id,c.img
from tbcolumn a, tbcategory b ,tbproduct c
where a.id = b.columnid and b.id = c.categoryid
and not exists(select 1 from tbproduct where c.id = id and submittime > a.submittime)
select a.columnid,c.tbproduct
from tbcolumn a,tbcategory b,tbproduct c
where a.ID = b.ID and b.id = c.id
and not exists(select 1 from tbcolumn d,tbcategory e,tbproduct f where d.ID = e.ID and e.id = f.id and a.id = d.id and f.submittime > c.submittime )
left join tbcategory b on a.id = b.columnid
left join tbproduct c on b.id = c.categoryid
order by c.submittime desc