select * from ( select top 6 sjname from tb where vip=1) a union select * from ( select top 6 sjname from tb order by newid() ) b
select * from ( select top 6 sjname from tb where vip=1) a union select * from ( select top 6 sjname from tb order by newid() ) b
select top 6 * from ( select top 6 sjname,1 as id from [table] where vip=1 union select top 6 sjname,2 as id from [table] where vip<>1 order by newid() ) as a order by id
select top 6 sjname from table where vip=1 union select * from ( select top 6 sjname from table order by newid() )T
做一个嵌套表: select *from (select top 6 sjname from table where vip=1 ) union select*from (select top 6 sjname from table order by newid())
select * from (select top 6 sjname from tb where vip=1) a union select * from (select top 6 sjname from tb order by newid() ) b
SELECT TOP 6 sjname FROM TABLE WHERE vip=1 UNION ALLSELECT * FROM( SELECT TOP 6 sjname FROM TABLE WHERE vip<>1 ORDER BY NEWID() ) A
你要取出的总数据是6条?如果这样union all取出来的数据数量是不定的 如果要取出6条,则可以这样select top 6 sjname from [table] order by case when vip=1 then 1 else 2 end, newid()
select * from ( select top 6 sjname from tb where vip=1) a union select * from ( select top 6 sjname from tb order by newid() ) b
1、 with wang1 as(select top 6 sjname from table where vip=1) wang2 as(select cnt(*) from wang1) wang3 as(select top (6-cnt) * sjname from table ,wang2 where vip<>1 order by newid()) select sjname from wang1 union select * from wang3 2、select top 6 sjname from table where vip=1 union select top 6 sjname from table where vip<>1 order by newid()
SELECT TOP 6 sjname FROM (SELECT TOP 6 NEW_ID=NEWID(), sjname FROM VIP WHERE VIP=1 UNION ALL SELECT TOP 6 NEW_ID=NEWID(), sjname FROM VIP ORDER BY NEWID())AS T
Create table #t(sjname varchar,vip bit) insert into #t values('a',1) insert into #t values('b',0) insert into #t values('c',1) insert into #t values('d',0) insert into #t values('e',1) insert into #t values('f',0) insert into #t values('g',1)select top 6 sjname,vip from #t order by case vip when 1 then 1 else 0 end desc,newid() /* c 1 g 1 e 1 a 1 d 0 f 0 -------------------- a 1 e 1 g 1 c 1 f 0 b 0 */ insert into #t values('h',1) insert into #t values('j',1) select top 6 sjname,vip from #t order by vip desc,sjname,newid() /* a 1 c 1 e 1 g 1 h 1 j 1 */
select top 6 sjname from tb where vip=1) a
union
select * from (
select top 6 sjname from tb order by newid() ) b
select * from (
select top 6 sjname from tb where vip=1) a
union
select * from (
select top 6 sjname from tb order by newid() ) b
select top 6 sjname,1 as id from [table] where vip=1
union
select top 6 sjname,2 as id from [table] where vip<>1 order by newid()
) as a order by id
union
select * from
(
select top 6 sjname from table order by newid()
)T
select *from (select top 6 sjname from table where vip=1 )
union
select*from (select top 6 sjname from table order by newid())
union
select * from (select top 6 sjname from tb order by newid() ) b
FROM TABLE
WHERE vip=1 UNION ALLSELECT *
FROM(
SELECT TOP 6 sjname
FROM TABLE
WHERE vip<>1
ORDER BY NEWID()
) A
你要取出的总数据是6条?如果这样union all取出来的数据数量是不定的
如果要取出6条,则可以这样select
top 6
sjname
from
[table]
order by
case when vip=1 then 1 else 2 end,
newid()
select top 6 sjname from tb where vip=1) a
union
select * from (
select top 6 sjname from tb order by newid() ) b
with
wang1 as(select top 6 sjname from table where vip=1)
wang2 as(select cnt(*) from wang1)
wang3 as(select top (6-cnt) * sjname from table ,wang2 where vip<>1 order by newid()) select sjname from wang1 union select * from wang3
2、select top 6 sjname from table where vip=1
union
select top 6 sjname from table where vip<>1 order by newid()
SELECT TOP 6 sjname FROM (SELECT TOP 6 NEW_ID=NEWID(), sjname FROM VIP WHERE VIP=1
UNION ALL
SELECT TOP 6 NEW_ID=NEWID(), sjname FROM VIP ORDER BY NEWID())AS T
Create table #t(sjname varchar,vip bit)
insert into #t values('a',1)
insert into #t values('b',0)
insert into #t values('c',1)
insert into #t values('d',0)
insert into #t values('e',1)
insert into #t values('f',0)
insert into #t values('g',1)select top 6 sjname,vip from #t order by case vip when 1 then 1 else 0 end desc,newid()
/*
c 1
g 1
e 1
a 1
d 0
f 0
--------------------
a 1
e 1
g 1
c 1
f 0
b 0
*/
insert into #t values('h',1)
insert into #t values('j',1)
select top 6 sjname,vip from #t order by vip desc,sjname,newid()
/*
a 1
c 1
e 1
g 1
h 1
j 1
*/