加载中
mysql 分组行号查询

SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 ELSE 1 END AS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, ......

2019/03/16 09:23
1.8K
mysql 行号查询

1 SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS num, firstName, lastName FROM employees LIMIT 5; 2 SELECT (@row_number:=@row_number + 1) AS num, firstName, lastN...

2019/03/16 09:22
396
mysql 多行转多列

准备 /* Navicat Premium Data Transfer Source Server : root Source Server Type : MySQL Source Server Version : 50725 Source Host : localhost:3306 Source Schema : ......

2019/03/13 00:26
4.4K
mysql 生成日历

SELECT max( CASE dw WHEN 2 THEN dm END ) AS Mo, max( CASE dw WHEN 3 THEN dm END ) AS Tu, max( CASE dw WHEN 4 THEN dm END ) AS We, max( CASE dw WHEN 5 THEN dm END ) AS Th, max( C...

2019/03/10 21:42
881
mysql 判断闰年

判断2月份是不是28天 SELECT DAY ( LAST_DAY( DATE_ADD( DATE_ADD( DATE_ADD( CURRENT_DATE, INTERVAL - DAYOFYEAR( CURRENT_DATE ) DAY ), INTERVAL 1 DAY ), INTERVAL 1 MONTH ) ) ) FROM...

2019/03/10 19:31
1.6K
mysql 计算两个日期之间的工作日天数

创建透视表t500 建表 CREATE TABLE t500 ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=latin1; 插入500条数据 CREATE DEF...

2019/03/08 07:42
1.1W
sql 判断连续数字

设表名为TABLENAME,字段名为FIELD1则: SELECT FIELDN = FIELD1 FROM TABLENAME WHERE NOT EXISTS ( SELECT FIELD1 FROM TABLENAME A WHERE A.FIELD1 = tablename.field1 + 1 ) UNION SELECT...

2018/10/09 23:45
2K
Mysql 主从复制

master配置 server-id=1 log-bin=mysql-bin log-bin-index=binlog.index #binlog_format=statement general_log_file = mysql.log general_log = 1 binlog-do-db = youxia 2.master 创建用户...

2018/10/06 21:12
62
MySQL增加新用户

1.CREATE USER 'json'@'localhost' IDENTIFIED BY '123456'; 2.GRANT ALL PRIVILEGES ON * . * TO 'json'@'localhost'; 3.FLUSH PRIVILEGES; 4.use mysql; 5.update user set Host='%' where...

2018/10/06 10:44
87
update 多表

UPDATE base_teacher_attendance_schedule JOIN base_attendance_setting_record ON base_teacher_attendance_schedule.recordsettingid = base_attendance_setting_record.id SET base_teac...

2018/09/18 16:36
108
MySQL 获取不同字段最大值最小值

GREATEST LEAST 两个函数

2018/07/25 08:09
2.3K
mysql 1067 invalid default value for

在执行的sql第一句加上 set sql_mode = '';

2018/06/13 16:48
405
Oracle递归查询

contact by prior start with 官方说明

2018/05/09 23:24
61
MySQL 创建函数

DELIMITER $$ create   function sayhello() returns varchar(200) begin return 'hello boy'; end$$ DELIMITER ;...

2018/04/28 00:18
220
MySql中取出每个分组中的前N条记录

需求是这样的(CSDN上的一个问题):mysql中有个表:article(字段:id,type,date),type有1-10,10种类型。现在要用SQL找出每种类型中时间最新的前N个数据组成的集合。 这个问题应该有很多...

2018/04/27 23:35
247
SQL查找重复记录

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)...

2018/04/03 07:20
96
mysql

sum函数返回BigDecimal类型 替换 distinct --distinct 去重复效率低下: 可以通过exists实现: select distinct d.dname from dept d,emp e where d.deptno=e.deptno (效率低下) ...

2018/03/27 21:51
80
DB2笔记

刷新表 call sysproc.admin_cmd ( 'reorg table SCM_BASECATEGORY' ) 重新设置设置主键初始值 alter table SCM_BASECATEGORY alter column BASECATEGORY_ID restart with 317; DB2展示分组...

2017/09/15 18:40
41
SQL查询存在一个表而不在另一个表中的数据

方法1 使用 not in ,容易理解,效率低 select distinct A.ID from A where A.ID not in (select ID from B) 方法2 使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 nu...

2017/02/03 14:59
976

没有更多内容

加载失败,请刷新页面

返回顶部
顶部