请高手回答我，搞了一天了都！！！！！！！！！

[pengjungege]

Table A
类别 分数
A 80
A 90
A 60
.
.
.
.
B 70
B 80
.
.
.
.
.
.
C 80
C 75
求一Sql查询，查出4条记录，必须包括A,B,C至少各一，谢谢

[黄_瓜]

select top 1 from tb where 类别='a'order by newid 
union all
select top 1 from tb where 类别='b' order by newid 
union all
select top 1 from tb where 类别='c' order by newid 
union all 
select top 1 from tb order by newid 

[Mr_Nice]

引用 1 楼 beirut 的回复:

SQL codeselecttop1from tbwhere 类别='a'orderbynewidunionallselecttop1from tbwhere 类别='b'orderbynewidunionallselecttop1from tbwhere 类别='c'orderbynewidunionallselecttop1from tborderbynewid

这招狠...

[黄_瓜]

select * from
(
select top 1 * from tb where 类别='a'order by newid()
union all
select top 1 * from tb where 类别='b' order by newid()
union all
select top 1 * from tb where 类别='c' order by newid()
union all
select top 1 * from tb order by newid()
) as t
order by newid()

[黄_瓜]

select * from 
( 
select top 1 * from tb where 类别='a'order by newid() 
union all 
select top 1 * from tb where 类别='b' order by newid() 
union all 
select top 1 * from tb where 类别='c' order by newid() 
union all 
select top 1 * from tb order by newid() 
) as t 
order by newid()

[丰云]

引用楼主 pengjungege 的回复:

Table A
类别    分数 
A        80
A        90
A        60
.
.
.
.
B        70
B        80
.
.
.
.
.
.
C    80
C    75
求一Sql查询，查出4条记录，必须包括A,B,C至少各一，谢谢

另一条记录是什么？？

[pengjungege]

不好意思，各位， 我问题没说太清楚，这个A,B,C不是确定的，是从别的查询中取出来的，是一个范围，这个只是举例，我的代码是select * from testquestion where know in (select know from KLTree where Lessionid =" + Session["Lid"].ToString() + ") order by newid()，但是有些Know的值的记录没有取到，我想要一条语句使得Know的值的记录至少每个值取到一条，谢谢楼上的朋友

[pengjungege]

引用 5 楼 foren_whb 的回复:

引用楼主 pengjungege 的回复:Table A 类别    分数  A        80 A        90 A        60 . . . . B        70 B        80 . . . . . . C    80 C    75 求一Sql查询，查出4条记录，必须包括A,B,C至少各一，谢谢
另一条记录是什么？？

另外一条随便啊，只要包括了ABC之后，其他的没要求，不好意思，各位， 我问题没说太清楚，这个A,B,C不是确定的，是从别的查询中取出来的，是一个范围，这个只是举例，我的代码是select * from testquestion where know in (select know from KLTree where Lessionid =" + Session["Lid"].ToString() + ") order by newid()，但是有些Know的值的记录没有取到，我想要一条语句使得Know的值的记录至少每个值取到一条，谢谢楼上的朋友

[pengjungege]

请高手帮帮我一条搞定吧，搞定了我好过年啊

[xman_78tom]

-- SQL 2005
select top(4) 类别, 分数
(select *, rid=row_number() over (partition by 类别 order by newid()) from tabA) t
order by rid

[黄_瓜]

--1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 1 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)

--2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 1=(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)

[pengjungege]

楼上的朋友，你的这个语句我运行错误。我修改的为select top(4) *
(select *, rid=row_number() over (partition by know order by newid()) from testquestion) t
order by rid

[黄_瓜]

select top(4) * from(
select *, ID=row_number() over (partition by 类别 order by newid())  from stquestion 
where know in (select know from KLTree where Lessionid =" + Session["Lid"].ToString() + ")) as t
where id=1
 order by newid()

[黄_瓜]

引用 11 楼 pengjungege 的回复:

楼上的朋友，你的这个语句我运行错误。我修改的为select top(4) *
(select *, rid=row_number() over (partition by know order by newid()) from testquestion) t
order by rid

你sql是2k还是05的？

[pengjungege]

2000的数据库附加到05上

[feegle_develop]

按楼上的方法，去拼接SQL语句....

[黄_瓜]

试试12楼的


回复内容太短了！

[东那个升]

引用 1 楼 beirut 的回复:

SQL codeselecttop1from tbwhere 类别='a'orderbynewidunionallselecttop1from tbwhere 类别='b'orderbynewidunionallselecttop1from tbwhere 类别='c'orderbynewidunionallselecttop1from tborderbynewid

就是他了

[黄_瓜]

引用 11 楼 pengjungege 的回复:

楼上的朋友，你的这个语句我运行错误。我修改的为select top(4) *
(select *, rid=row_number() over (partition by know order by newid()) from testquestion) t
order by rid

少个from
select top(4) * from(select *, rid=row_number() over (partition by know order by newid()) from testquestion) t
order by rid

[丰云]

select * from testquestion
where know in
(select know from KLTree
where Lessionid =" + Session["Lid"].ToString() + ")
order by newid()

这句话没问题！
你说有些know 没有取到，是你的子查询结果就没取到吧。。。
你把
(select know from KLTree where Lessionid =" + Session["Lid"].ToString() + ")
单独执行看看

[pengjungege]

谢谢楼上的，晚上吃晚饭结贴