根据某列随机选择唯一信息，怎么选？

Question

假如表如下
ID              A              B              C              邮编
1              1              2               3             200000
2              1              3              4              200000
3              2              3              4              200000
4              3              4              5              200001
5              1              2              3              200001
6              2              6              7              200001
7              2              6              8              200002
7              2              6              5              200002想根据邮编随机取数据 ，每条邮编只有一条信息，如下显示ID              A              B              C              邮编
1              1              2               3             200000
6              2              6              7              200001
7              2              6              8              200002

Answer 1

select *
from tb t
where id=(select 
          max(ID)
          from tb
          where 邮编=t.邮编)

Answer 2

SELECT * 
FROM tb t
WHERE NOT EXISTS(
          SELECT 1 
          FROM tb
          WHERE 邮编 = t.邮编
           AND id>t.id
     )

Answer 3

if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[A] int,[B] int,[C] int,[yb] int)
Insert tb
select 1,1,2,3,200000 union all
select 2,1,3,4,200000 union all
select 3,2,3,4,200000 union all
select 4,3,4,5,200001 union all
select 5,1,2,3,200001 union all
select 6,2,6,7,200001 union all
select 7,2,6,8,200002 union all
select 8,2,6,5,200002
Go
Select * 
from tb t
where id=(select top 1 Id 
          from tb 
          where yb=t.yb 
          order by newid())
/*
ID          A           B           C           yb
----------- ----------- ----------- ----------- -----------
2           1           3           4           200000
4           3           4           5           200001
8           2           6           5           200002(3 個資料列受到影響)*/

Answer 4

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[A] [int],[B] [int],[C] [int],[邮编] [int])
INSERT INTO [tb]
SELECT '1','1','2','3','200000' UNION ALL
SELECT '2','1','3','4','200000' UNION ALL
SELECT '3','2','3','4','200000' UNION ALL
SELECT '4','3','4','5','200001' UNION ALL
SELECT '5','1','2','3','200001' UNION ALL
SELECT '6','2','6','7','200001' UNION ALL
SELECT '7','2','6','8','200002' UNION ALL
SELECT '7','2','6','5','200002'
-->SQL查询如下:
--原来是要随机的，来个05的SELECT ID, A, B, C, 邮编 
FROM (
         SELECT rn = RANK()OVER(PARTITION BY 邮编 ORDER BY newid()), * 
         FROM [tb]
     ) t
WHERE rn = 1
/*
ID          A           B           C           邮编
----------- ----------- ----------- ----------- -----------
2           1           3           4           200000
6           2           6           7           200001
7           2           6           8           200002(3 行受影响)
*/

Answer 5

数据
nickname       postcard
123456789      200001
12345678       200001
1234567        200001
123456         200002
12345          200002
1234           200002
123            200003
12             200003
1              200003我想要的结果是按邮编随机，可以取一条或二条信息或更多也可以一条结果：
nickname       postcard
123456789      200001
12345          200002
1              200003
二条结果：nickname       postcard
12345678       200001
1234567        200001
123456         200002
1234           200002
123            200003
12             200003

Answer 6

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([nickname] [int],[postcard] [int])
INSERT INTO [tb]
SELECT '123456789','200001' UNION ALL
SELECT '12345678','200001' UNION ALL
SELECT '1234567','200001' UNION ALL
SELECT '123456','200002' UNION ALL
SELECT '12345','200002' UNION ALL
SELECT '1234','200002' UNION ALL
SELECT '123','200003' UNION ALL
SELECT '12','200003' UNION ALL
SELECT '1','200003'-->SQL查询如下:
--1条效果:
SELECT [nickname], [postcard]
FROM (
         SELECT rn = RANK()OVER(PARTITION BY [postcard] ORDER BY newid()), * 
         FROM [tb]
     ) t
WHERE rn <= 1
/*
nickname    postcard
----------- -----------
12345678    200001
123456      200002
123         200003(3 行受影响)
*/
--2条效果：
SELECT [nickname], [postcard]
FROM (
         SELECT rn = RANK()OVER(PARTITION BY [postcard] ORDER BY newid()), * 
         FROM [tb]
     ) t
WHERE rn <= 2
/*
nickname    postcard
----------- -----------
123456789   200001
1234567     200001
123456      200002
1234        200002
12          200003
123         200003(6 行受影响)
*/

Answer 7

为什么显示   'RANK' 不是可以识别的 函数名。