id newstitle zd newsdate menuid
1 aaaa 1 2008-11-25 4
2 bbbb 1 2008-12-01 4
3 cccc 0 2008-11-25 4
4 dddd 0 2008-12-02 4
--------------------------------
我想得到如下的显示结果
--------------------------------
bbbb 2008-12-01
aaaa 2008-11-25
dddd 2008-12-02
cccc 2008-11-25
----------------------------------------
我是这样写,但与我想要的结果的并不一样,请大家赐教
sql="SELECT * From News where menuid=4 and zd=1 order by newsdate desc union all select * from News where menuid=4 and zd=0 order by newsdate desc"
----------------------------------------
SELECT * From News where menuid=4 order by zd desc,newsdate desc
SELECT newstitle,newsdate From News where menuid=4 order by zd desc,newsdate desc
(
SELECT top 100 percent * From News
where menuid=4 and zd=1
order by newsdate desc
)t1
union all
select * from
(
select top 100 percent * from News
where menuid=4 and zd=0
order by newsdate desc
)t2
SELECT newstitle,newsdate From News
where menuid=4
order by zd desc,newsdate desc
newstitle,
newsdate
from News
order by
zd desc,newsdate desc
declare @t table (id int,newstitle varchar(10),zd int, newsdate datetime,menuid int)
insert into @t select 1,'aaaa',1,'2008-11-25',4
union all select 2,'bbbb',1,'2008-12-01',4
union all select 3,'cccc',0,'2008-11-25',4
union all select 4,'dddd',0,'2008-12-02',4
--select * from @t a where not exists (select * from @t where zd=a.zd )
select newstitle,newsdate from (SELECT top 20 * From @t where menuid=4 and zd=1 order by newsdate desc) a
union all
select newstitle,newsdate from (select top 20* from @T where menuid=4 and zd=0 order by newsdate desc) b
bbbb 2008-12-01 00:00:00.000
aaaa 2008-11-25 00:00:00.000
dddd 2008-12-02 00:00:00.000
cccc 2008-11-25 00:00:00.000