Sql1:create table tab (NewsID int, NewsTitle char(10), TypeID int )insert tab select 1, 'Title1' , 1 insert tab select 2 , 'Title2' , 2 insert tab select 3 , 'Title3' , 2 insert tab select 4 , 'Title4' , 3 insert tab select 5 , 'Title5' , 2 insert tab select 6 , 'Title6' , 1 insert tab select 7 , 'Title7' , 3 insert tab select 8 , 'Title8' , 2 insert tab select 9 , 'Title9' , 3select tab.* from (select max(newsid) as id from tab group by typeid union select min(newsid) from tab group by typeid) as t0 inner join tab on id=newsid order by typeid drop table tab sq2:正在想
create table tab (NewsID int, NewsTitle char(10), TypeID int ) create table tab2 (TypeID int, TypeName char(10))insert tab2 select 1,'n1' insert tab2 select 2,'n2' insert tab2 select 3,'n3' insert tab select 1, 'Title1' , 1 insert tab select 2 , 'Title2' , 2 insert tab select 3 , 'Title3' , 2 insert tab select 4 , 'Title4' , 3 insert tab select 5 , 'Title5' , 2 insert tab select 6 , 'Title6' , 1 insert tab select 7 , 'Title7' , 3 insert tab select 8 , 'Title8' , 2 insert tab select 9 , 'Title9' , 3 select tab.* from (select max(newsid) as id from tab group by typeid union select min(newsid) from tab group by typeid) as t0 inner join tab on id=newsid order by typeid select typename,count(*) as 数量 from tab inner join tab2 on tab.typeid=tab2.typeid group by typenamedrop table tab drop table tab2
--1. 每个TypeID的选出两条数据! --1.a select * from Tb1 a where NewsID in( select top 2 NewsID from Tb1 where TypeID=a.TypeID)--1. b select a.* from Tb1 a,( select ID1=min(NewsID),ID2=max(NewsID) from Tb1 group by TypeID )b where a.NewsID in(b.ID1,b.ID2)
--Tb1所有的TypeName,和相应TypeName在Tb2的数量 select *,(select count(*) from Tb2 where TypeID=a.TypeID) from Tb1 a --或者: select a.*,b.cnt from Tb1 a left join( select TypeID,cnt=count(*) from Tb2 group by TypeID )b on a.TypeID=b.TypeID --或者: select a.TypeID,a.TypeName,count(b.TypeID) as cnt from Tb1 a left join Tb2 b on a.TypeID=b.TypeID group by a.TypeID,a.TypeName
select top 2 * from tb WHERE tyid=1 UNION select top 2 * from tb WHERE tyid=2 UNION select top 2 * from tb WHERE tyid=3 UNION ORDER BY tyid
select * from Tb1 a where NewsID in( select top 2 NewsID from Tb1 where TypeID=a.TypeID) order by tyid
问题1 declare @a table (NewsID int, NewsTitle char(10), TypeID int )insert @a select 1, 'Title1' , 1 insert @a select 2 , 'Title2' , 2 insert @a select 3 , 'Title3' , 2 insert @a select 4 , 'Title4' , 3 insert @a select 5 , 'Title5' , 2 insert @a select 6 , 'Title6' , 1 insert @a select 7 , 'Title7' , 3 insert @a select 8 , 'Title8' , 2 insert @a select 9 , 'Title9' , 3 select * from @a a where (select count(1) from @a b where a.typeid=b.typeid and a.newsid<b.newsid)<2 order by a.typeid
问题1select * from newtable a where NewsID in( select top 2 NewsID from newtable where TypeID=a.TypeID) order by typeid
问题2select a.typeid,a.typename,count(b.typeid) as cnt from newtable2 a left join newtable b on a.typeid=b.typeid group by a.typeid,a.typename
1 select * from Tb1 a where NewsID in( select top 2 NewsID from Tb1 where TypeID=a.TypeID)2 select A.TypeName , B.Cnt from Tb1 A left join (select TypeId , count(*) as Cnt from Tb2 group by TypeID ) B on A.Typeid = B.TypeID
insert tab select 2 , 'Title2' , 2
insert tab select 3 , 'Title3' , 2
insert tab select 4 , 'Title4' , 3
insert tab select 5 , 'Title5' , 2
insert tab select 6 , 'Title6' , 1
insert tab select 7 , 'Title7' , 3
insert tab select 8 , 'Title8' , 2
insert tab select 9 , 'Title9' , 3select tab.* from (select max(newsid) as id from tab group by typeid
union
select min(newsid) from tab group by typeid) as t0 inner join tab on id=newsid
order by typeid drop table tab
sq2:正在想
create table tab2 (TypeID int, TypeName char(10))insert tab2 select 1,'n1'
insert tab2 select 2,'n2'
insert tab2 select 3,'n3'
insert tab select 1, 'Title1' , 1
insert tab select 2 , 'Title2' , 2
insert tab select 3 , 'Title3' , 2
insert tab select 4 , 'Title4' , 3
insert tab select 5 , 'Title5' , 2
insert tab select 6 , 'Title6' , 1
insert tab select 7 , 'Title7' , 3
insert tab select 8 , 'Title8' , 2
insert tab select 9 , 'Title9' , 3
select tab.* from (select max(newsid) as id from tab group by typeid
union
select min(newsid) from tab group by typeid) as t0 inner join tab on id=newsid
order by typeid select typename,count(*) as 数量 from tab inner join tab2 on tab.typeid=tab2.typeid
group by typenamedrop table tab
drop table tab2
--1.a
select * from Tb1 a
where NewsID in(
select top 2 NewsID from Tb1 where TypeID=a.TypeID)--1. b
select a.*
from Tb1 a,(
select ID1=min(NewsID),ID2=max(NewsID)
from Tb1 group by TypeID
)b where a.NewsID in(b.ID1,b.ID2)
select *,(select count(*) from Tb2 where TypeID=a.TypeID)
from Tb1 a
--或者:
select a.*,b.cnt
from Tb1 a left join(
select TypeID,cnt=count(*) from Tb2
group by TypeID
)b on a.TypeID=b.TypeID
--或者:
select a.TypeID,a.TypeName,count(b.TypeID) as cnt
from Tb1 a left join Tb2 b on a.TypeID=b.TypeID
group by a.TypeID,a.TypeName
WHERE tyid=1
UNION select top 2 * from tb
WHERE tyid=2
UNION select top 2 * from tb
WHERE tyid=3
UNION
ORDER BY tyid
where NewsID in(
select top 2 NewsID from Tb1 where TypeID=a.TypeID)
order by tyid
declare @a table (NewsID int, NewsTitle char(10), TypeID int )insert @a select 1, 'Title1' , 1
insert @a select 2 , 'Title2' , 2
insert @a select 3 , 'Title3' , 2
insert @a select 4 , 'Title4' , 3
insert @a select 5 , 'Title5' , 2
insert @a select 6 , 'Title6' , 1
insert @a select 7 , 'Title7' , 3
insert @a select 8 , 'Title8' , 2
insert @a select 9 , 'Title9' , 3
select * from @a a where
(select count(1) from @a b where a.typeid=b.typeid and a.newsid<b.newsid)<2
order by a.typeid
where NewsID in(
select top 2 NewsID from newtable where TypeID=a.TypeID) order by typeid
b on a.typeid=b.typeid group by a.typeid,a.typename
select * from Tb1 a
where NewsID in(
select top 2 NewsID from Tb1 where TypeID=a.TypeID)2
select A.TypeName , B.Cnt from
Tb1 A left join
(select TypeId , count(*) as Cnt from Tb2 group by TypeID ) B
on A.Typeid = B.TypeID