有些难的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')

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

分数可以增加增加增加,希望能够解决这个问题.
...全文
816 45 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
45 条回复
切换为时间正序
请发表友善的回复…
发表回复
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提醒 做聚合的时候确实没有判断包含节点的关系,需要修改一下
加载更多回复（24）
Oracle查询排列组合,Oracle SQL排列组合之组合问题
简单表述如下：表结构 c4列表示状态，c1，c2，c3只要一个不为空，c4就为Y现在想知道每个组合的情况 比如为什么组合(c1、c2)的值是2，因为只有2行(第1行、第3行)数据满足c1、c2都不为空相关测试sql代码如下：SQL>...
SQL - 多字段组合升序ASC降序DESC
在SQL语言中，字段的排序默认是升序 所以以下数据表的升序查找可以有三种写法： （取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出） （1）SELECT device_id,gpa,age from ...
SQL排列组合
排列组合是针对离散数据常用的数据组织方法，本节将分别介绍排列、组合的SQL实现方法，并结合实例着重介绍通过组合对数据的处理如何使用SQL实现排列与组合？本节将通过介绍分组对比统计场景，抽象出通用的解决方案...
C# SqlServer 未定字段未定条件的组合查询写法
 用户提了一种需求是关于数据库组合查询的，之前也封装过组合查询的存储过程，但是之前那个实现起来很简单，写个存储过程，剩下的就是传参了。我今天遇到的这个还不能写成一个存储过程，因为数据库出来SqlServer...
sqlserver 穷举 排列 组合
困扰我一天的一个算法问题，忽然灵光一闪，想到了，呵呵，...问 : 从 n个数中取r个数 穷举显示所有排列 和 组合T-sql 解：IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[number]'...
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章