【急】求sql语句，要求见内容。

qq330596595 2013-10-23 03:45:29
有这样一个表


对字段‘statetime’进行分组查询，如图


得到如下结果


但是我最终想得到的结果是在分组后每组取出前两条数据，并且实现翻页，用sql语句。数据库是sql2000的。





ps：只有80分了,你懂的，大神...

...全文
472 38 打赏 收藏 转发到动态 举报
写回复
用AI写文章
38 条回复
切换为时间正序
请发表友善的回复…
发表回复
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
对了，你再试试运行下面的代码，我加了一个排序的，然后把结果截图，贴出来，看看： 

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime,
       rownum
from
(
	select t1.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc,rownum
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
哦，明白了。 那照理，刚才的代码，应该是可以的，返回statetime时间相同的数据中，的前两行，关键是没数据：
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi, newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo, newspage,userid,StateTime, rownum from ( select t1.*, ( select count(*) from ( select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi, newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo, newspage,userid,StateTime from tb_News where (NewsType = '时光树') and (userid = 1) group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType, NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort, newspartinfo,newspage,userid ) t2 where t2.StartTime = t1.StartTime and t2.NewsId <= t1.NewsId ) as rownum from ( select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi, newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo, newspage,userid,StateTime from tb_News where (NewsType = '时光树') and (userid = 1) group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType, NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort, newspartinfo,newspage,userid )t1 )t where rownum <= 2 order by StateTime desc,rownum
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 16 楼 yupeigu 的回复:
试试这个呢，用了临时表：





if object_id('tempdb..#temp') is not null

   drop table #temp

   

   

--先放到临时表

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,

	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,

	   newspage,userid,StateTime,identity(int,1,1) as identity_id

	   into #temp

from tb_News

where (NewsType = '时光树') and (userid = 1)

group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,

		 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,

		 newspartinfo,newspage,userid





--查询结果

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,

	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,

	   newspage,userid,StateTime

from 

(

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,

	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,

	   newspage,userid,StateTime,

	   

	   (select count(*)

	    from #temp t2

	    where t1.StateTime = t2.StateTime

	          and t1.identity_id >= t2.identity_id) as rownum



from #temp t1

)t

where rownum <=2

order by StateTime desc,rownum
大哥，我是sql2000的数据库啊，刚才用这个sql的时候，报错说“查询设计器不支持 DROP TABLE SQL 构造。”点击确认后又报了这个错误，然后没有结果返回
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
试试这个呢，用了临时表： 


if object_id('tempdb..#temp') is not null
   drop table #temp
   
   
--先放到临时表
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
	   newspage,userid,StateTime,identity(int,1,1) as identity_id
	   into #temp
from tb_News
where (NewsType = '时光树') and (userid = 1)
group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
		 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
		 newspartinfo,newspage,userid


--查询结果
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
	   newspage,userid,StateTime
from 
(
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
	   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
	   newspage,userid,StateTime,
	   
	   (select count(*)
	    from #temp t2
	    where t1.StateTime = t2.StateTime
	          and t1.identity_id >= t2.identity_id) as rownum

from #temp t1
)t
where rownum <=2
order by StateTime desc,rownum
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 14 楼 yupeigu 的回复:
[quote=引用 13 楼 qq330596595 的回复:] [quote=引用 12 楼 yupeigu 的回复:] [quote=引用 11 楼 qq330596595 的回复:] [quote=引用 4 楼 yupeigu 的回复:] 楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
是的，就是这个意思[/quote] 你试试这个： 

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select a.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
[/quote]报错了“列前缀‘a’与查询中所用的表名或列名不匹配”。[/quote] 改成这样试试： 



select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select t1.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
[/quote]这样的结果是查询出了这个人所有信息，不是分组后的头两条数据
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
引用 13 楼 qq330596595 的回复:
[quote=引用 12 楼 yupeigu 的回复:] [quote=引用 11 楼 qq330596595 的回复:] [quote=引用 4 楼 yupeigu 的回复:] 楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
是的，就是这个意思[/quote] 你试试这个： 

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select a.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
[/quote]报错了“列前缀‘a’与查询中所用的表名或列名不匹配”。[/quote] 改成这样试试： 



select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select t1.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 12 楼 yupeigu 的回复:
[quote=引用 11 楼 qq330596595 的回复:] [quote=引用 4 楼 yupeigu 的回复:] 楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
是的，就是这个意思[/quote] 你试试这个： 

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select a.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
[/quote]报错了“列前缀‘a’与查询中所用的表名或列名不匹配”。
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
引用 11 楼 qq330596595 的回复:
[quote=引用 4 楼 yupeigu 的回复:] 楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
是的，就是这个意思[/quote] 你试试这个： 

select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime
from
(
	select a.*,
		   (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.StartTime = t1.StartTime
					  and t2.NewsId <= t1.NewsId
		   ) as rownum
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by StateTime desc
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 4 楼 yupeigu 的回复:
楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
是的，就是这个意思
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 2 楼 ap0405140 的回复:
LZ最好能把SQL语句弄成文字的,以便修改.
是说列名吗
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
这个改一下： 



select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
       newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
       newspage,userid,StateTime,
       rownum
from
(
	select t1.*,
	       (    
		        select count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime,
						   convert(varchar(7),StateTime,120) as mon
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.mon = t1.mon
		   )-	
		   (    
		        select 
		               count(*) 
				from 
				(
					select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
						   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
						   newspage,userid,StateTime,
						   convert(varchar(7),StateTime,120) as mon
					from tb_News
					where (NewsType = '时光树') and (userid = 1)
					group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
							 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
							 newspartinfo,newspage,userid
				) t2
				where t2.mon = t1.mon
					  and t2.NewsId <= t1.NewsId
		   )+1 as rownum 
	from
	(
		select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
			   newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
			   newspage,userid,StateTime,convert(varchar(7),StateTime,120) as mon
		from tb_News
		where (NewsType = '时光树') and (userid = 1)
		group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
				 NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
				 newspartinfo,newspage,userid
	)t1
)t
where rownum <= 2
order by mon desc,StateTime desc,rownum
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 36 楼 guoren12 的回复:
[quote=引用 34 楼 qq330596595 的回复:] [quote=引用 32 楼 guoren12 的回复:] [quote=引用 31 楼 qq330596595 的回复:] [quote=引用 30 楼 guoren12 的回复:] 
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
               newspage,userid,StateTime
        from tb_News as t
        where (NewsType = '时光树') and (userid = 1) and s.NewsId IN
        (select top 2 NewsId
        from tb_News 
        where t.StateTime=[StateTime],
              t.NewsId=[NewsId],
              t.NewsTitle=[NewsTitle],
              t.NewsContent=[NewsContent],
              t.NewsTime=[NewsTime],
              t.NewsClick=[NewsClick],
              t.NewsType=[NewsType],
              t.NewsAudi=[NewsAudi],
              t.newstc=[newstc],
              t.Newssource=[Newssource],
              t.updateuser=[updateuser],
              t.Newsimg=[Newsimg],
              t.Statetype=[Statetype],
              t.psort=[psort],
              t.newspartinfo=[newspartinfo],
              t.newspage=[newspage],
              t.userid=[userid]
              ORDER BY [StateTime])
你试试这个行不行[/quote]我用的是sql2000哦，报错了[/quote]额，水平有限，我只能帮这么多了= =我的是08[/quote]恩恩，没事，谢谢你帮助了我
decamincow 2013-11-08
  • 打赏
  • 举报
 回复
引用 34 楼 qq330596595 的回复:
[quote=引用 32 楼 guoren12 的回复:] [quote=引用 31 楼 qq330596595 的回复:] [quote=引用 30 楼 guoren12 的回复:] 
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
               newspage,userid,StateTime
        from tb_News as t
        where (NewsType = '时光树') and (userid = 1) and s.NewsId IN
        (select top 2 NewsId
        from tb_News 
        where t.StateTime=[StateTime],
              t.NewsId=[NewsId],
              t.NewsTitle=[NewsTitle],
              t.NewsContent=[NewsContent],
              t.NewsTime=[NewsTime],
              t.NewsClick=[NewsClick],
              t.NewsType=[NewsType],
              t.NewsAudi=[NewsAudi],
              t.newstc=[newstc],
              t.Newssource=[Newssource],
              t.updateuser=[updateuser],
              t.Newsimg=[Newsimg],
              t.Statetype=[Statetype],
              t.psort=[psort],
              t.newspartinfo=[newspartinfo],
              t.newspage=[newspage],
              t.userid=[userid]
              ORDER BY [StateTime])
你试试这个行不行[/quote]我用的是sql2000哦，报错了[/quote]额，水平有限，我只能帮这么多了= =我的是08
阳泉酒家小当家 2013-11-08
  • 打赏
  • 举报
 回复
引用 29 楼 qq330596595 的回复:
[quote=引用 26 楼 yupeigu 的回复:] [quote=引用 25 楼 qq330596595 的回复:] [quote=引用 4 楼 yupeigu 的回复:] 楼主的意思，是不是把你写的语句的查询结果， 再按照starttime字段来分组，每个组返回2条数据
嗯，这个是好的，谢谢了。你好厉害，我们可不可以加qq啊，我想拜你为师，学习sql。可以吗？ [/quote] 嗯 我加你关注吧，你也加我关注，然后就给我发私信[/quote]恩恩，已经加关注，但是我还有个问题不明白啊，师傅。先看这两图这个是查询所有的结果；下面是刚才那个sql的结果按照月份分组取出头两条的话，2013年10月份的数据取出头两条后应该是2013/10/29和2013/10/26啊，为什么结果是2013/10/25和2013/10/9呢？[/quote] 哦 ，明白了，这个还得修改一下。
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 32 楼 guoren12 的回复:
[quote=引用 31 楼 qq330596595 的回复:]
[quote=引用 30 楼 guoren12 的回复:]
SELECT * FROM tb_News AS t

WHERE t.StartDate IN

(SELECT TOP 2 StartDate

 FROM tb_News

 WHERE t.statetime=[statetime]  --分组的对象

 ORDER BY StartDate DESC)

这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,

               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,

               newspage,userid,StateTime

        from tb_News as t

        where (NewsType = '时光树') and (userid = 1) and s.NewsId IN

        (select top 2 NewsId

        from tb_News 

        where t.StateTime=[StateTime],

              t.NewsId=[NewsId],

              t.NewsTitle=[NewsTitle],

              t.NewsContent=[NewsContent],

              t.NewsTime=[NewsTime],

              t.NewsClick=[NewsClick],

              t.NewsType=[NewsType],

              t.NewsAudi=[NewsAudi],

              t.newstc=[newstc],

              t.Newssource=[Newssource],

              t.updateuser=[updateuser],

              t.Newsimg=[Newsimg],

              t.Statetype=[Statetype],

              t.psort=[psort],

              t.newspartinfo=[newspartinfo],

              t.newspage=[newspage],

              t.userid=[userid]

              ORDER BY [StateTime])


你试试这个行不行[/quote]我用的是sql2000哦，报错了
decamincow 2013-11-08
  • 打赏
  • 举报
 回复
引用 32 楼 guoren12 的回复:
[quote=引用 31 楼 qq330596595 的回复:] [quote=引用 30 楼 guoren12 的回复:] 
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
               newspage,userid,StateTime
        from tb_News as t
        where (NewsType = '时光树') and (userid = 1) and t.NewsId IN
        (select top 2 NewsId
        from tb_News 
        where t.StateTime=[StateTime],
              t.NewsId=[NewsId],
              t.NewsTitle=[NewsTitle],
              t.NewsContent=[NewsContent],
              t.NewsTime=[NewsTime],
              t.NewsClick=[NewsClick],
              t.NewsType=[NewsType],
              t.NewsAudi=[NewsAudi],
              t.newstc=[newstc],
              t.Newssource=[Newssource],
              t.updateuser=[updateuser],
              t.Newsimg=[Newsimg],
              t.Statetype=[Statetype],
              t.psort=[psort],
              t.newspartinfo=[newspartinfo],
              t.newspage=[newspage],
              t.userid=[userid]
              ORDER BY [StateTime])
你试试这个行不行[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
               newspage,userid,StateTime
        from tb_News as t
        where (NewsType = '时光树') and (userid = 1) and t.NewsId IN
        (select top 2 NewsId
        from tb_News 
        where t.StateTime=[StateTime],
              t.NewsId=[NewsId],
              t.NewsTitle=[NewsTitle],
              t.NewsContent=[NewsContent],
              t.NewsTime=[NewsTime],
              t.NewsClick=[NewsClick],
              t.NewsType=[NewsType],
              t.NewsAudi=[NewsAudi],
              t.newstc=[newstc],
              t.Newssource=[Newssource],
              t.updateuser=[updateuser],
              t.Newsimg=[Newsimg],
              t.Statetype=[Statetype],
              t.psort=[psort],
              t.newspartinfo=[newspartinfo],
              t.newspage=[newspage],
              t.userid=[userid]
              ORDER BY [StateTime])
decamincow 2013-11-08
  • 打赏
  • 举报
 回复
引用 31 楼 qq330596595 的回复:
[quote=引用 30 楼 guoren12 的回复:] 
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊[/quote]
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
               newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
               newspage,userid,StateTime
        from tb_News as t
        where (NewsType = '时光树') and (userid = 1) and s.NewsId IN
        (select top 2 NewsId
        from tb_News 
        where t.StateTime=[StateTime],
              t.NewsId=[NewsId],
              t.NewsTitle=[NewsTitle],
              t.NewsContent=[NewsContent],
              t.NewsTime=[NewsTime],
              t.NewsClick=[NewsClick],
              t.NewsType=[NewsType],
              t.NewsAudi=[NewsAudi],
              t.newstc=[newstc],
              t.Newssource=[Newssource],
              t.updateuser=[updateuser],
              t.Newsimg=[Newsimg],
              t.Statetype=[Statetype],
              t.psort=[psort],
              t.newspartinfo=[newspartinfo],
              t.newspage=[newspage],
              t.userid=[userid]
              ORDER BY [StateTime])
你试试这个行不行
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 30 楼 guoren12 的回复:
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
不太明白这个sql，表中没有StartDate这个字段啊
decamincow 2013-11-08
  • 打赏
  • 举报
 回复 
SELECT * FROM tb_News AS t
WHERE t.StartDate IN
(SELECT TOP 2 StartDate
 FROM tb_News
 WHERE t.statetime=[statetime]  --分组的对象
 ORDER BY StartDate DESC)
这个简单的语句可以帮你做分组top2，试试吧。
qq330596595 2013-11-08
  • 打赏
  • 举报
 回复
引用 26 楼 yupeigu 的回复:
[quote=引用 25 楼 qq330596595 的回复:]
[quote=引用 4 楼 yupeigu 的回复:]
楼主的意思，是不是把你写的语句的查询结果，

再按照starttime字段来分组，每个组返回2条数据
嗯，这个是好的，谢谢了。你好厉害，我们可不可以加qq啊，我想拜你为师，学习sql。可以吗？
[/quote]

嗯 我加你关注吧，你也加我关注，然后就给我发私信[/quote]恩恩，已经加关注，但是我还有个问题不明白啊，师傅。先看这两图这个是查询所有的结果；下面是刚才那个sql的结果按照月份分组取出头两条的话，2013年10月份的数据取出头两条后应该是2013/10/29和2013/10/26啊，为什么结果是2013/10/25和2013/10/9呢？
加载更多回复（18）
mysql教学管理系统数据库
该数据库用于mysql中sql语句的学习与操作。
常用sql语句大全
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。它们并不对表中的行进行处理，这是因为DDL语句并不处理数据库中实际的数据。假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。表的结构以及表的所有属性仍然保留，例如字段属性及索引。
7天带你玩转Mysql数据库之SQL语句
在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。 3. 稍微复杂⼀点的SQL就不知道该如何写了。 4. 看不懂同⾏写的SQL是为什么。 5. 不知道索引的原理。 6. 不知道怎么...
oracle修改的sql语句,请教oracle高手 SQL语句该怎么修改，如何写。
当前位置:我的异常网» Oracle开发»请教oracle高手 SQL语句该怎么修改，如何写。请教oracle高手 SQL语句该怎么修改，如何写。www.myexceptions.net网友分享于：2013-02-04浏览：18次请问oracle高手 SQL语句该如何修改，怎么写。急！！！后台为ORACEL数据库：select A.SORDER,A.KOTECD,A.JSTSTM,A....
使用游标的SQL语句
使用游标的SQL语句 必须使用游标的SQL语句有查询结果为多条记录的select语句，current形式的update和delete语句。 查询结果为多条记录的select语句 一般情况下，select语句查询结果为多条记录，因此需要使用游标机制将多条记录一次一条的送主程序处理，从而把集合的操作转换为对单个记录的处理，使用游标的步骤为: 1:说明游标 用declare语句为一条select语句定义游标: exec SQL declare <游标名> cursor for <select语句
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章