跨表查询，条件实际上要精简的多

最新推荐文章于 2023-03-17 19:55:11 发布

秦伟H

最新推荐文章于 2023-03-17 19:55:11 发布

阅读量763

点赞数

分类专栏：服务器 sql

本文链接：https://blog.csdn.net/huqinweI987/article/details/43573201

版权

服务器同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

sql

7 篇文章 0 订阅

订阅专栏

详细过程待更新

为了满足前端的便利需求，拼了个比较恶心的json格式：

# 方案一：
# {
# 好友心情列表单条：（好友ID）用户名，图片url,更新时间，我赞他的类型,他被三种赞的统计,
# "feelinglist":[
# 第一个userid是自己
# {"userid":15911086638, "picurl":"asda/adasd/aczxc", "updatetime":"20141111 15:30","praisetype":"1",
# "bepraiselist":[
# {"userid":18500339741,"praisetype":"3"},
# {"userid":18500339740,"praisetype":"0"},
# {"userid":18500339740,"praisetype":"1"}
# ],
# "praise1count":"222", "praise2count":"333", "praise3count":"1113"},
# {"userid":18500339741, "picurl":"asda/adasd/aczxc", "updatetime":"20141111 15:30","praisetype":"1",
# "bepraiselist":"",
#"praise1count":"222", "praise2count":"333", "praise3count":"1113"},
# {"userid":18500339740, "picurl":"asda/adasd/aczxc", "updatetime":"20141111 15:30","praisetype":"2",
# "bepraiselist":"",
#"praise1count":"222", "praise2count":"333", "praise3count":"1113"}
# ]
# }

sql函数里其他变量数组什么的不熟悉，所以选用了游标，争取一个select把结果插到游标里，再不济也就两个，其中一个是针对自己的情况

为了避免复杂的跨表联查加嵌套查询，把一些没必要单独查询统计的信息直接插到心情列表里了（之前是去好友的互动关系表去select，这样比较难用一句select出来）

Id,user_id,picture_url,update_time,praise_1,praise_2,praise_3,total_praise_1,total_praise_2,total_praise_3,total_praise

最后精简到朋友列表和心情列表，朋友列表有对心情的赞之类的互动关系，心情列表则是心情图片的链接和更新时间以及赞的数量以及历史统计。

之前一直想的先刷出好友列表,确定搜索范围，再拿好友列表去搜，没必要，一个friend_list.user_id = _llUserId就搞定

最终cursor：只用一个简单条件（state = 0代表当前是好友，1代表删除的，方便找回等操作）

# DECLARE cur CURSOR FOR

SELECT user_feeling.user_id,user_feeling.picture_url,user_feeling.update_time,friend_list.feeling_praise_type,user_feeling.praise_1,user_feeling.praise_2,user_feeling.praise_3

FROM user_feeling LEFT JOIN friend_list

ON friend_list.friend_id = user_feeling.user_id #联结，关键不是一对一关系，在friend_list中friend_id不是唯一，是每个好友向你辐射一个

WHERE friend_list.user_id = 15911086638 AND state = 0;

别人的最终方案：

通过数据表简化了一些复杂条件（尽管我不知道是否冗余，因为产品的实际需求只有一个心情，不是朋友圈的多状态共存，但是这种互联网创业公司要的是开发效率，不是优化，说不准过两天功能就没了）

DROP TABLE IF EXISTS `user_feeling_content`;
CREATE TABLE `user_feeling_content` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `picture_url` varchar(255) DEFAULT NULL COMMENT '心情图片链接',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `user_feeling_praise`
-- ----------------------------
DROP TABLE IF EXISTS `user_feeling_praise`;
CREATE TABLE `user_feeling_praise` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `feeling_id` int(11) DEFAULT NULL COMMENT '心情ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '谁赞的',
  `praise_type` int(11) DEFAULT NULL COMMENT '赞类型',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

然后，sql函数是这样的

BEGIN    
     DECLARE pcRet varchar(2048) DEFAULT "{\"feelinglist\":["; 
     
     DECLARE m_feeling_id int DEFAULT 0;   
     DECLARE m_user_id Bigint(20) DEFAULT 0;
     DECLARE m_picture_url Varchar(255); 
     DECLARE m_create_time DateTime;
     DECLARE iCount int DEFAULT 0;
     DECLARE m_iZFeeling int DEFAULT 0;#自己是否赞过某心情
     DECLARE m_praise_type int DEFAULT 0;
     DECLARE m_praise_count0 int DEFAULT 0;
     DECLARE m_praise_count1 int DEFAULT 0;
     DECLARE m_praise_count2 int DEFAULT 0;
     DECLARE m_praise_count3 int DEFAULT 0;
      
     DECLARE feeling_done int DEFAULT FALSE;
     DECLARE feeling_cur CURSOR FOR select * FROM (SELECT id,user_id,picture_url,create_time 
	  FROM user_feeling_content WHERE user_id = _lluser OR user_id IN (SELECT friend_id FROM friend_list WHERE user_id = _lluser) 
	  ORDER BY create_time DESC ) as user_feeling_content2 GROUP BY user_id ORDER BY create_time DESC;
	  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET feeling_done = true;
     
     #{"userid":15911086638, "picurl":"asda/adasd/aczxc", "updatetime":"20141111 15:30","praisetype":"1","praise1count":"222", "praise2count":"333", "praise3count":"1113",  
     OPEN feeling_cur;
     #开始循环
       feeling_loop:LOOP
         FETCH feeling_cur INTO m_feeling_id,m_user_id,m_picture_url,m_create_time; # 提取游标里的数据
        
         IF feeling_done THEN
           LEAVE feeling_loop;
         END IF;
         
         IF iCount > 0 THEN
            SET pcRet = CONCAT(pcRet, ",");
         END IF;
         
         SET iCount = iCount + 1;#计算总共获取的心情数

			SET pcRet = CONCAT(pcRet, "{\"userid\":\"", m_user_id, "\",\"picurl\":\"", m_picture_url, "\",\"updatetime\":\"", unix_timestamp(m_create_time), "\",");
			#判断是是否赞了该心情
			SELECT count(*),praise_type INTO m_iZFeeling,m_praise_type FROM user_feeling_praise WHERE feeling_id = m_feeling_id AND user_id = _lluser;
			IF m_iZFeeling = 0 THEN
            SET pcRet = CONCAT(pcRet, "\"praisetype\":\"0\",");
         ELSE
         	SET pcRet = CONCAT(pcRet, "\"praisetype\":\"", m_praise_type, "\",");
         END IF;
         #三种类型赞的总数
         BEGIN
       /*  SELECT COUNT(*), SUM(CASE  WHEN praise_type='1' THEN 1 ELSE 0 END) AS 'count1',  
			SUM(CASE  WHEN praise_type='2' THEN 1 ELSE 0 END) AS 'count2',  
			SUM(CASE  WHEN praise_type='3' THEN 1 ELSE 0 END) AS 'count3' 
			INTO m_praise_count0,m_praise_count1,m_praise_count2,m_praise_count3
			FROM user_feeling_praise WHERE feeling_id = m_feeling_id GROUP BY feeling_id;*/
			select count(*) INTO m_praise_count1 FROM user_feeling_praise WHERE feeling_id = 2 AND praise_type='1';
			select count(*) INTO m_praise_count2 FROM user_feeling_praise WHERE feeling_id = 2 AND praise_type='2';
			select count(*) INTO m_praise_count3 FROM user_feeling_praise WHERE feeling_id = 2 AND praise_type='3';
			SET pcRet = CONCAT(pcRet, "\"praise1count\":\"", m_praise_count1, "\",\"praise2count\":\"", m_praise_count2, "\",\"praise3count\":\"", m_praise_count3, "\",");
			END;
			#循环该心情的所有赞
			SET pcRet = CONCAT(pcRet, "\"bepraiselist\":[");
			BEGIN
				DECLARE m_user_id2 Bigint(20) DEFAULT 0;
				DECLARE m_praise_type2 int DEFAULT 0;
				DECLARE iPraiseCount int DEFAULT 0;
				DECLARE praise_done int DEFAULT FALSE;
				DECLARE praise_cur CURSOR FOR select user_id,praise_type from user_feeling_praise where feeling_id=m_feeling_id;#这里还需要注意是不是排除自己，还有时间顺序
				
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET praise_done = true;
				OPEN praise_cur;
	     		#开始循环
	       	praise_loop:LOOP
	         FETCH praise_cur INTO m_user_id2,m_praise_type2; # 提取游标里的数据
	        
	         IF praise_done THEN
	           LEAVE praise_loop;
	         END IF;
	         
	         IF iPraiseCount > 0 THEN
	            SET pcRet = CONCAT(pcRet, ",");
	         END IF;
	         
	         SET iPraiseCount = iPraiseCount + 1;#计数
				#{"userid":18500339741,"praisetype":"3"},
				SET pcRet = CONCAT(pcRet, "{\"userid\":\"", m_user_id2, "\",\"praisetype\":\"", m_praise_type2,"\"}");
			
				END LOOP praise_loop;
	     		CLOSE praise_cur;
     		END;
			#结束赞列表循环
			SET pcRet = CONCAT(pcRet, "]}");
	
      END LOOP feeling_loop;
     	CLOSE feeling_cur;
                     
      # 结尾
      SET pcRet = CONCAT(pcRet,"]}");   

     RETURN pcRet;     
 END

Id,user_id,picture_url,update_time,praise_1,praise_2,praise_3,total_praise_1,total_praise_2,total_praise_3,total_praise

最后精简到朋友列表和心情列表，朋友列表有对心情的赞之类的互动关系，心情列表则是心情图片的链接和更新时间以及赞的数量以及历史统计。

之前一直想的先刷出好友列表,确定搜索范围，再拿好友列表去搜，没必要，一个friend_list.user_id = _llUserId就搞定

最终cursor：只用一个简单条件（state = 0代表当前是好友，1代表删除的，方便找回等操作）

别人的最终方案：

通过数据表简化了一些复杂条件，然后，sql函数是这样的

秦伟H

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
跨表查询，条件实际上要精简的多

详细过程待更新为了满足前端的便利需求，拼了个比较恶心的json格式：# 方案一：# {# 好友心情列表单条：（好友ID）用户名，图片url,更新时间，我赞他的类型,他被三种赞的统计,# "feelinglist":[# 第一个userid是自己# {"userid":15911086638, "picurl"
复制链接

扫一扫