select a.ID,a1.data,a2.data,a3.data,a4.data
from test a,
(select ID,data from test where Type_ID=1) a1,
(select ID,data from test where Type_ID=2) a2,
(select ID,data from test where Type_ID=3) a3,
(select ID,data from test where Type_ID=4) a4
where a.ID=a1.ID(+)
and a.ID=a2.ID(+)
and a.ID=a3.ID(+)
and a.ID=a4.ID(+)
from test a,
(select ID,data from test where Type_ID=1) a1,
(select ID,data from test where Type_ID=2) a2,
(select ID,data from test where Type_ID=3) a3,
(select ID,data from test where Type_ID=4) a4
where a.ID=a1.ID(+)
and a.ID=a2.ID(+)
and a.ID=a3.ID(+)
and a.ID=a4.ID(+)
from test a,
(select ID,data from test where Type_ID=1) a1,
(select ID,data from test where Type_ID=2) a2,
(select ID,data from test where Type_ID=3) a3,
(select ID,data from test where Type_ID=4) a4
where a.ID=a1.ID
and a.ID=a2.ID
and a.ID=a3.ID
and a.ID=a4.ID
order by a2.ID,a3.ID
select distinct a.ID ,a1.data,a2.data,a3.data,a4.data
from test a,
(select ID,data from test where Type_ID=1) a1,
(select ID,data from test where Type_ID=2) a2,
(select ID,data from test where Type_ID=3) a3,
(select ID,data from test where Type_ID=4) a4
where a.ID=a1.ID(+)
and a.ID=a2.ID(+)
and a.ID=a3.ID(+)
and a.ID=a4.ID(+)
order by a1.data,a2.data;
说说你的想法?
另外(+)是什么意思?to nyangpro(阿宁):
也说说你的想法?
sum(decode(type_id,2,data,0)) type_id=2,
sum(decode(type_id,3,data,0)) type_id=3,
sum(decode(type_id,4,data,0)) type_id=4
group by id
order by 3,4
sum(decode(type_id,2,data,0)) type_id2,
sum(decode(type_id,3,data,0)) type_id3,
sum(decode(type_id,4,data,0)) type_id4
from yourtable
group by id
order by 3,4
select * from
(select id, max(decode(type_id,1,data,'0')) type1,
max(decode(type_id,2,data,'0')) type2,
max(decode(type_id,3,data,'0')) type3,
max(decode(type_id,4,data,'0')) type4
from yourtable
group by id) a
order by a.type2,a.type3
http://tahiti.oracle.com/pls/tahiti/tahiti.docindex9i:
http://tahiti.oracle.com/pls/db901/db901.docindex?re=homepage#index-SQL
在上面的网址里没有找到(+)?
出来了!!!
能解释一下你的SQL语句?十分感谢
(+)在左面是右连接
(+)在右面是左连接
你看一下decode的语法就会明白了
不加distinct 可以得到数据,可是不是三条记录,而是12条
加上distinct 错误为:不是 SELECTed 表达式to zhenyukeji(何处是我家) :
加(+)和不加出来的结果一样?为什么加(+)?能解释一下吗?to cyberflying(雁南飞) :
四个都加max,是不是表示按照四个排序,而不是我说的两个?
如果只按照其中几个排序的话,怎么写?
如a1.data为NULL
不加(+)该记录不显示
加(+)该记录显示为:
ID Type_ID=1 Type_ID=2 Type_ID=3 Type_ID=4
1 杨 二 北京
select * from
(select id, max(decode(type_id,1,data,'0')) type1,
max(decode(type_id,2,data,'0')) type2,
max(decode(type_id,3,data,'0')) type3,
max(decode(type_id,4,data,'0')) type4
from yourtable
group by id) a
order by a.type2,a.type3这个是用decode()把每一行都转换成你所要求的格式(在相应的type下面放data的值,其他的补个'0'),然后按id分组,去最大的data,因为从你的data的值看来,都会比'0'大的,所以用max()。排序是order by a.type2,a.type3,和max()无关的
不过呢
令人伤心的是
它无法支持过滤
没有问题,是我看错了,不好意思:)