SqlServer
sql server top
SELECT TOP 2 * FROM table
查询表中前2条数据
SELECT TOP 50 PERCENT * FROM table
查询表中前百分之50的数据
查询数据前10-20条
select top 10 * from where (select top 20 * from tableName)
where
where (scflag = 0 or scflag is null)
where 后边有括号 d
SELECT * FROM student WHERE (YEAR(Sage)=1990 or Ssex='男')
SELECT * from student WHERE id = 1 or id = 2 AND age = 20;
and 优先级高一些 s
满足一个条件就可以
Sage is NULL sage=null
DISTINCT
去重复字段
getdate()
获取当前时间
DATEDIFF
返回两个日期之间的间隔。date-part以什么计算天/年?
DATEDIFF ( date-part, date-expression-1, date-expression-2 )
CONVERT()
函数是把日期转换为新数据类型的通用函数。3个参数 结果类型长度[截取] 时间 类型一般120[模板]
isnull(exper,0)
判断exper是否为空，是则返回1，否则返回0
year()
获取当前时间年
month()
获取当前月
DAY(GETDATE())
获取当前 日
获取当前 年月 --201705
select CONVERT(varchar(6),GETDATE(),112) --201804
select CONVERT(varchar(7),GETDATE(),120) --2018-04
获取当前 年月日 --20170512
select CONVERT(varchar(8),GETDATE(),112) --20180423
select CONVERT(varchar(10),GETDATE(),120) --2018-04-23
当前季度
select year(getdate())10000+((month(getdate())/3)3+1)*100 + 1 --季度第一天
select year(getdate())10000+((month(getdate())/3)3+3)*100 + 31 --季度最后一天
格式化时间
select CONVERT(varchar,GETDATE(),20) --2018-04-23 14:44:22
select CONVERT(varchar,GETDATE(),23) --2018-04-23
select CONVERT(varchar,GETDATE(),24) --14:44:22
select CONVERT(varchar,GETDATE(),102) --2018.04.23
select CONVERT(varchar,GETDATE(),111) --2018/04/23
select CONVERT(varchar,GETDATE(),112) --20180423
cast(month(GETDATE()) as VARCHAR)
数字转字符串后拼接
字符串转日期
select CONVERT(datetime,'2018-04-23',20) --参数3是模板
截取字符串 检索位置
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。
expressionToSearch ：用于被查找的字符串。
start_location：开始查找的位置，为空时默认从第一位开始查找。
LTRIM ( character_expression )删除字符变量中的起始空格
RTRIM ( character_expression ) 删除字符变量中的尾随空格
SUBSTRING ( expression , start , length ) 截取字符串
REVERSE 字符串反转
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] 查询字符串下标
Case when
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--------------------------------
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略
表达式
select a.ranking,b.bmmc,a.zpfz,
(case when a.zpfz>90 and a.zpfz<100 then '优秀' when a.zpfz>80 and a.zpfz<90 then '良好' else '一般' end) as name
from tjhkhtaskls_ab a LEFT JOIN tbmdic b on a.bmdm = b.bmdm
计算上个月第一天和最后一天
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
计算本月第一天和最后一天
select dateadd(dd,-day(getdate())+1,getdate())
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
获取当前月份的上个月：MONTH(DATEADD(MONTH,1,GETDATE()))
获取当前月份的下个月：MONTH(DATEADD(MONTH,-1,GETDATE()))
按周统计查询 在星期天时会获取下个星期的(bug)
当前时间周的起始日期（以周一为例）select DATEADD(week,DATEDIFF(week,0,getdate()),0)
上周起始：select dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上周起始：select dateadd(week,-2,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上上周起始：select dateadd(week,-3,DATEADD(week,DATEDIFF(week,0,getdate()),0))
//sql server 日期函数在星期天获取 有异常 先用java代替
public static String getLastWeekMonday() {
SimpleDateFormat foramt = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
if (1 == cal.get(Calendar.DAY_OF_WEEK)) {
cal.add(Calendar.DATE, -1);
}
cal.add(Calendar.DAY_OF_MONTH, -7);
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
return foramt.format(cal.getTime());
}
public static String getLastWeekSunday() {
SimpleDateFormat foramt = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
if (1 == cal.get(Calendar.DAY_OF_WEEK)) {
cal.add(Calendar.DATE, -1);
}
cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
return foramt.format(cal.getTime());
}
public static String getthisWeekMonday() {
SimpleDateFormat foramt = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
if (1 == cal.get(Calendar.DAY_OF_WEEK)) {
cal.add(Calendar.DATE, -1);
}
cal.add(Calendar.DAY_OF_MONTH, 0);
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
return foramt.format(cal.getTime());
}
public static String getthisWeekSunday() {
SimpleDateFormat foramt = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
if (1 == cal.get(Calendar.DAY_OF_WEEK)) {
cal.add(Calendar.DATE, 0);
}
cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
return foramt.format(cal.getTime());
}
今天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0
昨天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1
7天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7
30天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30
本月的所有数据：select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0
本年的所有数据：select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0
查询今天是今年的第几天： select datepart(dayofyear,getDate())
查询今天是本月的第几天：1. select datepart(dd, getDate())
2.select day(getDate())
查询本周的星期一日期是多少 （注意：指定日期不能是周日，如果是周日会计算到下周一去。所以如果是周日要减一天） SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
查询昨天日期：select convert(char,dateadd(DD,-1,getdate()),111) //111是样式号，（100-114）
查询本月第一天日期：Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday
查询本月最后一天日期：Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as lastday //修改-3的值会有相应的变化
本月有多少天：select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))
求两个时间段相差几天：select datediff(day,'2012/8/1','2012/8/20') as daysum
在指定的日期上±N天：select convert(char,dateadd(dd,1,'2012/8/20'),111) as riqi //输出2012/8/21
在指定的日期上±N分钟：select dateadd(mi,-15,getdate()) //查询当前时间15分钟之前的日期
grouip by
用分组函数时 查询的字段 要包含在聚合函数 或者 分组条件中 至于为什么 你懂的
select transTime,sum(fee) from charge_gantrypass_statistics where transTime >= dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0)) and transTime <= dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),6)) GROUP BY transTime
合并结果
SELECT '1' AS FLAG,rwflag 工单类型,count(*) 数量 from ttaskmainls WHERE cjsj>='2018-12-15' and cjsj <= '2019-6-16' GROUP BY rwflag
unION
ALL
SELECT '0' AS FLAG,jzflag 工单类型,count(*) 数量 from ttaskmainls WHERE cjsj>='2018-12-15' and cjsj <= '2019-6-16' GROUP BY jzflag
结果是两行一样的可以用union ALL h
union all 中不可以 用 order by 排序关键字
要是想排序 最后在 合并的每个select 添加 关键字
把关键字也进行排序
例子:
SELECT
train_type AS trainLevel,
SUM (train_session) AS num,
SUM (train_duration) AS timeNum,
SUM (person_count) AS personCount,
2 as rowNum
FROM
collective_train_record a
LEFT JOIN tbmdic b ON b.bmdm = a.bmdm
GROUP BY
train_type
UNION
SELECT
'总计' AS trainLevel,
SUM (c.num) AS num,
SUM (c.timeNum) AS timeNum,
SUM (c.personCount) AS personCount,
1 as rowNum
FROM
(
SELECT
train_type AS trainLevel,
SUM (train_session) AS num,
SUM (train_duration) AS timeNum,
SUM (person_count) AS personCount
FROM
collective_train_record a
LEFT JOIN tbmdic b ON b.bmdm = a.bmdm
GROUP BY
train_type
) c
ORDER BY
rowNum DESC,
num DESC,
timeNum DESC
行列转换问题
SELECT c.sjlxmc as eventType,count(*) as num from ttfgdls_tfsj a LEFT JOIN tsjlxdic_tfsj c on a.sjlxdm = c.sjlxdm GROUP BY c.sjlxmc
select
sum(CASE a.eventType WHEN '自撞' THEN num ELSE 0 END) as '自撞',
sum(CASE a.eventType WHEN '其他' THEN num ELSE 0 END) as '其他',
sum(CASE a.eventType WHEN '追尾' THEN num ELSE 0 END) as '追尾',
sum(CASE a.eventType WHEN '翻车' THEN num ELSE 0 END) as '翻车',
SUM(CASE a.eventType WHEN '阻塞' THEN num ELSE 0 END) as '阻塞',
sum(CASE a.eventType WHEN '相撞' THEN num ELSE 0 END) as '相撞'
from (SELECT c.sjlxmc as eventType,count(*) as num from ttfgdls_tfsj a LEFT JOIN tsjlxdic_tfsj c on a.sjlxdm = c.sjlxdm GROUP BY c.sjlxmc) a
case 判断列的内容 是否是when 然后选择 字段
SELECT c.sjlxmc as eventType,count(*) as num from ttfgdls_tfsj a LEFT JOIN tsjlxdic_tfsj c on a.sjlxdm = c.sjlxdm WHERE a.fqsj >= '2018-06-21' and a.fqsj <= '2019-06-21' GROUP BY c.sjlxmc
select
sum(CASE a.eventType WHEN '自撞' THEN num ELSE 0 END) as '自撞',
sum(CASE a.eventType WHEN '其他' THEN num ELSE 0 END) as '其他',
sum(CASE a.eventType WHEN '追尾' THEN num ELSE 0 END) as '追尾',
sum(CASE a.eventType WHEN '翻车' THEN num ELSE 0 END) as '翻车',
SUM(CASE a.eventType WHEN '阻塞' THEN num ELSE 0 END) as '阻塞',
sum(CASE a.eventType WHEN '相撞' THEN num ELSE 0 END) as '相撞'
from (SELECT c.sjlxmc as eventType,count(*) as num from ttfgdls_tfsj a LEFT JOIN tsjlxdic_tfsj c on a.sjlxdm = c.sjlxdm WHERE a.fqsj >= '2018-06-21' and a.fqsj <= '2019-06-21' GROUP BY c.sjlxmc) a
对于一行或者一列 问题 可以不用转换 直接用list int 接收就可以
追加一行内容用
UNION all
追加一列内容
select * from (select roadid,count(*) as tnum from alarmEvents GROUP BY roadid) a
LEFT JOIN
(select roadid,count(*) as fnum from alarmEvents where judge_result in ('1','3','4') GROUP BY roadid) b on a.roadid = b.roadid
查询日期
List<AccidentStatisticsTable> list= constructionStatisticsDao.getDailyAccidentOccurrencNum(starttime,endtime);
System.out.println(list);
System.out.println();
List list1 = new ArrayList();
for (int i = Integer.valueOf(starttime.substring(starttime.length()-2,starttime.length()));i<=Integer.valueOf(endtime.substring(endtime.length()-2,endtime.length()));i++){
for (AccidentStatisticsTable x:list){
if (i==x.getRq()){
list1.add(x.getNnum());
}
}
if (list1.size()<i){
list1.add(0);
}
}
经典sql
left(TOLLSTANDARDNAME,
CASE CHARINDEX('(',TOLLSTANDARDNAME)
WHEN 0 THEN len(TOLLSTANDARDNAME)
ELSE CHARINDEX('(',TOLLSTANDARDNAME)-1 END) as tollsName,
浙公网安备 33010602011771号