解决方案 »
- 访问oracle数据库是不是只能用ODBC
- SQL语句中日期问题
- 如何通过exp命令行导出另一台机器上的数据
- 我用了管道表函数,编译时ORACLE报00600错误
- 怎样拷贝Oracle的数据库文件?
- 同时安装oracle 8i & 10G客户端,.net应用程序报错 "ORA-12154:TNS无法解析指定的连接标识符"
- 请问有谁知道Oracle数据库的登录用户名和密码分别是什么啊?以后能否不用登录信息啊?
- ???求教:怎样从sqlServer2000中把数据库中的表倒入:Orcale数据库中???
- oracle for unix5.0.5监听程序无法启动,大家帮忙看看!
- help me 连接oracle不上
- []请教个Oracle ODAC,Client,VS2010的问题
- Oracle11g安装问题
将下面语句的执行结果当作查询语句拿去执行SELECT 'select A.ProductID,MAX(AGE) AS AGE,'||
WMSYS.WM_CONCAT('MAX(DECODE(B.FieldName,'''||NAME||''',C.Value)) AS '||NAME)
||' FROM Product A,ProductField B,Product_Field C
WHERE A.ProductID=C.ProductID
AND A.table_id =B.table_id
AND B.ProductFieldID=C.ProductFieldID
AND A.table_id='''||P_ID||'''
GROUP BY A.ProductID'
FROM ProductField
WHERE table_id=P_ID
SELECT 'select A.ProductID,MAX(AGE) AS AGE,'||
WMSYS.WM_CONCAT('MAX(DECODE(B.FieldName,'''||NAME||''',C.Value)) AS '||NAME)
||' FROM Product A,ProductField B,Product_Field C
WHERE A.ProductID=C.ProductID
AND A.table_id =B.table_id
AND B.ProductFieldID=C.ProductFieldID
AND A.table_id='''||'ps_layer'||'''
GROUP BY A.ProductID'
FROM ProductField
WHERE table_id='ps_layer';
报了NAME 标识符无效的错误,麻烦版主帮我看一下,还有就是这个P_ID我这样传对吗?
create table Product
(
ProductID number,
age number,
table_id varchar2(20)
)
create table ProductField
(
ProductFieldID number,
FieldName VARCHAR2(50),
FieldType VARCHAR2(20),
table_id varchar2(20)
)
create table Product_Field
(
ProductID number,
ProductFieldID VARCHAR2(50),
Val VARCHAR2(20)
)
insert into Product (ProductID, age,table_id) values (1,28, 'ps_layer');
insert into ProductField (ProductFieldID, FieldName,FieldType,table_id) values (1,'Name','varchar2','ps_layer');
insert into ProductField (ProductFieldID, FieldName,FieldType,table_id) values (2,'productType','varchar2','ps_layer');insert into Product_Field (ProductID , ProductFieldID,val) values (1,1, 'MP3');
insert into Product_Field (ProductID , ProductFieldID,val) values (1,2, '消费数码'); select * from Product;
select * from ProductField;
select * from Product_Field;
SELECT 'select A.ProductID,MAX(AGE) AS AGE,'||
WMSYS.WM_CONCAT('MAX(DECODE(B.FieldName,'''||FieldName||''',C.Value)) AS '||FieldName)
||' FROM Product A,ProductField B,Product_Field C
WHERE A.ProductID=C.ProductID
AND A.table_id =B.table_id
AND B.ProductFieldID=C.ProductFieldID
AND A.table_id='''||P_ID||'''
GROUP BY A.ProductID'
FROM ProductField
WHERE table_id=P_ID
方式一、
SELECT t1.productid,
t1.age,
MAX(decode(t2.productfieldid, 1, t3.val, NULL)) name,
MAX(decode(t2.productfieldid, 2, t3.val, NULL)) producttype
FROM product t1,
productfield t2,
product_field t3
WHERE t1.table_id = t2.table_id
AND t2.productfieldid = t3.productfieldid
GROUP BY t1.productid,
t1.age;
方式二、
SELECT t1.productid,
t1.age,
(SELECT (SELECT t3.val FROM product_field t3 WHERE t3.productfieldid = t2.productfieldid)
FROM productfield t2
WHERE t2.table_id = t1.table_id
AND t2.productfieldid = 1) NAME,
(SELECT (SELECT t3.val FROM product_field t3 WHERE t3.productfieldid = t2.productfieldid)
FROM productfield t2
WHERE t2.table_id = t1.table_id
AND t2.productfieldid = 2) producttype
FROM product t1;
方式三、
SELECT *
FROM (SELECT t1.productid,
t1.age,
t2.productfieldid,
t3.val
FROM product t1,
productfield t2,
product_field t3
WHERE t1.table_id = t2.table_id
AND t2.productfieldid = t3.productfieldid)
-----
pivot(MAX(val) --pivot_clause
FOR productfieldid --pivot_for_clause
IN(1 name, 2 producttype) --pivot_in_cluase
); 效果图:
我提供的方法是每次通过sql语句拼接动态查询语句,然后把拼接的结果拿去执行,这种方式更灵活一些
感谢版主的耐心回复,可以了,是我搞错了,应该是把执行的sql语句结果,在执行就达到效果了
再次非常感谢!