如果检索一个表中必须满足某个条件的全部数据和剩下的数据随机5条

[chilli6519]

晚上好：

有一个表 Table1， 其中有字段 id，title
如果检测 title = ‘abc’ 的全部数据，并且同时检索出， title <> ‘abc’ 的随机 5 条

不用存储过程，只能动态sql 语句

谢谢

[chilli6519]

顶起，谢谢大家大力支持

[chilli6519]

自己顶起，各位大牛帮助一下啊

[Tiger_Zhao]

SELECT TOP 1 *
  FROM table1
 WHERE title = 'abc'
UNION ALL
SELECT *
  FROM (  SELECT TOP 5 *
            FROM table1
           WHERE title <> 'abc'
        ORDER BY Newid()
       ) b

[zbdzjx]

with table1 as
(
select 1 id, 'a' title union all
select 2 id, 'b' title union all
select 3 id, 'c' title union all
select 4 id, 'd' title union all
select 5 id, 'e' title union all
select 6 id, 'abc' title union all
select 7 id, 'g' title union all
select 8 id, 'h' title union all
select 9 id, 'i' title union all
select 10 id, 'j' title union all
select 11 id, 'k' title union all
select 12 id, 'l' title union all
select 13 id, 'abc' title union all
select 14 id, 'n' title union all
select 15 id, 'o' title union all
select 16 id, 'p' title union all
select 17 id, 'q' title union all
select 18 id, 'abc' title union all
select 19 id, 's' title union all
select 20 id, 't' title 
)
select * from table1 where title='abc'
union all
select * from (select top 5 * from table1 where title<>'abc' order by NEWID()) aa

[Yole]

select * from Table1 where title = 'abc'
union all 
select top 5 * from Table1 where title <> 'abc' order by NEWID

[Haytor]

引用 3 楼 Tiger_Zhao 的回复:

SELECT TOP 1 *
  FROM table1
 WHERE title = 'abc'
UNION ALL
SELECT *
  FROM (  SELECT TOP 5 *
            FROM table1
           WHERE title <> 'abc'
        ORDER BY Newid()
       ) b

Tiger_Zhao，能帮我解释一下为什么下面这种写法不能达到你写的效果。

SELECT 
	a.* 
FROM 
	table1 a
WHERE 
	a.ID IN 
	(
		SELECT 
			TOP 5 b.ID 
		FROM 
			table1 b 
		WHERE 
			b.title<>'abc' 
		ORDER BY NEWID()
	) 
	OR a.title = 'abc'

[Tiger_Zhao]

[Quote=引用 6 楼 airsoft 的回复:]Tiger_Zhao，能帮我解释一下为什么下面这种写法不能达到你写的效果。[/Quote]
你指的是两组记录的次序？
UNION ALL 是把两次查询的结果拼起来，所以输出自然按组的次序来。
你的查询结果是一次出来的，当然是按遍历的次序输出的。