id ，
declarCycle 年度，
declarYear 报告年度
DeclarType 申报类型
dataStatus 完成状态 (1待提交、2待审核、3审核通过、4待修改)；

5个回答

select t2.declarCycle as 年度,
t2.declarYear as 报告年度,
t2.DeclarType as 申报类型,
count(1) as 应申报人数,
(select count(1)
from decla_regularInfo t1
where t1.declarCycle = t2.declarCycle
and t1.DeclarType = t2.DeclarType
and t1.declaryear = t2.declaryear
and t1.dataStatus = '1') as 审核通过,
(select count(1)
from decla_regularInfo t3
where t3.declarCycle = t2.declarCycle
and t3.DeclarType = t2.DeclarType
and t3.declaryear = t2.declaryear
and t3.dataStatus in ('2', '3', '4')) as 已申报人数
from decla_regularInfo t2
group by t2.declaryear, t2.declarCycle, t2.DeclarType
order by declarCycle, DeclarType;

qq_24044859 回复jackleeonlyone: 题主，我的回答错了吗？和我不是一样吗？你看一下回答的时间好不好啊，很心凉，怕写错，还建表测试。。。。

jackleeonlyone dataStatus =3 为审核通过，不过这个没关系，学到了，牛逼

select
id,
declarYear,
declarCycle,d
eclarType,
count(datastatus) as '应申报人数',
(select count(datastatus) from declar_regularInfo where datastatus in (2,3,4)) as '已申报人数'，
(select count(datastatus) from declar_regularInfo where datastatus = 3 ) as '审核通过'
from
declar_regularInfo
group by id,declarYear,declarCycle,declarType;

jackleeonlyone 这样统计数量并非是对的，select * from decla_regularInfo where declarYear =2018 and declarCycle =3 and declarType =1 and datastatus in (2,3,4) 就是只有一条，而这样写就是五条数据

jackleeonlyone 回复yangkkai_123: 这样写出来有点问题

qq_42178355 group by 的字段，必须是同前面查询的一样

yangkkai_123 回复yangkkai_123: count写到外面

yangkkai_123 写的不好见谅

，结构就是这样，应该不是正确的

weixin_378505179 嗯，这就是sql表,还要设置字符类型

select t2.declarCycle as 年度,
t2.declarYear as 报告年度,
t2.DeclarType as 申报类型,
count(1) as 应申报人数,
(select count(1)
from decla_regularInfo t1
where t1.declarCycle = t2.declarCycle
and t1.DeclarType = t2.DeclarType
and t1.declaryear = t2.declaryear
and t1.dataStatus = '1') as 审核通过,
(select count(1)
from decla_regularInfo t3
where t3.declarCycle = t2.declarCycle
and t3.DeclarType = t2.DeclarType
and t3.declaryear = t2.declaryear
and t3.dataStatus in ('2', '3', '4')) as 已申报人数
from decla_regularInfo t2
group by t2.declaryear, t2.declarCycle, t2.DeclarType
order by declarCycle, DeclarType;

select
id,
declarYear,
declarCycle,d
eclarType,
count(datastatus) as '应申报人数',
(select count(datastatus) from declar_regularInfo where datastatus in (2,3,4)) as '已申报人数'，
(select count(datastatus) from declar_regularInfo where datastatus = 3 ) as '审核通过'
from
declar_regularInfo
group by id,declarYear,declarCycle,declarType;

oracle SQL语句 急！！

oracle 请问一下，这一条拼接的sql怎么写成一条SQL(急！！!)

select * from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2 where t1.accbaseid = t2.accbaseid and t1.state = '02' and t2.state = '03' and t1.relievetime is not null and t1.punishtimelimit is null and t2.happenorgcode = '02' and t1.relievetime <= to_char(sysdate, 'yyyy-mm-dd') union all select * from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2 where t1.accbaseid = t2.accbaseid and t1.state = '02' and t2.state = '03' and t1.relievetime is null and t1.punishtimelimit is not null and t2.happenorgcode = '02' and to_char(add_months(sysdate, decode(t1.punishtimelimit, '半年', -6, '一年', -12, '二年', -24, 99)), 'yyyy-mm-dd') >= t1.punishtime union all select * from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2 where t1.accbaseid = t2.accbaseid and t1.state = '02' and t2.state = '03' and t1.relievetime is null and t1.punishtimelimit is null and t1.insidepunishtype = '01' and t2.happenorgcode ='02' and to_char(add_months(sysdate, case when t1.punishway = '01' then -6 when t1.punishway in ('02', '03', '04') then -12 when t1.punishway = '05' then -24 when t1.punishway = '06' then -24 else 99 end), 'yyyy-mm-dd') >= t1.punishtime 各位大神，请问这一条拼接sql咋样才能写成一条SQL,求指导

java 执行 sql 文件报错 求大神帮忙解决 急急急！！！！

sql文件里边的内容 /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50096 Source Host : localhost:3306 Source Database : cemscenter1 Target Server Type : MYSQL Target Server Version : 50099 File Encoding : 65001 Date: 2015-04-22 10:09:37 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Procedure structure for QueryLimberWaterHourData -- ---------------------------- DROP PROCEDURE IF EXISTS `QueryLimberWaterHourData`; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryLimberWaterHourData`() BEGIN select * from temp_waterhourdata; END ;; DELIMITER ; 这个sql文件在Navicat里边执行可以成功 在java程序里用 SQLExec 执行就报错 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1 java 代码 SQLExec sqlExec = new SQLExec(); sqlExec.setDriver("com.mysql.jdbc.Driver"); sqlExec.setUrl("jdbc:mysql://localhost:3306/test"); sqlExec.setUserid("root"); sqlExec.setPassword("ssdlh"); sqlExec.setSrc(new File("src/process"+name+".sql")); sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance( SQLExec.OnError.class, "abort"))); sqlExec.setPrint(true); sqlExec.setOutput(new File("src/sql.out")); sqlExec.setProject(new Project()); try{ sqlExec.execute();

oracle和sql server的数据比较

oracle和sql server分别有同样的一堆表，来源一样，但是由于导入导出的时候可能会出错，想设计一个程序比较两个数据库中的那些表的大小，就是想比较两个数据库中具有相同表名的表的行数是否相等，不相等的最好能输出是在oracle的数据量比较多还是sqlserver里面的数据量比较多，并输出成一个文档，最好是excel或者是txt，急，求大神！！！额，不限工具，不过最好用R语言、python、C语言、MATLAB等以便我装逼的语言,万分感谢！！！！

sql developer 无法远程连接 oracle

sql报错,求大神帮忙看看，在线等！

Caused by: java.sql.SQLException: sql injection violation, syntax error: TODO : LITERAL_INT top : select top 20 * from (select row_number() over ( order by name asc ) row_number, * from (select id,name,display_Name,type,instance_Url,state, content, version,create_Time,creator from wf_process where 1=1 and state = ? ) aa ) a where row_number > 0 order by row_number at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:711) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:240) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:928) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:928) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318) at org.springframework.jdbc.core.JdbcTemplate\$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1436) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581) ... 126 more Caused by: com.alibaba.druid.sql.parser.ParserException: TODO : LITERAL_INT top at com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser.parseStatementList(OracleStatementParser.java:490) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:112) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:619) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:574) at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:699) ... 138 more

A表有字段Id， Name，age ；B表有a_id,money,yue; A表有数据B表为新建的，现在要给A表的每一条数据在B表中建一条数据，A表的Id=B表的a_id。我用的数据库是MySQL。

oracle sql优化的问题 各位大神有什么办法优化一下吗

SELECT a.JIAODU_X AS "xPoint", a.CAIJITIME AS "yPoint", a.SENSORNO AS "sensorno", a.SENSEID AS "sensorid" FROM WM_DRAWPONIT_DB a where a.CAIJITIME > =to_date( '2017-05-28 14:50:16','yyyy-mm-dd hh24:mi:ss') and a.CAIJITIME < =to_date('2018-01-22 14:06:08','yyyy-mm-dd hh24:mi:ss') and a.SENSEID ='a9b78b7a524843be87b254303b3c8bb1' order by a.CAIJITIME 加上时间排序后非常的慢 要20多秒 加上时间排序 ![图片说明](https://img-ask.csdn.net/upload/201801/22/1516610638_676020.jpg) 去掉时间排序 ![图片说明](https://img-ask.csdn.net/upload/201801/22/1516610657_962812.jpg) 各位大神有什么办法优化吗 小弟对sql不太懂

oracle session 的 sqlID 为空

sqldeveloper连接远程oracle

**sqldeveloper连接远程oracle,用户名、密码、ip、端口、SID都是正确的，本机telnet此ip端口也能通，但是用sqldeveloper连接就报IO异常，使用别的电脑一样用sqldeveloper却能连接上，求各位神指点！！**

sql server语句如何转化为oracle语句

sql server语句如何转化为oracle语句: --sqlserver declare @c int select @c=count(*) from Employee select @c,id,name from Employee --如何转为oracle sql

sqlserver的sql语句转成oracle

SELECT FF.*, AF.FUNCGROUPNAME FROM (SELECT F.FUNCCODE,F.FUNCNAME,F.ISMENU,F.FUNCGROUPID, (CASE WHEN PARTY_ID IS NULL THEN 0 ELSE 1 END) ISCHECK FROM APP_FUNCTION F LEFT JOIN CAP_RESAUTH CR ON F.FUNCCODE = CR.RES_ID AND CR.PARTY_ID = #role:int#) FF LEFT JOIN APP_FUNCGROUP AF ON FF.FUNCGROUPID = AF.FUNCGROUPID ORDER BY FUNCGROUPID 这是一条sqlserver的语句，转成oracle的语句应该怎么转？主要是CASE函数那~~~求帮忙

sql server语句转化成oracle

alter proc skyGetBizKey V_AddCount int, V_KeyName Varchar2(128), V_Retval int Output As begin try begin tran If Exists(Select IndexValue From skyBizkeys Where BizForm = v_KeyName) Begin Set v_Retval = (Select top 1 ISNULL(IndexValue, 0) + 1 From skyBizkeys Where BizForm = @KeyName) if upper(@KeyName) = '\$TEMPINSTANCE' and exists(select 1 from skyBizkeys Where BizForm = '\$INSTANCE' and IndexValue > @Retval) begin set @Retval = (select top 1 ISNULL(IndexValue, 0) + 1 from skyBizkeys Where BizForm = '\$INSTANCE') end; Update skyBizkeys Set IndexValue = @Retval+@AddCount-1 Where BizForm = @KeyName end; Else Begin Insert Into skyBizkeys(BizForm, IndexValue) Values (@KeyName, @AddCount+1) Set @Retval = 1 end;

ZT KSSJ JSSJ MBID ZF 2015-12-11 2016-12-11 1 ZF 2015-12-18 2016-12-18 1 ZF 2015-12-18 2016-12-18 1 ZF 2015-12-19 2016-12-19 1 TF 2016-01-14 1 ZF 2016-02-19 2017-02-19 1 ZF 2016-02-29 2016-04-30 1 TF 2016-03-21 1 ZF 2015-12-11 2016-12-11 3 ZF 2015-12-18 2016-12-18 3 ZF 2015-12-18 2016-12-18 3 ZF 2015-12-19 2016-12-19 3 TF 2016-01-14 3 ZF 2016-02-19 2017-02-19 3 ZF 2016-02-19 2017-02-19 3 ZF 2016-01-14 2016-03-14 4 ZF 2016-02-04 2017-02-04 4 ZF 2016-02-19 2017-02-19 4 TF 2016-02-29 4 ZF 2016-02-19 2017-02-19 61 上面为原始数据。最终想要的结果为： KSSJ JSSJ MBID 2015-12-11 2016-01-14 1 2016-02-19 2016-03-21 1 2015-12-11 2016-01-14 3 2016-02-19 2017-02-19 3 2015-01-14 2016-02-29 4 2016-02-19 2017-02-19 61

sql 语句中字符类型的转换问题，在线等！！！急！！！

select 'http//'+ID+'.html' as LINK from A ID的字符类型是bigint的，出现错误提示：“从数据类型 varchar 转换为 bigint 时出错。” 求解决语法！！！

Oracle sqldeveloper打开问题

ArrayList源码分析(入门篇)

ArrayList源码分析 前言： 写这篇博客的主要原因是，在我上一次参加千牵科技Java实习生面试时，有被面试官问到ArrayList为什么查找的速度较快，插入和删除的速度较慢？当时我回答得不好，很大的一部分原因是因为我没有阅读过ArrayList源码，虽然最后收到Offer了，但我拒绝了，打算寒假学得再深入些再广泛些，下学期开学后再去投递其他更好的公司。为了更加深入理解ArrayList，也为

String s = new String(" a ") 到底产生几个对象？

loonggg读完需要3分钟速读仅需 1 分钟大家好，我是你们的校长。我之前讲过，这年头，只要肯动脑，肯行动，程序员凭借自己的技术，赚钱的方式还是有很多种的。仅仅靠在公司出卖自己的劳动时...

MySQL数据库面试题（2020最新版）

HashMap底层实现原理，红黑树，B+树，B树的结构原理 Spring的AOP和IOC是什么？它们常见的使用场景有哪些？Spring事务，事务的属性，传播行为，数据库隔离级别 Spring和SpringMVC，MyBatis以及SpringBoot的注解分别有哪些？SpringMVC的工作原理，SpringBoot框架的优点，MyBatis框架的优点 SpringCould组件有哪些，他们...

《经典算法案例》01-08：如何使用质数设计扫雷（Minesweeper）游戏