关于取不重复数据

Question

先上数据
表一：表一是主表，ID是主键，UserId是用户ID，TbId是商品ID，CityID是用户所在城市ID
Id UserId TbId CityId
5     4     5683452134     7
4     1     4055287301     6
3     1     4055287301     6
2     2     4017366777     95
1     49 4028650029     128
......表二：是城市表
Id UserId CityId
1     49     6
2     2     100
......我想要的结果是随机取得主表的三个记录
要求一：主表数据不能重复，每个用户，商品，城市，都不能重复
要求二：主表在不能出现城市表出现的城市值，什么意思呢，比如用户49设置的是，不能要城市6的人在一起，用户2设置的是不和城市100的人在一起所以我想要的结果如下（返回三条记录）
Id UserId TbId CityId
5     4     5683452134     7
2     2     4017366777     95
1     49 4028650029     128上述数据中城市6的人被过滤掉了，因为用户49不要和城市6的人在一起，所有返回结果中用户49不能和用户1不能一起请问高手，如果得到这种结果的话SQL该怎么写？

Answer 1

把栏位单独抓出来,结果中加distinct可以的

Answer 2

@forever4066，没这么简单吧，你能写个SQL吗，谢谢了

Answer 3

select top 3 *
from
(
select
   a.*
from
  (select * from a as t where not exists(select 1 from a where userid=t.userid and TbId=t.TbId and CityId=t.CityId and id<t.id)) as a
inner join
   b
on
   a.cityid<>b.cityid) as t
order by newid()试试这个

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-08-24 21:40:36
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
-- Feb 10 2012 19:13:17 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([Id] int,[UserId] int,[TbId] bigint,[CityId] int)
insert [a]
select 5,4,5683452134,7 union all
select 4,1,4055287301,6 union all
select 3,1,4055287301,6 union all
select 2,2,4017366777,95 union all
select 1,49,4028650029,128 union all
select 2,49,4028650029,128 union all
select 6,4,5683452134,7
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([Id] int,[UserId] int,[CityId] int)
insert [b]
select 1,49,6 union all
select 2,2,100
--------------开始查询--------------------------select top 3 *
from
(
select
   a.*
from
  (select * from a as t where not exists(select 1 from a where userid=t.userid and TbId=t.TbId and CityId=t.CityId and id<t.id)) as a
where
   userid not in (select a.UserId from a inner join b on a.CityId=b.CityId)
) as t
order by newid()
----------------结果----------------------------
/* Id          UserId      TbId                 CityId
----------- ----------- -------------------- -----------
5           4           5683452134           7
1           49          4028650029           128
2           2           4017366777           95(3 行受影响)
*/

Answer 5

@fredrickhu，感谢你的答复，如果我再加一张表，数据如下
表三（会话表）TeamId UserId DisplayOrder
1 4 1
1 49 2
1 2 3
1 4 4
2 7 1
2 5 2
2 9 3
2 7 1
我在这里解释一下表三，表三是表一选中的结果，写到表三中的，三个人一组，按顺序写到表三中，表三可以看成是谈话顺序表，用户4对用户49讲，用户49对用户2讲，用户2对用户4讲，形成一个闭环。所以写到表三中的每组记录是四条，而不是三条我想请教一下fredrickhu，或者别的高手，如果再次从表一中选三个随机三条记录的时候，表三中讲过话的用户不能出现在这三条记录中，这个SQL该如何写呢。还有一点要说明，如果从表一中选中的数据，写到表三中后，选中的数据在表一中将立即删除此贴分数如果不够，我可以再开一贴，请帮忙解答一下，这个表三加进去后，难度会非常的高

Answer 6

我刚才写的问题，表三排版有点问题，我重给出表三的数据TeamId UserId DisplayOrder
1 4 1
1 49 2
1 2 3
1 4 4
2 7 1
2 5 2
2 9 3
2 7 1

Answer 7

@fredrickhu，你好，你能帮我解决一下，当表三加进来之后，再获取三条记录的问题吗

Answer 8

select top 3 *
from
(
select
   a.*
from
  (select * from a as t where not exists(select 1 from a where userid=t.userid and TbId=t.TbId and CityId=t.CityId and id<t.id)) as a
where
   userid not in (select a.UserId from a inner join b on a.CityId=b.CityId)
and
   userid not in(select UserId from c)
) as t
order by newid()难道是这样？ C表就是你新加的会话表

Answer 9

@fredrickhu，再帮我看看，谢谢了

Answer 10

create table test_chensir
(
id int,
UserId int,
TbId bigint,
CityId int
)
insert into test_chensir(id,UserId,TbId,CityId) values(5,4,5683452134,7)
insert into test_chensir(id,UserId,TbId,CityId) values(4,1,4055287301,6)
insert into test_chensir(id,UserId,TbId,CityId) values(3,1,4055287301,6)
insert into test_chensir(id,UserId,TbId,CityId) values(2,2,4017366777,95)
insert into test_chensir(id,UserId,TbId,CityId) values(1,49,4028650029,128)
select * from test_chensir as tc
right join (select UserId from test_chensir  group by UserId having(count(UserId))=1) as u
on tc.UserId=u.UserId
好像没搞懂需求！

Answer 11

根据我字面的理解 貌似是要求都不能重复。  6楼的代码就不能满足了 
比如插入一条记录
insert into a values(9,4,5683452135,10)得到结果（去掉TOP 3）
5           4           5683452134           7
1           49          4028650029           128
9           4           5683452135           10
2           2           4017366777           95
满足不了 都不相等 只能满足不都相同。

Answer 12

我是楼主，我再描述一下吧，争取说详细点：
表一是主表
Id UserId TbId CityId UserName CityName
1         4         5683452134         7         张三         上海
2         1         4055287301         6         李四         北京
3         1         4055287301         7         李四         北京
4         2         40173667779        5         赵六         天津
5         49          4028650029 128         周五         南京
6         6         4689650029         3         陈一         重庆
7         8         7845802029         8         王二         大连
8         32         7845802029         9         王二         大连
表一中，大连的王二，北京的李四，都有两条记录，每次选一组记录时候（一组三条记录），他们二人，只能一次选一个记录，不能将ID为7和8的同时选在一组中，而且城市相同的也不能选在一组中下面来看表二
CityId CityName UserId
3         重庆         4
6         北京         1
表二中的UserId为4的这个人，设置了他屏蔽的城市是重庆，userId为1的这个人设置了他屏蔽的城市是北京
所以在表一中选人的时候，要根据每个人屏蔽的城市作下筛选，就是和4在一组的另外两个人不能是重庆的，和1为一组的另外两个人不能是北京的
下面看下表三：
TeamId TeamName DisplayOrder UserId PrevId NextId     
1         张三/赵六/周五         1         4         49         2        
1         张三/赵六/周五         2         2         4         49      
1         张三/赵六/周五         3         49        2         4
假如已经选了一组人，插入到了表三中，DisplayOrder是讲话的顺序，4的下一个是49，2的下一个是49，49的下一个是4，形成一个闭环对话。当从表一中选出一组人，插入到表三后，选出的人所在的记录立即从表一中删除我的问题是：我现在再次从表一中选一组人，这三个人，除了要遵守不同TBID、不同城市、城市屏蔽的规则外，再加一个规则，就是张三、赵六、周五这三个人，不能同时出现一组中，其中两个人是可以的，但有个条件，就是在每一组中，张三的下一个不能再是赵六了，赵六的下一个是张三是可以的请问综合这三张表，如何写选人的SQL？

Answer 13

表三中讲过话的用户不能出现在这三条记录中， 楼主 你这2次回答有冲突。
12楼回复是说在C表中。不能出现2个人 对话多2次（2个人不能同时存在于2组中。但是1个人可以存在于多组） 你前面说的就是说明C表 中。1个人不能同时存在于2组。你这个测试数据有问题吧
北京有两个CITYID?
大连有两个CITYID？王二有两个USERID?

Answer 14

Id UserId TbId CityId UserName CityName
1         4         5683452134         7         张三         上海
2         1         4055287301         6         李四         北京
3         1         4055287301         7         李四         北京
4         2         40173667779        5         赵六         天津
5         49          4028650029 128         周五         南京
6         6         4689650029         3         陈一         重庆
7         8         7845802029         8         王二         大连
8         32         7845802029         9         王二         大连
啥数据啊。CITYNAME一样。但是CITYID不一样。USERNAME一样 USERID不一样。

Answer 15

这个思路  6楼回答 肯定不行的。 而且你这个数据太少了。很容易产生误解。
如果一个USERID 存在2行。那么他们的CITYID 可以不一样吗。

Answer 16

哦，我的测试数据复制错了，cityID应该是一样的，不好意思，应该是这样

Answer 17

WITH 表一(Id,UserId,TbId,CityId,UserName,CityName) AS (
    SELECT 1, 4,5683452134,  7,N'张三',N'上海' UNION ALL
    SELECT 2, 1,4055287301,  6,N'李四',N'北京' UNION ALL
    SELECT 3, 1,4055287301,  6,N'李四',N'北京' UNION ALL
    SELECT 4, 2,4017366777, 95,N'赵六',N'天津' UNION ALL
    SELECT 5,49,4028650029,128,N'周五',N'南京' UNION ALL
    SELECT 6, 6,4028650029,  3,N'陈一',N'重庆' UNION ALL
    SELECT 7, 8,7845802029,  9,N'王二',N'大连' UNION ALL
    SELECT 8,32,7845802029,  9,N'王二',N'大连'
),
表二(CityId,CityName,UserId) AS (
    SELECT 3,N'重庆',9 UNION ALL
    SELECT 6,N'北京',7
),
表三(TeamId,DisplayOrder,UserId) AS (
    SELECT 1,1, 4 UNION ALL
    SELECT 1,2, 2 UNION ALL
    SELECT 1,3,49
),
-- 表三 的 UserID 排序，行转列
表三PIVOD (TeamId,UserId1,UserId2,UserId3) AS (
    SELECT *
      FROM (SELECT TeamId,UserId,
                   ROW_NUMBER() OVER (PARTITION BY TeamId ORDER BY UserID) UserOrder
              FROM 表三
           ) T
     PIVOT (AVG(UserId)
            FOR UserOrder IN ([1],[2],[3])
           ) p
),
[user] AS (
    SELECT DISTINCT UserID, UserName
      FROM 表一
),
-- 所有用户组合，排除表三PIVOD
teamPIVOT AS (
    SELECT u1.UserId UserId1,
           u2.UserId UserId2,
           u3.UserId UserId3
      FROM [user] u1
      JOIN [user] u2 ON u2.UserID > u1.UserID
      JOIN [user] u3 ON u3.UserID > u2.UserID
     WHERE NOT EXISTS (SELECT *
                         FROM 表三PIVOD cp
                        WHERE cp.UserID1 = u1.UserID
                          AND cp.UserID2 = u2.UserID
                          AND cp.UserID3 = u3.UserID
                      )
),
-- 表一排除表二
ab AS (
SELECT a.*
  FROM 表一 a
 WHERE NOT EXISTS (SELECT *
                     FROM 表二 b
                    WHERE b.CityId = a.CityId
                      AND b.UserId = a.UserId
                  )
),
resultPIVOT AS (
    SELECT TOP 1
           t.*,
           a1.ID ID1,
           a2.ID ID2,
           a3.ID ID3
      FROM teamPIVOT t
      JOIN ab a1
        ON a1.UserId = t.UserId1
      JOIN ab a2
        ON a2.UserId = t.UserId2
       AND a2.TbId <> a1.TbId
       AND a2.TbID <> a1.CityId
      JOIN ab a3
        ON a3.UserId = t.UserId3
       AND a3.TbId <> a1.TbId   AND a3.TbId <> a2.TbId
       AND a3.TbID <> a1.CityId AND a3.TbID <> a2.CityId
  ORDER BY NEWID()
),
resultUNPIVOT AS (
     SELECT ID
       FROM resultPIVOT
    UNPIVOT (ID FOR IDOrder IN (ID1,ID2,ID3)
            ) up
)
SELECT a.*
  FROM resultUNPIVOT r
  JOIN 表一 a
    ON a.ID = r.ID
         Id      UserId                                    TbId      CityId UserName CityName
----------- ----------- --------------------------------------- ----------- -------- --------
          1           4                              5683452134           7 张三     上海
          3           1                              4055287301           6 李四     北京
          8          32                              7845802029           9 王二     大连
好复杂！
PIVOT 和 UNPIVOT 倒是用得熟练多了。

Answer 18

表三是自己得来的啊 不是本来就有的。
id userid tbid    cityid username cityname
1 1         321    5                  张三    北京
2 1         321    6                 张三   上海
3 2         325    7                李四            广州
4 3         326    5                王五            北京对于这个数据怎么取出来3条。我感觉在考虑多个USERID 相同的时候。先要选一条。可能你选的那一条和后面冲突
不能存在3条记录满足条件。但是另外一个记录可能满足。这个我们要考虑删除USERID相同的记录就要考虑后面的情况了。
在和表2进行连接的时候也是一样。相同城市的不同USERID 在表2中可能一个USERID 是冲突另外一个ID 不是冲突的

Answer 19

请问
PIVOT (AVG(UserId)
            FOR UserOrder IN ([1],[2],[3])
           ) p
请问算UserId的平均值是什么意思

Answer 20

id userid tbid    cityid username cityname
1 1         321    5                  张三    北京
2 1         321    6                 张三   上海
3 2         325    7                李四            广州
4 3         326    5                王五            北京
如果元数据是这样的。这里面（2,3,4)满足条件的。但是对USERID 重复。
你要删除重复的ID 的时候就要考虑后面的记录了。不然很可能保留的记录不能满足条件。删除的那条是可以满足。
所以要有个前提   表1里面的数据很多。基本不存在取不出来的情况。

Answer 21

PIVOT 是类似分组统计的运算，语法要求内部必须是统计函数。
即使对应位置只有一个UserId，还是要用MIN()、MAX()、AVG()之类的函数才行。