mysql 查询出一个字段对应另外一字段的2值(在线等)

Question

见表语句
CREATE TABLE `test_3` (
`jid` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`eid` INT( 10 ) NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
`orderid` INT( 10 ) NOT NULL ,
PRIMARY KEY ( `jid` ) 
) ENGINE = MYISAM 测试数据
INSERT INTO test_3(jid, eid, name,orderid)VALUES(1, 2, "数据挖掘" ,1);INSERT INTO test_3(jid, eid, name,orderid)VALUES(2, 2, "退退",2);INSERT INTO test_3(jid, eid, name,orderid)VALUES(3, 3, "测试1",1);INSERT INTO test_3(jid, eid, name,orderid)VALUES(4, 3, "大数据量",2);INSERT INTO test_3(jid, eid, name,orderid)VALUES(5, 3, "人员数目",3);INSERT INTO test_3(jid, eid, name,orderid)VALUES(6, 4, "测试2",1);INSERT INTO test_3(jid, eid, name,orderid)VALUES(7, 5, "测试3",1);要求：
根据eid查询出，jid对应的name值和jid值，jid和name用逗号连接起来，并且按orderid升序值查询出2个值，小于2个值查出它的1个值查询结果如下：
+--------------+-------+------------+
| eid | jid    | name       |
+--------------+-------+------------+
|   2 | 1,2    | 数据挖掘,退退      |
|   3 | 3,4    | 测试1,大数据量     |
|   4 | 6      | 测试3              |
|   5 | 7      | 测试2              |
+--------------+-------+------------+

Answer 1

mysql> select * from test_3;
+-----+-----+----------+---------+
| jid | eid | name     | orderid |
+-----+-----+----------+---------+
|   1 |   2 | 数据挖掘 |       1 |
|   2 |   2 | 退退     |       2 |
|   3 |   3 | 测试1    |       1 |
|   4 |   3 | 大数据量 |       2 |
|   5 |   3 | 人员数目 |       3 |
|   6 |   4 | 测试2    |       1 |
|   7 |   5 | 测试3    |       1 |
+-----+-----+----------+---------+
7 rows in set (0.08 sec)mysql> select eid,group_concat(jid),group_concat(name)
    -> from test_3
    -> group by eid;
+-----+-------------------+-------------------------+
| eid | group_concat(jid) | group_concat(name)      |
+-----+-------------------+-------------------------+
|   2 | 1,2               | 数据挖掘,退退           |
|   3 | 3,4,5             | 测试1,大数据量,人员数目 |
|   4 | 6                 | 测试2                   |
|   5 | 7                 | 测试3                   |
+-----+-------------------+-------------------------+
4 rows in set (0.08 sec)mysql>

Answer 2

code=SQL]@ACMAIN_CHM 谢谢您的回复
但是还有个问题就是
eid 和 name 最多只取2个值 
即：| 3 | 3,4  | 测试1,大数据量|[[/code]

Answer 3

mysql> select eid,group_concat(jid),group_concat(name)
    -> from test_3
    -> where orderid<=2
    -> group by eid;
+-----+-------------------+--------------------+
| eid | group_concat(jid) | group_concat(name) |
+-----+-------------------+--------------------+
|   2 | 1,2               | 数据挖掘,退退      |
|   3 | 3,4               | 测试1,大数据量     |
|   4 | 6                 | 测试2              |
|   5 | 7                 | 测试3              |
+-----+-------------------+--------------------+
4 rows in set (0.00 sec)mysql>