Orcale数据库心得

目录(?)[+]

遇到的问题

• Java 连接 Orcale数据库的配置文件

dbyc.driver=oracle.jdbc.driver.OracleDriver     //加载orcale驱动    
dbyc.url=jdbc:oracle:thin:@192.168.3.220:1521:orcl  //orcale数据库url
dbyc.username=sys as sysdba
dbyc.password=123456
   
   
1
2
3
4

• Maven添加Orcale数据库的jar包

<dependency>  
    <groupId>com.oracle</groupId>  
    <artifactId>ojdbc14</artifactId>  
    <version>10.2.0.1.0</version>  
</dependency> 
   
   
1
2
3
4
5

• Group by

SELECT
    d.pointvalue currentGasVolum,
    d.pointtime recvTime,
    d.meterCode
FROM
    (
        SELECT
            z.pointvalue,
            z.pointtime,
            w.id,
            CONCAT(w.gjbh,w.bh) meterCode
        FROM
            zdscadadata z
        LEFT JOIN
            b_watchvariableinfo w ON z.pointname = w.variable
        WHERE
            CONCAT(w.gjbh,w.bh) in
            <foreach collection="meterCodeList" open="(" close=")" separator="," item="item">
                #{item.meterCode}
            </foreach>
        ORDER BY
            z.pointtime DESC
    )   d
GROUP BY
    d.id
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

这句sql大概的意思就是根据pointtime 字段排序，然后使用group by 取出最大值，在mysql环境下是完全可以正常使用的，但是在orcale环境下就报错了，错误是：ORA-00979 不是 GROUP BY 表达式，但是orcale又的确有group by 这个方法，经过查阅资料，结果发现，orcale中的group by 后面的字段必须包括所有你select出来的，代码如下：

SELECT
    d.pointvalue currentGasVolum,
    d.pointtime recvTime,
    d.meterCode,
    d.id
FROM
    (
        SELECT
            z.pointvalue,
            z.pointtime,
            w.id,
            CONCAT(w.gjbh,w.bh) meterCode
        FROM
            zdscadadata z
        LEFT JOIN
            b_watchvariableinfo w ON z.pointname = w.variable
        WHERE
            CONCAT(w.gjbh,w.bh) in
            <foreach collection="meterCodeList" open="(" close=")" separator="," item="item">
                #{item.meterCode}
            </foreach>
        ORDER BY
            z.pointtime DESC
    )   d
GROUP BY
    d.id,d.pointvalue,d.pointtime,d.meterCode  <!-- 加上所有的字段 -->

   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

不理解为什么要加上无关的字段，但是它就是这么规定的，组长的意思是使用group by 相当于没有任何意义了，因此让我换一种写法，使用max()函数试一下，于是代码变成了这样：

• MAX()函数

SELECT
    MAX(z.pointtime) recvTime,
    z.pointvalue currentGasVolum,
    CONCAT(w.gjbh,w.bh) meterCode
FROM
    zdscadadata z
LEFT JOIN
    b_watchvariableinfo w ON z.pointname = w.variable
WHERE
    CONCAT(w.gjbh,w.bh) = #{meterCode}  AND w.variable = 'VGR';
   
   
1
2
3
4
5
6
7
8
9
10

执行后，发现也报错了：ORA-00937: not a single-group group function，没有深究这是什么错，尝试了一下只查询MAX(z.pointtime) recvTime，删除z.pointvalue currentGasVolum和CONCAT(w.gjbh,w.bh) meterCode，发现是可以的，也就是说，orcale的max()函数只支持查询一个字段？于是，我们又换了一种方法，继续使用order by ，然后取第一条数据，代码是这样的：

• Rownum

SELECT 
    * 
FROM
    (
        SELECT
            z.pointvalue currentGasVolum,
            z.pointtime recvTime,
            CONCAT(w.gjbh,w.bh) meterCode
        FROM
            zdscadadata z
        LEFT JOIN
            b_watchvariableinfo w ON z.pointname = w.variable
        WHERE
            CONCAT(w.gjbh,w.bh) = #{meterCode}  AND w.variable = 'VGR'
        ORDER BY
            z.POINTTIME DESC
    ) d
WHERE
    ROWNUM = 1
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• Orcale获取当前日期：

select to_char(sysdate,’YYYY-mm-dd hh24:mi:ss’) from dual;

• Orcale获取前一天的日期：

select to_char(sysdate-1,’YYYY-mm-dd hh24:mi:ss’) from dual;

• Orcale查询在昨天跟今天之间的数据：

SELECT * FROM SCADADTA WHERE POINTTIME BETWEEN sysdate-1 AND sysdate ;