tangkuo
- 浏览: 94718 次
- 性别:
- 来自: 深圳
-
文章分类
- 全部博客 (209)
- http://docs.jpush.io/server/java_sdk/ (1)
- SpingMVC ModelAndView (1)
- Model (1)
- Control以及参数传递 (1)
- https://www.alipay.com/ (1)
- 检查指定的字符串列表是否不为空。 (1)
- 转自http://my.oschina.net/rpgmakervx/blog/505434 (1)
- 压缩js (1)
- 含包含 字母数字校验 (1)
- 判断浏览器是ie (1)
- fixbox (0)
- 转自http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html (1)
- http://dl2.iteye.com/upload/attachment/0113/2406/afbd8d53-dcad-3afc-8d78-41c1591 (0)
- IDEA (0)
- maven搭建 (0)
- http://www.jeasyuicn.com/api/docTtml/index.htm (1)
- 给tomcat添加运行内存： (1)
- JPUSH推送 (1)
- requestScope (0)
- oracle中plsql安装client10连接数据库 (1)
- 不需要安装oracle11g (1)
- tuikuan (1)
- http://www.hjxmall.com/index.php (1)
- java (1)
- 参考 (1)
- xml签名验签模拟程序 (1)
- 技术网站收集分享 (1)
- Java NIO提供了与标准IO不同的IO工作方式 ------来源于 http://www.iteye.com/magazines/132-Java-NIO (1)
- oracle表查询语句优化 (0)
- oracle (5)
- a (1)
- TenpayHttpClient (2)
- mongodb (1)
- http://www.qcloud.com/product/cmongo.html?utm_source=pcsem1&utm_medium=bdgj46&utm_campaign=baidu (1)
- SpringMVC整合MongoDB开发 (0)
- SpringMVC整合MongoDB开发 https://www.mongodb.org (1)
- Java 语言中常见问题总结 (1)
- 数据库SQL优化大总结 (1)
- 拦截器与过滤器的区别 (1)
- Struts2中拦截器与过滤器的区别及执行顺序 (1)
- Spring声明式事务管理与配置 (0)
- oracle OPERTION (1)
- java 高并发多线程开发 (1)
- Collection 与 map 接口相关集合 (1)
- 多线程开发实践 (1)
- JVM调优总结 转自 http://www.importnew.com/18694.html (1)
- redis 五种数据类型的使用场景 转自于 http://blog.csdn.net/gaogaoshan/article/details/41039581 (1)
- HttpWatch http基础 来自于http://blog.csdn.net/gaogaoshan/article/details/21237555 (1)
- maven 远程仓库 http://blog.csdn.net/gaogaoshan/article/details/40266779 (1)
- 生成Webservice客户端的4中方法 http://blog.csdn.net/gaogaoshan/article/details/8980775 (1)
- http://fgh2011.iteye.com/blog/1564283 (1)
- sleep和wait有什么区别 http://xiaowei2002.iteye.com/blog/2174188 (1)
- JDK中常用包及其类 常见的几种RuntimeException (1)
- Java的运行原理 (1)
- mybatis缓存的使用及理解 http://my.oschina.net/dxqr/blog/123163 (1)
- 软件架构设计分析 (1)
- redis技术总结 (3)
- java面试总结知识点 (1)
- ZooKeeper技术 (1)
- Hadoop (1)
- sso单点登录 (1)
- SpringIOC (1)
- 书签ssssssssssssssssssssss (1)
- spring事务的隔离级别 http://www.cnblogs.com/yangy608/archive/2011/06/29/2093478.html (1)
- 秒杀系统架构分析与实战 http://www.importnew.com/18920.html (1)
- oracle 连接plsql配置 (1)
- maven工程集成springmvc http://blog.csdn.net/fox_lht/article/details/16952683 (1)
- java类序列化与反序列化版本唯一号serialVersionUID (1)
- spring注解用法总结 (1)
- eclipse导入maven项目找不到资源文件方法 (1)
- dubbo (0)
- socket 网络编程 服务器与客户端 编程 (1)
- Thread与Runnable实现线程利用线程插队实现求和操作 (1)
- 生产者与消费者模式 (1)
- JAXB注解 java 关于xml的注解，自动生成xml文件 - @XML*** (1)
- xml 与JAVAbean转换 (1)
- MAP (2)
- decimalToString (1)
- 反编译插件 (0)
- 反编译插件 https://sourceforge.net/projects/jadclipse/?source=typ_redirect (1)
- AWX (1)
- 官网地址Dinp (1)
- public interface ExecutorService extends Executor (1)
- MAIN (1)
- 转自于 http://blog.csdn.net/lufeng20/article/details/24314381 (1)
- JaxbUtil (1)
- HttpXmlClient (0)
- Http post 请求 (1)
- URLDecoder (1)
- spdb (0)
- 订单号生成规则 (1)
- 距离0点毫秒数 (1)
- MyBatis存储过程调用java (1)
- Banks (1)
- 学习网址 (1)
- hots配置oracle数据库配置修改 (1)
- 支付宝文档 (1)
- Jadclipse (1)
- filter (0)
- Filter过滤器 (1)
- 工具类 fixbox (1)
- java quartz (0)
- java quartz 实现定时任务，每个礼拜一到礼拜五定时执行 (1)
- Thread (4)
- getResourceAsStream (1)
- BigData (1)
- 开源架构 (17)
- 架构 (0)
- 文件操作 (2)
- tools (20)
- 测试工具 (1)
- vm (1)
- mq (1)
- blog (1)
- 开源架构搭建技术 (6)
- JAVA细节技术点总结 (1)
- 优化技术 (1)
- LINUX命令使用 (1)
- spring (2)
- 大数据 (1)
- struts2 (1)
- python (1)
- pay (1)
- linux (1)
- 压力测试 (1)
- 代码检查插件 (1)
- 游戏端开发 (1)
- 微信开发相关 (1)
- 搜索引擎 (1)
- 网络技术 (1)
- 分布式部署 (1)
- 堆栈信息 (1)
最新评论
CASE WHEN THEN END
SELECT ID,
ORDER_ID,
ACCOUNT_NO,
CREATE_TIME,
DR_CR_FLAG,
BUSINESS_TYPE,
PAYMENT_ORDER_NO,
DR_CR_AMOUNT,
CR_AMOUNT,
PRE_CR_AMOUNT,
ACCOUNT_AMOUNT,
EXPENSE_FLAG
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER_ID, BUSINESS_TYPE, EXPENSE_FLAG ORDER BY VOUCHER_NO desc) RN,
T.ID,
T.ORDER_ID,
T.ACCOUNT_NO,
T.CREATE_TIME,
T.DR_CR_FLAG,
T.BUSINESS_TYPE,
T.PAYMENT_ORDER_NO,
T.DR_CR_AMOUNT,
T.CR_AMOUNT,
T.PRE_CR_AMOUNT,
T.ACCOUNT_AMOUNT,
T.EXPENSE_FLAG
FROM T_ACCOUNT_WATER T
WHERE T.ACCOUNT_NO = 1019659769267895
AND T.BUSINESS_TYPE != 'REFUND'
AND T.CREATE_TIME >= TO_DATE('2016-01-15','yyyy-mm-dd')
AND T.CREATE_TIME < TO_DATE('2016-01-15','yyyy-mm-dd') +1)
WHERE RN = 1
AND CASE
WHEN BUSINESS_TYPE = 'NBNKTRANS' THEN
DR_CR_FLAG
ELSE
'CR'
END = 'CR'
UNION ALL
SELECT ID,
ORDER_ID,
ACCOUNT_NO,
CREATE_TIME,
DR_CR_FLAG,
BUSINESS_TYPE,
PAYMENT_ORDER_NO,
DR_CR_AMOUNT,
CR_AMOUNT,
PRE_CR_AMOUNT,
ACCOUNT_AMOUNT,
EXPENSE_FLAG
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER_ID, BUSINESS_TYPE, EXPENSE_FLAG ORDER BY VOUCHER_NO desc) RN,
T.ID,
T.ORDER_ID,
T.ACCOUNT_NO,
T.CREATE_TIME,
T.DR_CR_FLAG,
T.BUSINESS_TYPE,
T.PAYMENT_ORDER_NO,
T.DR_CR_AMOUNT,
T.CR_AMOUNT,
T.PRE_CR_AMOUNT,
T.ACCOUNT_AMOUNT,
T.EXPENSE_FLAG
FROM T_ACCOUNT_WATER T
WHERE T.ACCOUNT_NO = 1019659769267895
AND T.BUSINESS_TYPE = 'REFUND'
AND T.CREATE_TIME >= TO_DATE('2016-01-15','yyyy-mm-dd')
AND T.CREATE_TIME < TO_DATE('2016-01-15','yyyy-mm-dd') +1);
ORDER_ID,
ACCOUNT_NO,
CREATE_TIME,
DR_CR_FLAG,
BUSINESS_TYPE,
PAYMENT_ORDER_NO,
DR_CR_AMOUNT,
CR_AMOUNT,
PRE_CR_AMOUNT,
ACCOUNT_AMOUNT,
EXPENSE_FLAG
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER_ID, BUSINESS_TYPE, EXPENSE_FLAG ORDER BY VOUCHER_NO desc) RN,
T.ID,
T.ORDER_ID,
T.ACCOUNT_NO,
T.CREATE_TIME,
T.DR_CR_FLAG,
T.BUSINESS_TYPE,
T.PAYMENT_ORDER_NO,
T.DR_CR_AMOUNT,
T.CR_AMOUNT,
T.PRE_CR_AMOUNT,
T.ACCOUNT_AMOUNT,
T.EXPENSE_FLAG
FROM T_ACCOUNT_WATER T
WHERE T.ACCOUNT_NO = 1019659769267895
AND T.BUSINESS_TYPE != 'REFUND'
AND T.CREATE_TIME >= TO_DATE('2016-01-15','yyyy-mm-dd')
AND T.CREATE_TIME < TO_DATE('2016-01-15','yyyy-mm-dd') +1)
WHERE RN = 1
AND CASE
WHEN BUSINESS_TYPE = 'NBNKTRANS' THEN
DR_CR_FLAG
ELSE
'CR'
END = 'CR'
UNION ALL
SELECT ID,
ORDER_ID,
ACCOUNT_NO,
CREATE_TIME,
DR_CR_FLAG,
BUSINESS_TYPE,
PAYMENT_ORDER_NO,
DR_CR_AMOUNT,
CR_AMOUNT,
PRE_CR_AMOUNT,
ACCOUNT_AMOUNT,
EXPENSE_FLAG
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORDER_ID, BUSINESS_TYPE, EXPENSE_FLAG ORDER BY VOUCHER_NO desc) RN,
T.ID,
T.ORDER_ID,
T.ACCOUNT_NO,
T.CREATE_TIME,
T.DR_CR_FLAG,
T.BUSINESS_TYPE,
T.PAYMENT_ORDER_NO,
T.DR_CR_AMOUNT,
T.CR_AMOUNT,
T.PRE_CR_AMOUNT,
T.ACCOUNT_AMOUNT,
T.EXPENSE_FLAG
FROM T_ACCOUNT_WATER T
WHERE T.ACCOUNT_NO = 1019659769267895
AND T.BUSINESS_TYPE = 'REFUND'
AND T.CREATE_TIME >= TO_DATE('2016-01-15','yyyy-mm-dd')
AND T.CREATE_TIME < TO_DATE('2016-01-15','yyyy-mm-dd') +1);
分享到：
发表评论
相关推荐
sql数据库 转 Access SQL语句改写之 Case When Then When Then Else End!值得下载看看!资源免费,大家分享!! 更多免费资源 http://ynsky.download.csdn.net/
1 case when 写法 2 sum case when 用法 3 select 拼接字段 示例如下： when 2 then 'C' else 'D' end ) as '类型',count(*) as '数量' from table group by orderType
主要介绍了sql学习之CASE WHEN THEN ELSE END的用法,需要的朋友可以参考下
1. CASE WHEN 表达式有两种形式 代码如下:–简单Case函数 CASE sex WHEN ‘1’ THEN ‘男’ WHEN ‘2’ THEN ‘女’ ELSE ‘其他’ END –Case搜索函数 CASEWHEN sex = ‘1’ THEN ‘男’ WHEN sex = ‘2’ ...
我们都知道SQL中适用case when then来转化数据库中的信息 比如 select (case sex when 0 then '男' else '女' end) AS sex from studentInfo
substring(),charindex(),PATINDEX(),convert(),case when then end,cast(),ISNULL(),Len(),Replace(),Right()
mysql数据库中CASE WHEN语句。 case when语句，用于计算条件...语法简单 CASE 函数： 代码如下:CASE input_expression WHEN when_expression THEN result_expression [ …n ] [ ELSE else_result_expression END
代码如下: –简单Case函数 CASE sex WHEN ‘1’ THEN ‘男’ WHEN ‘2’ THEN ‘女’ ELSE ‘其他’ END –Case搜索函数 CASE WHEN sex = ‘1’ THEN ‘男’ WHEN sex = ‘2’ THEN ‘女’ ELSE ‘其他’ END 这两种...
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# ...
when 条件1 then 值1 when 条件2 then 值2 else 其他值 END 别名 from 表名; 下面是一个分组求和示例： select sum(redpackmoney) as stota,ischeck from t_redpack group by isCheck 使用case ...
判断类似一个班级的男生和女生的人数，用sum (cese when then ) 代码如下: select count(er.execute_result), sum(case er.execute_result when 1 then 1 else 0 end) completed, sum(case er.execute_result when 0...
case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定...
例如,下面的语句显示中文年月 代码如下: select getdate() as 日期,case month(getdate()) when 11 then ‘十一’ when 12 then ‘十二’ else substring(‘一二三四五六七八九十’, month(getdate()),1) end+’月...
SELECT name AS Name, CASE category WHEN “Holiday” THEN “Seasonal” WHEN “Profession” THEN “Bi_annual” WHEN “Literary” THEN “Random” END AS “Pattern”FROM sales; 9 rows in set (0.00 ...
本文实例讲述了mysql存储过程之case语句用法。分享给大家供大家参考，具体如下： 除了if语句，mysql提供了一个替代的...END CASE; 我们可以使用简单CASE语句来检查表达式的值与一组唯一值的匹配，上述sql中，case_e
然后有一种语法让我眼前一亮，case when then else end 当满足CASE设定的条件时，就可以执行then语句。由于我要做的分组查询统计，是要罗列每一种情况，而且根据输入的“管理员编号”不同返回不同结果，结果记录的...
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY...
select @sql = @sql + ',sum(case 地区 when '''+地区+''' then 数量 end) ['+地区+']' + ',sum(case 地区 when '''+地区+''' then 比率 end) [比率]' from (select distinct 地区 from 表) as a select @sql...
(case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN score END ) AS 语文, MAX( CASE WHEN course='\u6570\...