关于电影网站按分类列出电影的问题,请看示意图
小弟做了一个电影网站,页面上要按分类列出电影,每个分类列出5部电影,第一部的图片和名字在左边列出来,后面4部的名字列在右边就行了,这样做其实也不难.
先查出所有分类的名字,然后循环出来，再嵌套查询top5每个分类的电影。

这不是我想要的结果，我想一个SQL语句查出所有分类的前5部电影（select   top   5   from   电影库   group   by   分类   order   by   addtime   desc）,然后一次按格式列出分类名(就是不用数据集嵌套，不用再根据分类查询一次数据库了)，第一部的图片和名字在左边列出来,后面4部的名字列在右边
下面给个图片的链接http://www.swsky.cn/tmp/movie.jpg，就是要这样的格式，不知道如何实现？有哪位大哥做过？


------解决方案--------------------
没有做过，但是还是来帮顶一下，关注ing
------解决方案--------------------
取出每个分类前五条记录的sql语句

SQL2000的写法二：
select a.questionid,a.categoryid
from dbo.QA_Questions a
where Questionid in (select top 5 Questionid from QA_Questions where categoryid=a.categoryid
order by Questionid desc)order by a.categoryid asc


SQL2005的写法：

WITH NewTable AS(
SELECT *,RANK() OVER(PARTITION BY CategoryId Order BY QuestionId) RANK
FROM QA_Questions)
SELECT * FROM NewTable
WHERE RANK < 6


要取图片，具体看你的图片表和你的电影表是怎么关联的，取电影的时候同时取图片，

然后前台在for循环里面用拼字符串的方法把要显示的样式和数据显示出来，

例如
for (int i=0;i <dt.Rows.Count ; i++)
{
string HTML;
HTML += " ";
...
}
------解决方案--------------------
strSQL = "select * from (select a.id,a.m_kid,a.m_name,a.m_poster,rank() over (partition by a.m_kid order by a.id desc) rank,b.k_name from sw_movies a left join sw_m_kind b on b.id=a.m_kid ) m where m.rank <7 ";
objConn = new SqlConnection(ConfigurationManager.AppSettings[ "SqlConnStr "]);
myCommand = new SqlDataAdapter(strSQL, objConn);
objDataset = new DataSet();
myCommand.Fill(objDataset, "sw_movies ");
int RecordCount = 0;
RecordCount = objDataset.Tables[0].Rows.Count;
for (int i = 0; i < RecordCount; i++)
{
if (Convert.ToInt32(objDataset.Tables[0].Rows[i][ "rank "]) == 1)
{
if (i == 0)
{
kindhtml = kindhtml + " <div id=\ "Layer1\ "> <ol style=\ "float: left; margin-left: 0px; width: 236px; background-color: #f7f3f7\ "> ";
kindhtml = kindhtml + " <li class=ico> <img height=\ "11\ " src=\ "images/smv/bbs_icon.gif\ " width=\ "8\ " /> <a href=\ "list.aspx?id= " + objDataset.Tables[0].Rows[i][ "m_kid "] + "\ "> " + objDataset.Tables[0].Rows[i][ "k_name "] + " </a> </li> <li class=classtxt> <a href=\ "list.aspx?id= " + objDataset.Tables[0].Rows[i][ "m_kid "] + "\ "> <img height=\ "11\ " src=\ "images/smv/bbs_more.gif\ " width=\ "45\ " border=\ "0\ " /> </a> </li> </ol> ";
kindhtml = kindhtml + " <ol class=classpic> <li> <a href=\ "view.aspx?id= " + objDataset.Tables[0].Rows[i][ "id "] + "\ "> <img height=\ "105\ " src=\ " " + objDataset.Tables[0].Rows[i][ "m_poster "] + "\ " width=\ "75\ " border=\ "0\ " /> </a> </li> <li> <a href=\ "view.aspx?id= " + objDataset.Tables[0].Rows[i][ "id "] + "\ "> " + objDataset.Tables[0].Rows[i][ "m_name "] + " </a> </li> </ol> <ul> ";
}