请教一个存储过程或sql的写法

    A表：id name work

     1  x   aaa

     2  y   bbb




B表：    id source

  1  32

  1  24

  1  65

  2  11

  2  33

 

要得出： id  name   work     source

  1    x    aaa   32，24，65

  2    y    bbb    11,33



这个应该怎么写呢？ 非常感谢！
问题补充：
还想问一下 一个mysql存储过程中返回多个结果集，比如：

Delimiter //

drop  procedure hello //

create procedure hello()

     begin

     DECLARE c CURSOR FOR

  SELECT ProjectID FROM tProject ;

select * from tProjectIssuedInvoice where Project ='122334574385';

        select * from tProjectIssuedInvoice where Project ='122252445176';



     end//

delimiter ;



我在用java调用这个存储过程的时候，怎么才能取到两个（实际情况是多个，循环得到出那个参数）结果集的数据阿？

create or replace function str_list( 
                    key_name in varchar2,
                    key in varchar2,
                    strname in varchar2,
                    tablename     in varchar2 )
return varchar2
as
    type rc is ref cursor;
    str    varchar2(4000);
    sep    varchar2(2);
    val    varchar2(4000);
    cur    rc;
begin
    open cur for 'select '||strname||'
                    from '|| tablename || '
                    where ' || key_name || ' = :x'
                using key;
    loop
        fetch cur into val;
        exit when cur%notfound;
        str := str || sep || val;
        sep := ', ';
    end loop;
    close cur;
    return str;
end;

试一下，上面的方法，我昨天碰到过的同样问题。。。

select distinct th.staff_id as 帐号, th.staff_name as 姓名,str_list('staff_id', th.staff_id, 'role_name', 'hqwtest') 账号角色 from hqtest th

hqtest是一个临时表

这样写出来确实是牵强了点，如果费用这样写的话还是可以的。
你可以创建临时表和遍历，再加上临时变量来实现。
当然不如在前台实现更好一点。

sql server中定义存储过程的时候是可以定义输入输出参数的，当然输出参数可以是多个的。
mysql也应该可以的。

关于sql server,请看这里[url]http://jnotnull.iteye.com/blog/253363[/url]

如果是在oralce下，可以考虑用自定义聚集函数实现
可以参照[url]http://www.iteye.com/topic/183084[/url]

如果是分别检索a,b的数据然后在java端实现字符串连接，似乎也不是那么麻烦
例程如下

[code="java"]
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.collections.MultiHashMap;

/**

• ResultSetTest *
• @version 1.0

• @since 2008/10/20
  */
  public class ResultSetTest
  {
  public static void main(String[] args)
  {
  List aList = Arrays.asList(
  new AEntity[]{
  new AEntity("1", "x", "aaa"),
  new AEntity("2", "y", "bbb"),
  }
  );

  List bList = Arrays.asList(
      new BEntity[]{
          new BEntity("1", "32"),
          new BEntity("1", "24"),
          new BEntity("1", "65"),
          new BEntity("2", "11"),
          new BEntity("2", "33"),
      }
  );
  
  merge2Result(aList, bList);
  
  for (int i = 0 ; i < aList.size() ; i++)
  {
      AEntity a = (AEntity)aList.get(i);
      System.out.println("id = " + a.getId() 
          + "\tname = " + a.getName()
          + "\twork = " + a.getWork()
          + "\tsource = " + a.getSource()
      );
  }
  

  }

  public static void merge2Result(List aList, List bList)
  {
  MultiHashMap sourceMap = new MultiHashMap();
  for (int i = 0 ; i < bList.size() ; i++)
  {
  BEntity b = (BEntity)bList.get(i);
  sourceMap.put(b.getId(), b.getSource());
  }

  for (int i = 0 ; i < aList.size() ; i++)
  {
      AEntity a = (AEntity)aList.get(i);
      Collection sources = sourceMap.getCollection(a.getId());
      if (sources == null || sources.size() == 0
      ){
          continue;
      }
      StringBuffer sb = new StringBuffer();
      for (Iterator iter = sources.iterator() ; iter.hasNext() ; )
      {
          sb.append((String)iter.next()).append(",");
      }
      a.setSource(sb.substring(0, sb.length() - 1));
  }
  

  }
  }

[/code]

[code="java"]
public class AEntity
{
public String id;
public String name;
public String work;
public String source;

public AEntity(String id, String name, String work)
{
    this.id   = id  ;
    this.name = name;
    this.work = work;
}

//...

}
[/code]
[code="java"]
public class BEntity
{
public String id;
public String source;

public BEntity(String id, String source)
{
    this.id     = id    ;
    this.source = source;
}

//...

}
[/code]