select decode(item_code,'BK4100','BKD100','BK4200','BKD200',ITEM_CODE) as item_code,tab_year as year ,tab_month as month,
(select COL1 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BKD100','BKD200')),
(select COL7 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BK4100','BK4200'))
as LJ from dxp_102544 t where t.item_code in ('BK4100','BK4200')
请问大大怎么改
(select COL1 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BKD100','BKD200')),
(select COL7 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BK4100','BK4200'))
as LJ from dxp_102544 t where t.item_code in ('BK4100','BK4200')
请问大大怎么改
(select COL7 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BK4100','BK4200')
检查一下,这两个语句有返回多行的情况
ORA-01427: 单行子查询返回多个行
差点理解成查询结果本是多行了
说明楼主写法有误:
(SELECT COL1 FROM DXP_102544 A WHERE A.TAB_YEAR=T.TAB_YEAR AND A.TAB_MONTH=T.TAB_MONTH AND A.ITEM_CODE IN ('BKD100','BKD200')),
(select COL7 from dxp_102544 a where a.tab_year=t.tab_year and a.tab_month=t.tab_month and a.item_code in ('BK4100','BK4200')) AS LJ 关联会出现一对多的情况,换成如下语句:
SELECT DECODE(T.ITEM_CODE,'BK4100','BKD100','BK4200','BKD200',T.ITEM_CODE) AS ITEM_CODE,
TAB_YEAR AS YEAR ,TAB_MONTH AS MONTH,
A.COL1,
b.COL1
FROM DXP_102544 T
LEFT JOIN DXP_102544 A
ON A.TAB_YEAR=T.TAB_YEAR AND A.TAB_MONTH=T.TAB_MONTH AND A.ITEM_CODE IN ('BKD100','BKD200')
LEFT JOIN DXP_102544 B
ON B.TAB_YEAR=T.TAB_YEAR AND B.TAB_MONTH=T.TAB_MONTH AND B.ITEM_CODE IN ('BK4100','BK4200')
WHERE T.ITEM_CODE='BK4200'
;