SELECT
C.CUST_ID,C.CUST_NAME,S2.PVI,S3.PUI_NBR
FROM CUST C,SERV_SERVICE S1,SERV_PVI S2,SERV_PUI S3
WHERE C.CUST_ID=S1.CUST_ID
AND S1.SERV_SERVICE_ID=S2.SERV_SERVICE_ID
AND S1.SERV_SERVICE_ID=S3.SERV_SERVICE_ID
AND S1.STATE='S11'
AND C.CUST_NAME LIKE '韩永佳%';
结果是这样我想让结果只显示成一行。把PUI_NBR分三列显示SQL怎么写?
我不想在查询结果里面拼装 因为模糊查询可能有几万条结果 在java中处理有可能太慢
FROM CUST C,SERV_SERVICE S1,SERV_PVI S2,SERV_PUI S3
WHERE C.CUST_ID=S1.CUST_ID
AND S1.SERV_SERVICE_ID=S2.SERV_SERVICE_ID
AND S1.SERV_SERVICE_ID=S3.SERV_SERVICE_ID
AND S1.STATE='S11' AND C.CUST_NAME LIKE '韩永佳%'
GROUP BY C.CUST_ID,C.CUST_NAME,S2.PVI;
大神 这个能不能把他PUI_NBRS分成三列呀,如果和一起我还要拆开
你写的语句可以运行
给你放个图
SELECT CUST_ID,CUST_NAME,PVI,
max(decode(r,1,PUI_NBR,null)) PUI_NBR1,
max(decode(r,2,PUI_NBR,null)) PUI_NBR2,
max(decode(r,3,PUI_NBR,null)) PUI_NBR3
from
(SELECT C.CUST_ID,C.CUST_NAME,S2.PVI,S3.PUI_NBR row_number() over(partition by C.CUST_ID,C.CUST_NAME,S2.PVI order by S3.PUI_NBR ) r FROM CUST C,SERV_SERVICE S1,SERV_PVI S2,SERV_PUI S3
WHERE C.CUST_ID=S1.CUST_ID
AND S1.SERV_SERVICE_ID=S2.SERV_SERVICE_ID
AND S1.SERV_SERVICE_ID=S3.SERV_SERVICE_ID
AND S1.STATE='S11' AND C.CUST_NAME LIKE '韩永佳%')
group by CUST_ID,CUST_NAME,PVI;
max(decode(r,1,PUI_NBR,null)) PUI_NBR1,
max(decode(r,2,PUI_NBR,null)) PUI_NBR2,
max(decode(r,3,PUI_NBR,null)) PUI_NBR3
from
(SELECT C.CUST_ID,C.CUST_NAME,S2.PVI,S3.PUI_NBR, row_number() over(partition by C.CUST_ID,C.CUST_NAME,S2.PVI order by S3.PUI_NBR ) r FROM CUST C,SERV_SERVICE S1,SERV_PVI S2,SERV_PUI S3
WHERE C.CUST_ID=S1.CUST_ID
AND S1.SERV_SERVICE_ID=S2.SERV_SERVICE_ID
AND S1.SERV_SERVICE_ID=S3.SERV_SERVICE_ID
AND S1.STATE='S11' AND C.CUST_NAME LIKE '韩永佳%')
group by CUST_ID,CUST_NAME,PVI;
max(decode(r,1,PUI_NBR,null)) PUI_NBR1,
max(decode(r,2,PUI_NBR,null)) PUI_NBR2,
max(decode(r,3,PUI_NBR,null)) PUI_NBR3
from
(SELECT C.CUST_ID,C.CUST_NAME,S2.PVI,S3.PUI_NBR,rownum r FROM CUST C,SERV_SERVICE S1,SERV_PVI S2,SERV_PUI S3
WHERE C.CUST_ID=S1.CUST_ID
AND S1.SERV_SERVICE_ID=S2.SERV_SERVICE_ID
AND S1.SERV_SERVICE_ID=S3.SERV_SERVICE_ID
AND S1.STATE='S11' AND C.CUST_NAME LIKE '韩永佳%')
group by CUST_ID,CUST_NAME,PVI;