急急急急急急急急
我创建了两个表,我想通过写一个试图,取出两张表一些记录显示。但是总是报错!请各位前辈帮忙指正。
以下为两张表结构和数据。CREATE TABLE IF NOT EXISTS `class_user` (
`id` int(11) NOT NULL auto_increment,
`classid` int(11) NOT NULL COMMENT '班级id',
`idorder` int(11) NOT NULL default '0' COMMENT 'order表编号',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='班级学员表' AUTO_INCREMENT=42 ;--
-- 导出表中的数据 `class_user`
--INSERT INTO `class_user` (`id`, `classid`, `idorder`) VALUES
(34, 2, 8000),
(33, 2, 8001),
(32, 2, 8002),
(31, 2, 8003),
(30, 2, 8004),
(29, 2, 8005),
(28, 2, 8006);-- ----------------------------------------------------------
-- 表的结构 `order`
--CREATE TABLE IF NOT EXISTS `order` (
`id` int(11) NOT NULL auto_increment,
`student_name` varchar(256) character set utf8 collate utf8_unicode_ci NOT NULL COMMENT '学员姓名',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='班级学员表' AUTO_INCREMENT=8007 ;--
-- 导出表中的数据 `order`
--INSERT INTO `order` (`id`, `student_name`) VALUES
(8000, '张三'),
(8001, '李四'),
(8002, '王五'),
(8003, '小张'),
(8004, '小李'),
(8005, '小王'),
(8006, '小白');
我写的SQL语句:SELECT
(select student_name from order where id=class_user.idorder) as name
FROM class_user
我创建了两个表,我想通过写一个试图,取出两张表一些记录显示。但是总是报错!请各位前辈帮忙指正。
以下为两张表结构和数据。CREATE TABLE IF NOT EXISTS `class_user` (
`id` int(11) NOT NULL auto_increment,
`classid` int(11) NOT NULL COMMENT '班级id',
`idorder` int(11) NOT NULL default '0' COMMENT 'order表编号',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='班级学员表' AUTO_INCREMENT=42 ;--
-- 导出表中的数据 `class_user`
--INSERT INTO `class_user` (`id`, `classid`, `idorder`) VALUES
(34, 2, 8000),
(33, 2, 8001),
(32, 2, 8002),
(31, 2, 8003),
(30, 2, 8004),
(29, 2, 8005),
(28, 2, 8006);-- ----------------------------------------------------------
-- 表的结构 `order`
--CREATE TABLE IF NOT EXISTS `order` (
`id` int(11) NOT NULL auto_increment,
`student_name` varchar(256) character set utf8 collate utf8_unicode_ci NOT NULL COMMENT '学员姓名',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='班级学员表' AUTO_INCREMENT=8007 ;--
-- 导出表中的数据 `order`
--INSERT INTO `order` (`id`, `student_name`) VALUES
(8000, '张三'),
(8001, '李四'),
(8002, '王五'),
(8003, '小张'),
(8004, '小李'),
(8005, '小王'),
(8006, '小白');
我写的SQL语句:SELECT
(select student_name from order where id=class_user.idorder) as name
FROM class_user
(SELECT student_name FROM `order` WHERE id=class_user.idorder) AS `name `
FROM class_user
(select student_name from `order` where id=class_user.idorder) as name
FROM class_userorder是关键字
Subquery returns more than 1 row 什么意思
mysql> select * from class_user;
+----+---------+---------+
| id | classid | idorder |
+----+---------+---------+
| 34 | 2 | 8000 |
| 33 | 2 | 8001 |
| 32 | 2 | 8002 |
| 31 | 2 | 8003 |
| 30 | 2 | 8004 |
| 29 | 2 | 8005 |
| 28 | 2 | 8006 |
+----+---------+---------+
7 rows in set (0.00 sec)mysql> select * from order;
ERROR 1064 (42000): You have an e
corresponds to your MySQL server
' at line 1
mysql> select * from `order`;
+------+--------------+
| id | student_name |
+------+--------------+
| 8000 | 张三 |
| 8001 | 李四 |
| 8002 | 王五 |
| 8003 | 小张 |
| 8004 | 小李 |
| 8005 | 小王 |
| 8006 | 小白 |
+------+--------------+
7 rows in set (0.00 sec)mysql> SELECT (select student_name from `order` where id=class_user.idorder) asname
-> FROM class_user;
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
| 小张 |
| 小李 |
| 小王 |
| 小白 |
+------+
7 rows in set (0.00 sec)mysql>
select * from `class_user` a inner join `order` b on a.`idorder`=b.`id`
你的真实数据是什么,用上述数据
SELECT *,
(SELECT student_name FROM `order` WHERE id=class_user.idorder) AS `name `
FROM class_user
没有问题
imit 2;
+----+---------+---------+------+--------------+
| id | classid | idorder | id | student_name |
+----+---------+---------+------+--------------+
| 34 | 2 | 8000 | 8000 | 张三 |
| 33 | 2 | 8001 | 8001 | 李四 |
+----+---------+---------+------+--------------+
2 rows in set (0.05 sec)mysql>
select * from `class_user` a inner join `order` b on a.`idorder`=b.`id` limit 2
测试没有问题