SQL
文章平均质量分 56
LitteBB
这个作者很懒,什么都没留下…
展开
-
Select From (Select from) as Alias
Sometimes we will get a column which contains a series of calculation such as ‘+‘or’-‘or’*‘or’/‘, meanwhile, we need use this result to do some other calculations as a result to be a final column For原创 2016-03-22 14:33:32 · 1376 阅读 · 0 评论 -
实际工作中用到的:同店换房时对新房间房费计算
需求: 1. 换房时,要选新房间,新房间的价格根据老房间的租期长短(是否长租)来决定是否加价; 2. 新房间的合同开始日期即为换房当日,合同结束日期为老合同房费结束日; 3. 新房间的这段时间内的房费需要单独计算(因为这段区间不一定是整数个月) 比如: 2016-05-10~2016-08-20 房费2000 2016-05-10~2016-06-09 1个整月 2000 201原创 2016-11-15 17:25:23 · 842 阅读 · 0 评论 -
实际工作用到的:同店换房时计算老房间费用
需求: 1. 租房时,房间由于其他原因,需要换房 2. 老房间的费用需要进行结算:2016-11-15开始换房,老房间2017-02-20房间到期,房费缴纳到2016-12-19 3. 换房时,需要先退还2016-11-15~2016-12-19日的房费/服务费,并对已用的水电费进行结算主要用到的思想: 1. 先将水电费手动计算并插入到临时表1中; 2. 然后查询页面上所展示出来的数据并插原创 2016-11-15 17:02:37 · 608 阅读 · 0 评论 -
Java连接sql server实例(初级入手记载)
实际工作中,闲来想试一试通过java连接sql server 数据库并进行DDL操作。网上的例子数不胜数,实测有效,不过觉得对目前的工作有点鸡肋。在此仅做记载之用~~~后续如果又需要,再继续研究!需要先下载sqljdbc4:下载地址(地址可能会失效,是因为我整理了网盘资料): http://pan.baidu.com/s/1gfv7VTT最简单的代码来了:package com.jdbc;impo原创 2016-11-18 09:47:02 · 590 阅读 · 0 评论 -
表变量与临时表的区别和联系
关于表变量和临时表原创 2016-11-16 09:58:04 · 644 阅读 · 0 评论 -
线上生成测试门店:实际工作中用到的
现在有这样的一个需求: 1. 所有线上数据不可以操作,一旦功能上线后,不能在线上环境进行测试; 2. 需要创建门店,以供测试做法如下:/*创建新门店及相关数据*/--1. 将张江店数据导入临时表select top 10 * into #T from Store where storeid = 'SHOP002'--2. 讲临时表数据更新为新门店UPDATE #T SET sto原创 2016-10-18 10:33:57 · 539 阅读 · 0 评论 -
根据起止日期生成连续日期
根据起止日期生成连续的日期,又两种方法,如下:--- 方法一select ContractRoomID,dateadd(day,number,'2016-07-15'),0 from ContractRoom ccleft join master.dbo.spt_values on number <=DATEDIFF(day,'2016-07-15','2016-08-15')where cc.原创 2016-10-18 10:11:15 · 1513 阅读 · 0 评论 -
触发器:实际工作中创建脚本时用到的
现在有这样的一个需求: 1. 我需要通过脚本自动创建预约订单,数据存在表CaseRecord中 2. 订单号为一个固定格式自增长的:S16000001~S16009999 3. 这个订单号的后四位是从一个表EntitySerialNo中的CurrentSerialNo字段取值如果通过页面一步步操作,会自动生成订单号,这样做效率太低。我要做的是通过脚本自动创建订单,原创 2016-07-13 19:56:13 · 911 阅读 · 0 评论 -
Actual practice - search some columns in one table but not that table - 8
Now take a look at two results :There are Table A and Table B, and I need find some loans which are exist in Table A but not in Table B.Here are 3 effective ways to achieve this purposeselect zard.loan原创 2016-04-21 16:25:25 · 552 阅读 · 0 评论 -
Actual Practise : Row_Number() over(partition by colnum1 order by colnum2 ) as No - 7
Let us see a requirement : we need show the Max(ID) of each loan record however each loan has one more records. How will we do? The best and the first way is :Let me show you by Row_Number() Ov原创 2016-04-13 17:28:23 · 1666 阅读 · 0 评论 -
Conclusion for my actual job in my work
Here I will make a conclusion for the day before today I have noted in blog. We can refer it to Select from (select from) , Case when then else end ,Functions and Internet!This is an actual instance in原创 2016-03-24 10:37:08 · 417 阅读 · 0 评论 -
Actual Practice : Scripts Integration in my work - 6
Below script is my actual script to solve or detach data I need. They are : RemainingTermBeforeModification ; currentAmterm ; maximumPaymentterm_NPV ; maximumAMTerm_NPV ; AmortizationTermBeforeModifi原创 2016-03-23 19:16:48 · 501 阅读 · 0 评论 -
Actual Practice : Scalar Valued Functions in my work - 2
Scalar Valued Functions Let us direct to actual instance原创 2016-03-21 13:14:59 · 555 阅读 · 0 评论 -
Actual Practice : Table Valued Functions in my work -3
Table Valued Functions Let us have a look a example:........ALTER FUNCTION [dbo].[GetDetails] ( @INPUTSQL VARCHAR(MAX) ) RETURNS @A TABLE ( ID INT IDENTITY(1,1), Details VARCHAR原创 2016-03-21 13:46:00 · 342 阅读 · 0 评论 -
Actual Practice : [with CTE]&[convert]&[coalesce] as in my work - 4
When some conditions of ‘Where’ part are fetched from another sql statement, usually, we can write as below :select * from Haaaaaaluationwhere HaaaaaavaluationId in ( select HaaaaaavaluationId fr原创 2016-03-22 15:43:23 · 491 阅读 · 0 评论 -
Actual Practice : DATEADD & DATEDIFF in my work - 1
an example to use DATEADD and DATEDIFF原创 2016-03-21 10:59:48 · 768 阅读 · 0 评论 -
Actual Practice : Insert into select from in my work -5
In my present work of testing, SQL statements are the most usual of all the testing process. I must practise it as much as I can. All I write are a must skill of Testing. Now let me show them and this原创 2016-03-23 18:31:14 · 501 阅读 · 0 评论 -
Get SQL run time
Here are some ways to get our sql run timeThis way will more comprehensiveset statistics profile onset statistics io onset statistics time ongo......--your sqlsgoset statistics profile off2.原创 2016-03-22 16:17:34 · 792 阅读 · 0 评论 -
实际工作用到的:关于PIOVT和UNPIOVT的用法
Normally, we select as belowSELECT @@SPID AS SPID,CASE quoted_identifierWHEN 1 THEN 'ON' ELSE 'OFF' END QUOTED_IDENTIFIER,CASE arithabortWHEN 1 THEN 'ON' ELSE 'OFF' END ARITHABORT,CASE ansi_null_d原创 2016-03-21 16:24:09 · 1911 阅读 · 0 评论