日期:2014-05-18  浏览次数:20588 次

选出每个父类ID=0的新闻1条
SELECT * FROM HT_News 
LEFT JOIN HT_NewsCategories ON HT_News.Category1=HT_NewsCategories.CategoryId
WHERE Top1=1 AND Status=1 AND HT_NewsCategories.ParentCategoryId=0

这个把符合条件的都显示了,我希望每个条件ParentCategoryId=0的类只显示一条新闻。

------解决方案--------------------
给出具体的表结构。
------解决方案--------------------
SQL code
;with f as
(
SELECT * FROM HT_News  
LEFT JOIN HT_NewsCategories ON HT_News.Category1=HT_NewsCategories.CategoryId
WHERE Top1=1 AND Status=1 AND HT_NewsCategories.ParentCategoryId=0)

select * from f t where 显示N条的字段=(select min(显示N条的字段) from f where Category1=t.Category1)

------解决方案--------------------
SQL code

; with maco as
(
SELECT * FROM HT_News  
LEFT JOIN HT_NewsCategories ON HT_News.Category1=HT_NewsCategories.CategoryId
WHERE Top1=1 AND Status=1 AND HT_NewsCategories.ParentCategoryId=0
)

select * from maco t
where id=(select min(id) from maco where 类名字段=t.类名字段)

--id指的是结果中的唯一列,就是不重复的列,可以标识为某一行的。