问题如题,
这是我的表结构:
CREATE TABLE BOOK
(
ID NUMBER,
STUNAME VARCHAR2(8),
BOOKNAME VARCHAR(20)
);
SQL 语句如下:select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME))) as BOOK1
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME))) as BOOK2
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME))) as BOOK3
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME))) as BOOK4
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME)))
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME)))
from BOOK
group by STUNAME;
这是我的表结构:
CREATE TABLE BOOK
(
ID NUMBER,
STUNAME VARCHAR2(8),
BOOKNAME VARCHAR(20)
);
SQL 语句如下:select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME))) as BOOK1
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME))) as BOOK2
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME))) as BOOK3
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME))) as BOOK4
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME)))
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME,'BOOK1',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK2',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK3',COUNT(BOOKNAME)))
,max(decode(BOOKNAME,'BOOK4',COUNT(BOOKNAME)))
from BOOK
group by STUNAME;
,sum(decode(BOOKNAME,'BOOK2',1,0)) as BOOK2
,sum(decode(BOOKNAME,'BOOK3',1,0)) as BOOK3
,sum(decode(BOOKNAME,'BOOK4',1,0)) as BOOK4
from BOOK
group by STUNAME
select STUNAME,'BOOK1' as BOOK,Count(*) as BookNum
from BOOK Where BOOKNAME='BOOK1'
group by STUNAME
Union all
select STUNAME,'BOOK2' as BOOK,Count(*) as BookNum
from BOOK Where BOOKNAME='BOOK2'
group by STUNAME
Union all
select STUNAME,'BOOK3' as BOOK,Count(*) as BookNum
from BOOK Where BOOKNAME='BOOK3'
group by STUNAME
Union all
select STUNAME,'BOOK4' as BOOK,Count(*) as BookNum
from BOOK Where BOOKNAME='BOOK4'
group by STUNAME
Order by BOOK,STUNAME
1 1 张三 C程序设计
2 2 张三 JAVA设计模式
3 3 张三 ASP.NET入门经典
4 4 李司 C#入门经典
5 5 李司 JAVA设计模式
6 6 李司 C程序设计
7 7 望物 C程序设计
8 8 望物 C#入门经典
把上面这个表转换成
STUNAME BOOKNAME1 BOOKNAME1 BOOKNAME1
张三 C程序设计 JAVA设计模式 ASP.NET入门经典
李司 C#入门经典 JAVA设计模式 C程序设计
望物 C程序设计 C#入门经典 0如果某个人在某一列上没有书,则为0;
v_out varchar2(500):='';
cursor c is select a.BOOKNAME from BOOK a where a.STUNAME=STUNAMES;
begin
for v_temp in c loop
v_out:=v_out||' '||v_temp.BOOKNAME;
end loop;
return v_out;
exception
when others then
return 'An error occured';
end ;
/
select distinct STUNAME,test_fun(STUNAME) BOOK from BOOK
转换后的表中的那些BOOK1、BOOK2……是根据原来的表以名称分组后max(BOOKNAME)的大小,max(BOOKNAME)有多大就有多少个Book1、BOOK2……这样的列,而你现在是把一个人所有的书都放在同一列里了。