Home / Questions / 131550
Accepted
Diablo
Asked: 2020-01-17 14:50:19 +0800 CST 2020-01-17 14:50:19 +0800 CST

错误 #1241 MySQL 子查询

  • 772

如何创建两个子查询MySQL？高效和优化。

当前代码向我抛出#1241 - Operando debe tener 1 columna(s)了phpMyAdmin的错误。

  • 第一个子查询计算可用类的总数。
  • 第二个子查询我想计算平均投票数和总投票数。

我的代码：

$sql = "SELECT video.title,video.icon,video.duration,video.url,video.author,level.level,
             (
                SELECT COUNT(*) 
                  FROM `lessons`
                 WHERE video.ident_video = lessons.ident_video
             ),
             (   
                SELECT AVG(vote) AS avg_vote, COUNT(*) votos
                  FROM `coments`
                 WHERE video.ident_video = coments.ident_video
             )                  
          FROM `video` 
     LEFT JOIN `level` on video.ident_level = level.ident_level
         WHERE video.ident_state=? ORDER BY video.ident_video DESC LIMIT 5";

错误：错误 #1241 - 操作数必须有 1 列

表SQL：

CREATE TABLE `level` (
  `ident_level` smallint(3) unsigned not null auto_increment primary key,
  `level` varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `state` (
  `ident_state` smallint(3) unsigned not null auto_increment primary key,
  `state` varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `video` (
  `ident_video` int(11) unsigned not null auto_increment primary key,  
  `title` varchar(255) not null,  
  `icon` varchar(100) not null,    
  `duration` varchar(10) not null,
  `url` varchar(100) not null unique,      
  `author` text not null,
  `ident_level` smallint(3) unsigned not null,
  `ident_state` smallint(3) unsigned not null,   
  foreign key(`ident_level`) references `level`(`ident_level`)
  on delete cascade on update cascade,
  foreign key(`ident_state`) references `state`(`ident_state`)
  on delete cascade on update cascade  
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `lessons` (
  `ident_lesson` smallint(11) unsigned not null auto_increment primary key,
  `page` int(11) unsigned not null,
  `title` varchar(100) not null,
  `url` varchar(100) not null unique,    
  `status` smallint(1) not null,  
  `ident_video` int(11) unsigned not null,   
   foreign key (`ident_video`) references `video`(`ident_video`)
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE coments(
  `ident_coment` smallint(11) unsigned not null auto_increment primary key,
  `title` varchar(255) not null,
  `coment` text not null,
  `vote` float(3) not null,         
  `ident_video` int(11) unsigned not null,
  `ident_user` int(11) unsigned not null,      
  `status` smallint(1) unsigned not null,
   foreign key (`ident_video`) references `video`(`ident_video`)
   on delete cascade on update cascade,
   foreign key(`ident_user`) references `user`(`ident_user`)
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;
mysql

1 Answers

  1. Best Answer

    Bulten 我试图重现您的数据。

    您可以尝试JOIN在子查询中使用，这样您就可以使用您在里面选择的列。

    它看起来或多或少像这样（为简单起见，我删除了一些列）：

    SELECT v.title, v.icon, lv.level, ls.total_lessons, c.avg_vote, c.votos
FROM video_20180116 v  
LEFT JOIN
    (
         SELECT ident_video, COUNT(*) total_lessons
            FROM lessons_20180116 GROUP BY ident_video
    )    ls ON (v.ident_video = ls.ident_video)

LEFT JOIN
    (   
        SELECT AVG(vote) avg_vote, ident_video, COUNT(*) votos
        FROM coments_20180116 GROUP BY ident_video
    )   c ON (v.ident_video = c.ident_video)

LEFT JOIN level_20180116 lv on v.ident_level = lv.ident_level
WHERE v.ident_state=? ORDER BY v.ident_video DESC LIMIT 5;

    概念证明

    VER DEMO EN REXTESTER

    CREATE TABLE `level_20180116` (
  `ident_level` smallint(3) unsigned not null auto_increment primary key,
  `level` varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `state_20180116` (
  `ident_state` smallint(3) unsigned not null auto_increment primary key,
  `state` varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `video_20180116` (
  `ident_video` int(11) unsigned not null auto_increment primary key,  
  `title` varchar(255) not null,
  `icon` varchar(100) not null,        
  `ident_level` smallint(3) unsigned not null,
  `ident_state` smallint(3) unsigned not null,   
  foreign key(`ident_level`) references `level_20180116`(`ident_level`)
  on delete cascade on update cascade,
  foreign key(`ident_state`) references `state_20180116`(`ident_state`)
  on delete cascade on update cascade  
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `lessons_20180116` (
  `ident_lesson` smallint(11) unsigned not null auto_increment primary key,
  `title` varchar(100) not null,
  -- `status` smallint(1) not null,  
  `ident_video` int(11) unsigned not null,   
   foreign key (`ident_video`) references `video_20180116`(`ident_video`)
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE coments_20180116(
  `ident_coment` smallint(11) unsigned not null auto_increment primary key,
  `title` varchar(255) not null,
  -- `coment` text not null,
   `vote` float(3) not null,         
  `ident_video` int(11) unsigned not null,
  -- `ident_user` int(11) unsigned not null,      
  -- `status` smallint(1) unsigned not null,
   foreign key (`ident_video`) references `video_20180116`(`ident_video`)
   on delete cascade on update cascade -- ,
   -- foreign key(`ident_user`) references `user`(`ident_user`)
   -- on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

INSERT INTO level_20180116 (level) VALUES ('L1'),('L2');

INSERT INTO state_20180116 (state) VALUES ('st1'),('st2');

INSERT INTO video_20180116 (title,ident_level,ident_state,icon) VALUES 
('v1',1,1,'ic1'),
('v2',1,1,'ic2'),
('v3',2,2,'ic3')
;

INSERT INTO lessons_20180116 (title,ident_video) VALUES 
('les1',1),
('les2',1),
('les3',2)
;

INSERT INTO coments_20180116 (title,ident_video, vote) VALUES 
('com1',1, 8.5),
('com2',1, 9.2),
('com3',2, 7.6)
;

SELECT v.title, v.icon, lv.level, ls.total_lessons, c.avg_vote, c.votos
FROM video_20180116 v  
LEFT JOIN
    (
         SELECT ident_video, COUNT(*) total_lessons
            FROM lessons_20180116 GROUP BY ident_video
    )    ls ON (v.ident_video = ls.ident_video)

LEFT JOIN
    (   
        SELECT AVG(vote) avg_vote, ident_video, COUNT(*) votos
        FROM coments_20180116 GROUP BY ident_video
    )   c ON (v.ident_video = c.ident_video)

LEFT JOIN level_20180116 lv on v.ident_level = lv.ident_level
WHERE v.ident_state=1 ORDER BY v.ident_video DESC LIMIT 5;

    结果

    title   icon    level   total_lessons   avg_vote            votos
v2      ic2     L1      1               7,59999990463257    1
v1      ic1     L1      2               8,84999990463257    2
    • 2