有些难的sql 查询，关于字段组合排列查询.

CNASP 2010-10-14 10:29:03

求sql 查询：
列出所有可能存在的组合，在每个组合中的每一个用户都评论(Comment)过同组所有其他用户的书评(Review). 总之有用户之间有互评就列为一组, 也许他们是好朋友... 就要找类似的组. 应该包含所有组合.

结果:
在给出的数据中应该查出:
用户 1和2 应该为一组,因为1评价了2,2也评价了1
用户 5,6,7应该为一组,因为5评价了6,7; 6评价了5,7 ; 7评价了5,6
用户 10,11,12,13,14应为一组,原因通上,他们互评了对方的书评.
这样在给出的数据中就应该有三组，要求分组列出他们的用户id

表结构:
ReviewBook
ReviewID,BookID,UserID,Reviews

CommentReview
ReviewID,UserID,Comments

其中每个书评(Review)只能有一个(Comment).

注:数据也许会上千条,这里只是列出几条举例
附1.数据表：
ReviewBook:
-------------------------------
ReviewID BookID UserID Reviews
1 1 1 rrr
2 2 2 rrr
3 3 3 rrr
4 4 4 rrr
5 5 5 rrr
6 6 6 rrr
7 7 7 rrr
8 8 8 r
10 9 9 r
11 10 10 r
12 10 11 r
13 10 12 r
14 10 13 r

CommentReview
------------------------
ReviewID UserID Comments
1 2 c
2 1 c
5 6 c
5 7 c
6 5 c
6 7 c
7 5 c
7 6 c
10 11 c
10 12 c
10 13 c
10 14 c
11 10 c
11 12 c
11 13 c
11 14 c
12 10 c
12 11 c
12 13 c
12 14 c
13 10 c
13 11 c
13 12 c
13 14 c
14 10 c
14 11 c
14 12 c
14 13 c

附2 建库脚本：
USE [test]
GO
CREATE TABLE [dbo].[ReviewBook](
[ReviewID] [int] NOT NULL,
[BookID] [int] NULL,
[UserID] [int] NULL,
[Reviews] [nvarchar](50) NULL,
CONSTRAINT [PK_ReviewBook] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (1, 1, 1, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (2, 2, 2, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (3, 3, 3, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (4, 4, 4, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (5, 5, 5, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (6, 6, 6, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (7, 7, 7, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (8, 8, 8, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (10, 9, 9, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (11, 10, 10, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (12, 10, 11, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (13, 10, 12, N'rrr')
INSERT [dbo].[ReviewBook] ([ReviewID], [BookID], [UserID], [Reviews]) VALUES (14, 10, 13, N'rrr')

GO

CREATE TABLE [dbo].[CommentReview](
[ReviewID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[Comments] [nvarchar](50) NULL,
CONSTRAINT [PK_CommentReview] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC,
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (1, 2, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (2, 1, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (5, 6, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (5, 7, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (6, 5, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (6, 7, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (7, 5, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (7, 6, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 11, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 12, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 13, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 14, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 10, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 12, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 13, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 14, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 10, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 11, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 13, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 14, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 10, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 11, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 12, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 14, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 10, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 11, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 12, N'c')
INSERT [dbo].[CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 13, N'c')

如果那里没说明白可以跟贴说明.

分数可以增加增加增加,希望能够解决这个问题.

...全文

777 点赞收藏 45

写回复

45 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

CNASP 2010-10-15

To: billpu
在code中:
INSERT test SELECT a.reviewid,a.userid FROM commentreview a INNER JOIN commentreview b
ON a.reviewid=b.userid AND a.userid=b.reviewid ORDER BY a.reviewid --创建自连接,排除差错记录
GO
-------------------------------------------
a.reviewid=b.userid AND a.userid=b.reviewid 这样用起来没有任何意义啊，虽然我写的user id 和 reviewid都是1,2,3,4, 可是真正的userid是不可能和 reviewid一样的，比如 G38455是userid,而reviewid可能是,1或2，或3，在给出的code中直接把 reviewid和userid 作为内联条件，出来的结果也不具有任何意义?

回复赞

billpu 2010-10-15

这里我多加了一条不符合条件的记录14,15
因为我觉得关键在自连接上,简单分析一下,表达能力比较差呵呵
自连接后并且子节点等于父节点并且父节点等于子节点的记录可以认为满足你的互评的要求
然后罗列所有的这样的记录,合并成满足记录的所有记录的字符串,排序筛选

回复赞

CNASP 2010-10-15

To:billpu
高手，我看得也头晕，容我理解理解，有问题再请教。

另请教可以用一条查询语句的解决的可能性？

回复赞

baiwmg1 2010-10-15

应该写点注释，这样就好看多了

回复赞

billpu 2010-10-15

今天想的我头晕,本来想用游标的方式遍历数据后来写的过程中发现把问题给想复杂了,利用自连接即可(因为节点只有一层),仓促写了出来有点乱大致的思路是用自连接排除不符合条件的数据,然后用自定义函数拼接符合条件的数据,最后过滤数据,另外我稍微改了一下你的表结构,因为看的眼花连接你可以自己做上去,语句写的也不够精简,凑合看吧

SET NOCOUNT ON



IF OBJECT_ID('commentreview')>0

DROP TABLE commentreview



CREATE TABLE [CommentReview](

[ReviewID] [int] NOT NULL,

[UserID] [int] NOT NULL,

[Comments] [nvarchar](50) NULL,

)

GO



INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (1, 2, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (2, 1, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (5, 6, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (5, 7, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (6, 5, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (6, 7, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (7, 5, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (7, 6, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 11, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 12, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 13, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (10, 14, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 10, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 12, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 13, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (11, 14, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 10, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 11, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 13, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (12, 14, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 10, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 11, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 12, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (13, 14, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 10, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 11, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 12, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 13, N'c')

INSERT [CommentReview] ([ReviewID], [UserID], [Comments]) VALUES (14, 15, N'c')



IF OBJECT_ID('test')>0 

DROP TABLE test

GO

CREATE TABLE test (reviewid int,userid int)

GO



INSERT test SELECT a.reviewid,a.userid FROM commentreview a INNER JOIN commentreview b

ON a.reviewid=b.userid  AND a.userid=b.reviewid ORDER BY a.reviewid --创建自连接,排除差错记录

GO



IF OBJECT_ID('f_str')>0

DROP FUNCTION f_str

GO



CREATE FUNCTION f_str(@reviewid VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @str VARCHAR(100)

SET @str=''

SELECT @str=@str+','+LTRIM(userid) FROM test WHERE reviewid=@reviewid

RETURN (@reviewid+','+STUFF(@str,1,1,''))

END

GO



--如果1,2和2,1你认为是同一个你可以再筛选一下

CREATE  TABLE test1 (reviewid varchar(100))

INSERT test1 SELECT dbo.f_str(reviewid) result  FROM test GROUP BY reviewid

--SELECT * FROM test1



IF OBJECT_ID('f_order')>0 

DROP FUNCTION f_order

GO



CREATE FUNCTION f_order(@value VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @tb TABLE (reviewid VARCHAR(100))

  WHILE CHARINDEX(',',@value)>0

  BEGIN

  INSERT @tb SELECT LEFT(@value,CHARINDEX(',',@value)-1)

  SET @value=RIGHT(@value,LEN(@value)-CHARINDEX(',',@value))

  END 

  INSERT @tb SELECT @value

DECLARE @str VARCHAR(100)

SET @str=''

SELECT @str=@str+','+reviewid FROM @tb  ORDER BY reviewid

RETURN (STUFF(@str,1,1,''))

END

GO

SELECT DISTINCT dbo.f_order(reviewid) FROM test1



SET NOCOUNT OFF



/*

1,2

10,11,12,13,14

5,6,7

*/

回复赞

dawugui 2010-10-15

没看懂,只能帮顶了.

回复赞

CNASP 2010-10-15

感谢大家给的方案，给我帮助很多，剩下的问题我自己慢慢学习，结贴前谢谢各位的热心参与，受益良多。

回复赞

SQL2088 2010-10-15

[Quote=引用 42 楼 cnasp 的回复:]
引用 39 楼 sql2088 的回复:
看33楼

33楼的也测试了，使用方案2, 在38楼数据的基础上，也就是33楼数据再加上 (1,4}{4,1},结果只能拿到
1,2,4
10,12,13
11,13
5,6,7
其中，4和2没有发生任何关系也被并为一组了。
如果使用33楼写的方案1则得到
1,2,4
10,11,12,13,14
5,6,7
两个结果都不正确，抱……
[/Quote]确实有点问题，明天有时间再看看

回复赞

SQLCenter 2010-10-15

这个问题很头大，全部找出来不难，难在分组。

回复赞

CNASP 2010-10-15

To:mengnnou
其实这句话在整个查询中意义不大，主要是表现一种约束，同一个用户对一个书评(Review)只能评价(Comment)一次. 反映在 CommentReview表中就是把ReviewID,UserID作为联合主键。

回复赞

mengnnou 2010-10-15

其中每个书评(Review)只能有一个(Comment).

啥意思？

回复赞

CNASP 2010-10-15

正确结果应该为
1,2
1,4
10,11,12,13,14
5,6,7

coleling的方案应该正确，可不知为何我得不到正确结果..

回复赞

CNASP 2010-10-15

[Quote=引用 39 楼 sql2088 的回复:]
看33楼
[/Quote]
33楼的也测试了，使用方案2, 在38楼数据的基础上，也就是33楼数据再加上 (1,4}{4,1},结果只能拿到
1,2,4
10,12,13
11,13
5,6,7
其中，4和2没有发生任何关系也被并为一组了。
如果使用33楼写的方案1则得到
1,2,4
10,11,12,13,14
5,6,7
两个结果都不正确，抱歉我找不到具体的原因，只能从结果上进行比较。

另外使用coleling的方案只能得到:
1,2
10,12
10,13
11,13
5,6,7

而正确的应该是
1,2
1,4
10,12,13
11,13
5,6,7
不知道我这样说，能不能说明问题

回复赞

CNASP 2010-10-15

明白，我的意思是暂时不考虑效率问题。另外coleling给的方法我得不到他的结果，也找不到原因,是数据的原因还是...

回复赞

billpu 2010-10-15

[Quote=引用 37 楼 cnasp 的回复:]

To billpu:
不很能理解 reviewid=userid 等式关系，另如在25楼的查询中，如coleling指出的，1，2，4并无关系，请问如何解决.

To coleling:
请问22楼结果
1,2
10,11,12,13,14
5,6,7
是在那些数据的基础上得到的,我在原始数据上测试后得到的是:
1,2
10,12
10,13
11,13
5,6,7……
[/Quote]
我想了下,2000没想出太好的方法或许可以再写个自定义函数递归,或者游标遍历结果集然后过滤,我想了半天都觉得如果你的表数据量大这样方法效率肯定不高,你可以参考下33楼的方案2,2005用with as的公用表达式方法,不管是语句的简介度或者思路都简洁的多效果可能也更好,不过2005我不太熟家里也没有环境所以测试不了,你自己测试一下,另外你的测试语句的数据确实不够不够丰富呵呵

回复赞

CNASP 2010-10-15

数据不一致可能会导致结果上的差异，这是我现有用于测试的;
CommentReview:
ReviewID UserID Comments
1 2 c
2 1 c
5 6 c
5 7 c
6 5 c
6 7 c
7 5 c
7 6 c
10 11 c
10 12 c
10 13 c
10 14 c
11 10 c
11 12 c
11 13 c
11 14 c
12 10 c
12 11 c
12 13 c
12 14 c
13 10 c
13 11 c
13 12 c
13 14 c
14 10 c
14 11 c
14 12 c
14 13 c

ReviewBook:
ReviewID BookID UserID Reviews
1 1 1 rrr
2 2 2 rrr
3 3 3 rrr
4 4 4 rrr
5 5 5 rrr
6 6 6 rrr
7 7 7 rrr
8 8 8 rrr
10 9 9 rrr
11 10 10 rrr
12 10 11 rrr
13 10 12 rrr
14 10 13 rrr

回复赞

CNASP 2010-10-15

To billpu:
不很能理解 reviewid=userid 等式关系，另如在25楼的查询中，如coleling指出的，1，2，4并无关系，请问如何解决.

To coleling:
请问22楼结果
1,2
10,11,12,13,14
5,6,7
是在那些数据的基础上得到的,我在原始数据上测试后得到的是:
1,2
10,12
10,13
11,13
5,6,7
看不出来是哪里出了问题.

回复赞

coleling 2010-10-15

[Quote=引用 32 楼 mengnnou 的回复:]

楼主给你的测试数据有问题，13没有评价12,11没有评价10，楼上几位的代码怎么能得出10,11,12,13,14均互相评价过？
[/Quote]

楼主给的数据确实有问题，不过反正不影响楼主意思的表达，我就自己把后面几条数据给改了。

回复赞

yuhuiwqvb 2010-10-15

额，好难。。。

回复赞

billpu 2010-10-15

恩谢谢coleling提醒做聚合的时候确实没有判断包含节点的关系,需要修改一下

回复赞

加载更多回复