mysql 子查询
0
[已解决问题]
解决于 2018-02-25 22:54
第一张表
第二张表
查询语句
SELECT v.id, v.pic, v.title, v.brief, v.tag, (SELECT GROUP_CONCAT(title) FROM lancer_video_tag WHERE id = (v.tag) AND is_show = 1) FROM lancer_video v WHERE v.is_pass = 1 AND v.is_show = 1 AND v.area = - 1 ORDER BY v.order_num DESC
得到结果
但是我觉得在最后一列的数据不对，应该是
问题补充：
这是建表语句
/* SQLyog Ultimate v8.32 MySQL - 5.6.38 : Database - db_lancer ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_lancer` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE `db_lancer`; /*Table structure for table `lancer_video` */ DROP TABLE IF EXISTS `lancer_video`; CREATE TABLE `lancer_video` ( `id` int(12) NOT NULL AUTO_INCREMENT COMMENT '主键 自增长', `user_id` int(12) DEFAULT NULL COMMENT '用户id', `video_url` varchar(256) NOT NULL COMMENT '视频链接地址', `pic` varchar(256) NOT NULL COMMENT '视频封面地址', `title` varchar(128) DEFAULT NULL COMMENT '视频标题', `video_time` int(12) NOT NULL DEFAULT '0' COMMENT '视频时长', `tag` varchar(256) DEFAULT NULL COMMENT '视频标签 格式：1，2，3', `star` int(8) NOT NULL DEFAULT '0' COMMENT '明星id', `area` int(8) NOT NULL DEFAULT '0' COMMENT '区域id -1--国内 -2--日本', `brief` varchar(256) DEFAULT NULL COMMENT '视频简介', `is_pass` tinyint(2) NOT NULL DEFAULT '0' COMMENT '审核是否通过 0--未通过 1--已通过', `is_show` tinyint(2) NOT NULL DEFAULT '1' COMMENT '是否展示 0--不展示 1--展示', `is_recommend` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否推荐到热门推荐页面 0--不推荐 1--推荐', `order_num` int(8) NOT NULL DEFAULT '0' COMMENT '视频排序', `watch_num` int(12) NOT NULL DEFAULT '0' COMMENT '视频前端观看人数', `click_num` int(12) NOT NULL DEFAULT '0' COMMENT '视频前端点赞人数', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COMMENT='蓝瑟视频表'; /*Data for the table `lancer_video` */ insert into `lancer_video`(`id`,`user_id`,`video_url`,`pic`,`title`,`video_time`,`tag`,`star`,`area`,`brief`,`is_pass`,`is_show`,`is_recommend`,`order_num`,`watch_num`,`click_num`,`create_time`) values (1,680000,'http://www.baidu.com','asdfasf','1',0,'5,2,3',1,-1,'sadfas',1,1,1,2,9,1,'2017-02-02 12:12:12'),(2,680000,'http://www.baidu.com','asdfasf','haha',0,'5,6,7',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(3,680000,'http://www.baidu.com','asdfasf','haha',0,'11,12,1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(4,680000,'http://www.baidu.com','asdfasf','haha',0,'2,5,7,8',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(5,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(6,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(7,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(8,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(9,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(10,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(11,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(12,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(13,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(14,680000,'http://www.baidu.com','asdfasf','haha',0,'1,2',1,-1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(15,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,-1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(16,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(17,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(18,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(19,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'),(20,680000,'http://www.baidu.com','asdfasf','haha',0,'1',1,1,'sadfas',1,1,1,1,1,1,'2017-02-02 12:12:12'); /*Table structure for table `lancer_video_tag` */ DROP TABLE IF EXISTS `lancer_video_tag`; CREATE TABLE `lancer_video_tag` ( `id` int(12) NOT NULL AUTO_INCREMENT COMMENT '主键 自增长', `title` varchar(64) NOT NULL COMMENT '标签名称', `is_show` tinyint(2) NOT NULL DEFAULT '1' COMMENT '是否展示 0--不展示 1--展示', `order_num` int(8) NOT NULL DEFAULT '0' COMMENT '排序字段', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='蓝瑟视频标签表'; /*Data for the table `lancer_video_tag` */ insert into `lancer_video_tag`(`id`,`title`,`is_show`,`order_num`,`create_time`) values (1,'动作',1,0,'2012-12-12 12:12:12'),(2,'喜剧',1,0,'2012-12-12 12:12:12'),(3,'爱情',1,0,'2012-12-12 12:12:12'),(4,'警匪',1,0,'2012-12-12 12:12:12'),(5,'恐怖',1,0,'2012-12-12 12:12:12'); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
最佳答案
1
子查询需要 GROUP BY （更新：这是错误的回答，不需要GROUP BY，正确的答案见下面的回答）
SELECT
v.id,
v.pic,
v.title,
v.brief,
v.tag,
(SELECT GROUP_CONCAT(title) FROM lancer_video_tag WHERE id = (v.tag) AND is_show = 1 GROUP BY title)
FROM lancer_video v
WHERE v.is_pass = 1
AND v.is_show = 1
AND v.area = - 1
ORDER BY v.order_num DESC
奖励园豆：5
您好，结果是一样的呢？
@阿森丶: 是 WHERE id = (v.tag) 的问题，改为下面的SQL就可以了
SELECT
v.id,
v.pic,
v.title,
v.brief,
v.tag,
(SELECT GROUP_CONCAT(title) FROM lancer_video_tag
WHERE INSTR(CONCAT(v.tag, ','), CONCAT(id, ',')) > 0 AND is_show = 1)
FROM lancer_video v
WHERE v.is_pass = 1
AND v.is_show = 1
AND v.area = - 1
ORDER BY v.order_num DESC@dudu: 谢谢，解决问题了
@dudu: 佩服