SELECT A.BB_ID,A.BB_NAME,A.BB_CHINANAME,A.BU_ID AA,C.ID,C.BB_NAME,C.C_DATE,
(SELECT BITNO_VALUE FROM COM_MXZ WHERE TO_CHAR(C_DATE,'hh24:mi') >= '00:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '01:00' AND BB_NAME = A.BB_NAME) 零点,
(SELECT BITNO_VALUE FROM COM_MXZ WHERE TO_CHAR(C_DATE,'hh24:mi') >= '13:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '14:00' AND BB_NAME = A.BB_NAME) 一点
FROM BASE_BITNO A LEFT JOIN COM_MXZ C ON (C.BB_NAME = A.BB_NAME)
WHERE A.BB_NAME=C.BB_NAME
零点和一点如果是一条记录的话就可以返回,但是如果是多条记录的话就提示ORA-01427: 单行子查询返回多个行这个错误
我找了好半天,不知道为什么,有高手解决一下么?
(SELECT BITNO_VALUE FROM COM_MXZ WHERE TO_CHAR(C_DATE,'hh24:mi') >= '00:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '01:00' AND BB_NAME = A.BB_NAME) 零点,
(SELECT BITNO_VALUE FROM COM_MXZ WHERE TO_CHAR(C_DATE,'hh24:mi') >= '13:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '14:00' AND BB_NAME = A.BB_NAME) 一点
FROM BASE_BITNO A LEFT JOIN COM_MXZ C ON (C.BB_NAME = A.BB_NAME)
WHERE A.BB_NAME=C.BB_NAME
零点和一点如果是一条记录的话就可以返回,但是如果是多条记录的话就提示ORA-01427: 单行子查询返回多个行这个错误
我找了好半天,不知道为什么,有高手解决一下么?
解决方案 »
- oracle开始-程序-的enterprises manager console图表不见了
- oracle的dmp文件可以导入到sql server 2000里吗?
- 硬盘写入后如何恢复彻底删除文件
- oracle入门问题
- vista下装不上oracle 9i?
- oratab是不是只有Linux上才有
- 用variable 来声明的变量怎么赋值呢?
- 初学者提问了(高手得分吧)
- 简单update语句问题(vs.net + oracle)
- $ORACLE_HOME/bin/exp, $ORACLE_HOME/bin/imp, $ORACLE_HOME/bin/svrmgrl
- MonetDB内存数据库什么创建函数和导入导出数据
- 有会用oracle自定义函数的朋友吗?
SELECT A.BB_ID,A.BB_NAME,A.BB_CHINANAME,A.BU_ID AA,C.ID,C.BB_NAME,C.C_DATE,
(case when TO_CHAR(C_DATE,'hh24:mi') >= '00:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '01:00'
then BITNO_VALUE else null end) 零点,
(case when TO_CHAR(C_DATE,'hh24:mi') >= '13:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '14:00'
then BITNO_VALUE else null end) 一点
FROM BASE_BITNO A LEFT JOIN COM_MXZ C ON (C.BB_NAME = A.BB_NAME)
试一下这样能否达到效果
SELECT A.BB_ID,
A.BB_NAME,
A.BB_CHINANAME,
A.BU_ID AA,
C.ID,
C.BB_NAME,
C.C_DATE,
(CASE WHEN TO_CHAR(C_DATE, 'hh24:mi') >= '00:00' AND
TO_CHAR(C_DATE, 'hh24:mi') <= '01:00' THEN
BITNO_VALUE ELSE NULL
END)
零点,
(CASE WHEN
TO_CHAR(C_DATE, 'hh24:mi') >= '13:00' AND
TO_CHAR(C_DATE, 'hh24:mi') <= '14:00' THEN
BITNO_VALUE ELSE NULL END) 一点
FROM BASE_BITNO A
LEFT JOIN COM_MXZ C
ON (C.BB_NAME = A.BB_NAME)
WHERE A.BB_NAME = C.BB_NAME
SELECT A.BB_ID,A.BB_NAME,A.BB_CHINANAME,A.BU_ID AA,C.ID,C.BB_NAME,C.C_DATE,
(case
when TO_CHAR(c.C_DATE,'hh24:mi') >= '00:00' AND TO_CHAR(c.C_DATE,'hh24:mi') <= '01:00' then c.BITNO_VALUE else null end ) 零点,
(case
when TO_CHAR(c.C_DATE,'hh24:mi') >= '13:00' AND TO_CHAR(c.C_DATE,'hh24:mi') <= '14:00' then c.BITNO_VALUE else null end ) 一点
FROM BASE_BITNO A LEFT JOIN COM_MXZ C ON (C.BB_NAME = A.BB_NAME)
WHERE A.BB_NAME=C.BB_NAME
SELECT A.BB_ID,A.BB_NAME,A.BB_CHINANAME,A.BU_ID AA,C.ID,C.BB_NAME,C.C_DATE,
(case when TO_CHAR(C_DATE,'hh24:mi') >= '00:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '01:00'
then BITNO_VALUE else null end) 零点,
(case when TO_CHAR(C_DATE,'hh24:mi') >= '13:00' AND TO_CHAR(C_DATE,'hh24:mi') <= '14:00'
then BITNO_VALUE else null end) 一点
FROM BASE_BITNO A LEFT JOIN COM_MXZ C ON (C.BB_NAME = A.BB_NAME)
WHERE A.BB_NAME=C.BB_NAME