27,579
社区成员
发帖
与我相关
我的任务
分享
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
DROP TABLE tb
少了表名
select *
from tb
ordr by 字段1,newid()
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @table
DECLARE @table TABLE (字段一 VARCHAR(1),字段二 INT)
INSERT INTO @table
SELECT 'a',1 UNION ALL
SELECT 'a',3 UNION ALL
SELECT 'b',2 UNION ALL
SELECT 'b',4
--SQL查询如下:
;WITH Liang AS
(
SELECT *,gid=ROW_NUMBER() OVER(PARTITION BY 字段一 ORDER BY NEWID())
FROM @table
)
SELECT
字段一,字段二
FROM Liang
WHERE gid=1
ORDER BY 字段二
/*
字段一 字段二
---- -----------
b 2
a 3
(2 行受影响)
*/
select *
from
(
select top 1 * from [table] where [字段一]='a' order by newid()
union all
select top 1 * from [table] where [字段一]='b' order by newid()
) t
order by [字段二]
;WITH Liang AS
(
SELECT
gid=ROW_NUMBER() OVER(PARTITION BY 字段一 ORDER BY 字段二),
*
FROM tb
)
SELECT 字段一,字段二
FROM Liang
WHERE ....
ORDER BY gid,字段二
select top 1 * from [table] where [字段一]='a' order by [字段二]
union all
select top 1 * from [table] where [字段一]='b' order by newid()