常用sql

SQL语句多个字段排序

写在前面的话：

本次迭代，分配的任务有点多啊，好在本妈妈朝10晚6的加班加点。其中一项就是列表展示啊，

展示就展示呗，还要排序。排序也可以，测试时排序字段值为啥都一样啊。结果点一下一个顺序。

就想在第一个字段排序完，是不是可以用第二个字段排序

要求：降序排列

select * from table order by column1 desc, column2 desc (两个都是降序）

select * from table order by column1 ,column2 desc(一升一降）

也可以这么写，更直观

select * from table order by column1 asc, column2 desc (一升一降）

SQL数据库中把一个表中的数据复制到另一个表中

1、如果是整个表复制表达如下：

insert into table1 select * from table2

2、如果是有选择性的复制数据表达如下：

insert into table1(column1,column2,column3...) select column1,column2,colunm3... from table2

3、一个数据库中的表中的数据复制到另一个数据库中的一个表，使用方法如下：

insert into 数据库A.dbo.table1(col1,col2,col3...) select col1,col2,col3... from 数据库B.dbo.table2

MySQL查询倒数第二条记录实现方法
代码1如下:

select *
from tablename a
where (
select count(*)
from tablename b
where a.id<b.id)=1

代码2如下:
select *
from tb1
order by tb1.id desc
limit 1,1;

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

有时候我们只需要保留某个表最新的10条记录这样的需求

DELETE FROM t_price WHERE id NOT IN ( SELECT id FROM t_price ORDER BY id ASC LIMIT 10)#这个会出现如上的错误

上面的会出现错误，我们可以包装下，用如下的语句
DELETE FROM t_price WHERE id NOT IN (SELECT x.id FROM ( SELECT id FROM t_price ORDER BY id ASC LIMIT 20) AS X)

mysql分组取每组前几条记录(排名) 附group by与order by的研究

 

mysql分组取每组前几条记录(排名) 附group by与order by的研究，需要的朋友可以参考下

 

--按某一字段分组取最大(小)值所在行的数据

代码如下:

/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/

--创建表并插入数据：

代码如下:

create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go

--一、按name分组取val最大的值所在行的数据。

代码如下:

--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5

*/

本人推荐使用1,3,4,结果显示1,3,4效率相同，2，5效率差些，不过我3,4效率相同毫无疑问，1就不一样了，想不搞了。

--二、按name分组取val最小的值所在行的数据。

代码如下:

--方法1：select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值

*/

--三、按name分组取第一次出现的行所在的数据。

代码如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/

--四、按name分组随机取一条数据。

代码如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5

*/

--五、按name分组取最小的两个(N个)val

代码如下:

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2

*/

--六、按name分组取最大的两个(N个)val

代码如下:

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/

--七，假如整行数据有重复，所有的列都相同（例如下表中的第5,6两行数据完全相同）。
按name分组取最大的两个(N个)val

代码如下:

/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5

*/

附：mysql “group by ”与"order by"的研究

 这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况

这种需求，我想很多人都遇到过。下面是我模拟我的内容表

create table `book` (
    `name` varchar (30),
    `category_id` int (11),
    `date` varchar (60)
); 
insert into `book` (`name`, `category_id`, `date`) values('eee','1','2106-06-10 19:19:21');
insert into `book` (`name`, `category_id`, `date`) values('aaa','1','2106-06-10 19:15:21');
insert into `book` (`name`, `category_id`, `date`) values('bbb','2','2106-06-10 19:11:21');
insert into `book` (`name`, `category_id`, `date`) values('ddd','2','2106-06-10 13:14:21');
insert into `book` (`name`, `category_id`, `date`) values('ccc','1','2106-06-10 12:14:21');

我现在需要取出每个分类中最新的内容

select * from test group by category_id order by `date`

结果如下

明显。这不是我想要的数据，原因是msyql已经的执行顺序是

引用

写的顺序：select ... from... where.... group by... having... order by..
执行顺序：from... where...group by... having.... select ... order by...

所以在order by拿到的结果里已经是分组的完的最后结果。
由from到where的结果如下的内容。

到group by时就得到了根据category_id分出来的多个小组


到了select的时候，只从上面的每个组里取第一条信息结果会如下

即使order by也只是从上面的结果里进行排序。并不是每个分类的最新信息。
回到我的目的上 －－分类中最新的信息
根据上面的分析，group by到select时只取到分组里的第一条信息。有两个解决方法
1，where+group by（对小组进行排序）
2，从form返回的数据下手脚（即用子查询）

由where+group by的解决方法
对group by里的小组进行排序的函数我只查到group_concat()可以进行排序，但group_concat的作用是将小组里的字段里的值进行串联起来。

select group_concat(id order by `date` desc) from `test` group by category_id

再改进一下

select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc

子查询解决方案

select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc

 

查询语句中select from where group by having order by的执行顺序

 

 

1.查询中用到的关键词主要包含六个，并且他们的顺序依次为 

select--from--where--group by--having--order by 

 

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 

与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行 

from--where--group by--having--select--order by, 

from:需要从哪个数据表检索数据 

 

where:过滤表中数据的条件 

group by:如何将上面过滤出的数据分组 

having:对上面已经分组的数据进行过滤的条件 

 

select:查看结果集中的哪个列，或列的计算结果 

order by :按照什么样的顺序来查看返回的数据 

 

2.from后面的表关联，是自右向左解析的 

而where条件的解析顺序是自下而上的。 

 

也就是说，在写SQL文的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表）， 

而把能筛选出小量数据的条件放在where语句的最左边 （用小表去匹配大表）

 

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序 

 使用count（列名）当某列出现null值的时候，count（*）仍然会计算，但是count(列名)不会。 

二、数据分组(group by ): 

select 列a,聚合函数（聚合函数规范） from 表明 where 过滤条件 group by 列a 

group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组。 

三、使用having字句对分组后的结果进行筛选，语法和where差不多:having 条件表达式 

需要注意having和where的用法区别： 

1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。 

2.where肯定在group by 之前，即也在having之前。 

3.where后的条件表达式里不允许使用聚合函数，而having可以。 

四、当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是： 

1.执行where xx对全表数据做筛选，返回第1个结果集。 

2.针对第1个结果集使用group by分组，返回第2个结果集。 

4.针对第2个结集执行having xx进行筛选，返回第3个结果集。

3.针对第3个结果集中的每1组数据执行select xx，有几组就执行几次，返回第4个结果集。 

 5.针对第4个结果集排序。 

例子： 

完成一个复杂的查询语句，需求如下： 

按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分，为了尽可能地提高平均分，在计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩。 分析： 

1．要求显示学生姓名和平均分 

因此确定第1步select s_name,avg(score) from student 

2．计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩 

因此确定第2步 where score>=60 and s_name!=’jr’ 3．显示个人平均分 

相同名字的学生（同一个学生）考了多门科目 因此按姓名分组 确定第3步 group by s_name 4．显示个人平均分在70分以上 

因此确定第4步 having avg(s_score)>=70 5．按由高到低的顺序 

因此确定第5步 order by avg(s_score) desc 

五、索引  

1.索引是单独的数据库对象，索引也需要被维护。 2.索引可以提高查询速度，但会降增删改的速度。 3.通过一定的查询触发，并不是越多越好。 什么时候不适合用索引？ 

1.当增删改的操作大于查询的操作时。 2.查询的语句大于所有语句的三分之一时。 

创建索引语法：create index 索引名 on 表明 （列名） 删除索引语法：drop index 索引名

mysql limit之后想统计查询出来的条数的某个字段和？

select uid,skip,time from attendance where uid = 8499 order by time limit 3
上面是查询的结果，我想把skip字段的结果加起来，怎么实现？sum试过了，是统计所有的，limit的不起作用

 
用子查询的方式来实现：

select uid, sum(skip) as sumskip, time from (select uid, skip, time from attendance where uid = 8499order by time limit 3) as subt;

test