前言

最近开发的系统中有个在线咨询功能。学生在前台提交咨询信息，教师可以登录后台回复咨询。该功能设计是直接使用一张表，使用是否开始标识该条记录是否是咨询的开始，然后使用一个会话id标识是属于一次咨询，根据创建时间排序，最后就像聊天一样。

后来遇到一个需求，就是需要查询出指定教师回复的咨询信息的第一条问和第一条答。先查询出所有开始的问，然后使用会话id内连接加子查询。<!--more-->然而遇到的问题是教师可能有多条回答，如果直接使用mybaits返回一个list，然后再去取第一条不是不可以。但是还是想直接使用sql完成，经过一番查找找到：

row_number() over(partition by  col1 ORDER BY col2 ASC )@H_403_7@ 
(ps：以上问题如果有更好解决办法的欢迎评论)
 
数据库
 
我将项目的数据库表进行改造并插入数据
 
 
-- ----------------------------
-- Table structure for zxzx
-- ----------------------------
CREATE TABLE "ZXZX" (
"ID" VARCHAR2(64 BYTE) NOT NULL,"CONSULT_ID" VARCHAR2(255 BYTE) NULL,"CONTENT" VARCHAR2(255 BYTE) NULL,"CREATE_DATE" DATE NULL,"IS_START" VARCHAR2(255 BYTE) NULL,"USER_ID" VARCHAR2(64 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of zxzx
-- ----------------------------
INSERT INTO "ZXZX" VALUES ('12sassaasd','12123123','第一条问',TO_DATE('2018-03-14 21:09:26','YYYY-MM-DD HH24:MI:SS'),'1','111');
INSERT INTO "ZXZX" VALUES ('213123qwewq','第一次回答第一条问',TO_DATE('2018-03-14 22:10:16','0','001');
INSERT INTO "ZXZX" VALUES ('sasdass2342','第二次回答第一条问',TO_DATE('2018-03-14 22:11:07','001');
INSERT INTO "ZXZX" VALUES ('23234wewer','第一条问追问',TO_DATE('2018-03-14 22:22:00','111');
INSERT INTO "ZXZX" VALUES ('345','回答一条追问',TO_DATE('2018-03-14 23:12:55','001');
INSERT INTO "ZXZX" VALUES ('234324','334455','第二条问',TO_DATE('2018-03-14 23:08:21','111');
INSERT INTO "ZXZX" VALUES ('5623365','回答第二条问',TO_DATE('2018-03-14 23:14:07','001');
INSERT INTO "ZXZX" VALUES ('12314','112233','问问问',TO_DATE('2018-03-14 23:14:49','111');
INSERT INTO "ZXZX" VALUES ('12342','答答答',TO_DATE('2018-03-14 23:15:15','002');

-- ----------------------------
-- Indexes structure for table zxzx
-- ----------------------------

-- ----------------------------
-- Checks structure for table zxzx
-- ----------------------------
ALTER TABLE "ZXZX" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table zxzx
-- ----------------------------
ALTER TABLE "ZXZX" ADD PRIMARY KEY ("ID");@H_403_7@ 
 
数据查询
 
这里需要将user_id=001所回答的问题查询出，也就是上图红框中的信息。
 
 
 
首先查询001的回答，这里就可能每个会话有多条，使用前面提到的row_number() over()便可以解决重复问题：
 
 
SELECT
       row_number()  over(partition by  consult_id  ORDER BY create_date ASC ) as rn,id,consult_id,content,user_id,is_start,create_date      
FROM zxzx  
WHERE user_id = '001' AND is_start !='1'@H_403_7@ 
 
 
 
接着上面的查询，可以看出排序分组之后的数据都有一个rn编号，接着将rn为一取出即可：
 
 
 
 
 
然后需要查询出所有的问的记录。将上面作为子查询，使用内连接并用consult_id字段连接。
 
 
select
   w.id,w.consult_id,w.content,w.user_id,w.is_start,w.create_date,h.id,h.consult_id,h.content,h.user_id,h.is_start,h.create_date
from zxzx w inner join (
  select * from(
    SELECT
           row_number()  over(partition by  consult_id  ORDER BY create_date ASC ) as rn,create_date      
    FROM zxzx  
    WHERE user_id = '001' AND is_start !='1'
  )where rn = 1
) h on h.consult_id = w.consult_id 
where w.is_start = '1'@H_403_7@ 
 
 
最后查询结果
 
 
 
知识点
 
数据库结果去重
 
上面说到使用row_number() over()函数获取根据某个字段分组后的一条数据，这就有点像“去重”。在Oracle中还可以使用distinct来返回唯一值。
 
 
 
distinct关键字，语法如下：
 
 
SELECT DISTINCT col FROM table_name@H_403_7@ 
​ 在使用关键字 distinct 的时候，主要是要知道其作用于单个字段和多个字段的时候是有区别的：
 
 
 
作用于单个字段时，其“去重”的是表中所有该字段值重复的数据；
 
 
作用于多个字段的时候，其“去重”的表中所有字段（即 distinct 具体作用的多个字段）值都相同的数据。
 
 
 
 
row_number() over() 函数，语法如下：
 
 
row_number() over(partition by  col1 ORDER BY col2 ASC )@H_403_7@ 
​ row_number()函数用于给记录进行标号，over()函数的作用是将表中的记录进行分组和排序。语法类似上面：将表中的数据按照col1进行分组，按照col2排序。partition by：表示分组。该函数主要是根据分组进行排序之后然后取出第一条数据以实现“去重”
 
连接查询
 
 
 
内连接inner join on：只显示两张表都匹配的结果；
 
 
左外连接left join on：以左边的表为基准，显示出左表的全部字段和右表所匹配的字段，空的用null表示；
 
 
右外连接right join on：与左外连接相反，右外连接会查询出右表的所有字段，显示左表与右表匹配的字段，空用null表示；
 
 
全连接full join on：显示两张表全部内容。
 
 原文链接：https://www.f2er.com/oracle/206308.html