一些sql用法例子【Updating】

1、利用instr连接表做字段查询,group_concat做值的合并:

create table ab(product_id int,product_name varchar(10), product_type_id varchar(10));

insert into ab values(1,'产品A','1,2'),(2,'产品B','2,3');

create table ac(product_type_id int,product_type_name varchar(10));

insert into ac values(1,'类别1'),(2,'类别2'),(3,'类别3');

select product_id,product_name,ab.product_type_id,group_concat(product_type_name) as typename from ab,ac where instr(ab.product_type_id,ac.product_type_id) group by product_id,product_name;

root @localhost : bbb 14:45:24>select * from ab;

+------------+--------------+-----------------+

| product_id | product_name | product_type_id |

+------------+--------------+-----------------+

|          1 | 产品A        | 1,2             |

|          2 | 产品B        | 2,3             |

+------------+--------------+-----------------+

2 rows in set (0.01 sec)

root @localhost : bbb 14:55:54>select * from ac;

+-----------------+-------------------+

| product_type_id | product_type_name |

+-----------------+-------------------+

|               1 | 类别1             |

|               2 | 类别2             |

|               3 | 类别3             |

+-----------------+-------------------+

3 rows in set (0.00 sec)
root @localhost : bbb 14:55:56>select product_id,product_name,ab.product_type_id,group_concat(product_type_name) as typename from ab,ac where instr(ab.product_type_id,ac.product_type_id) group by product_id,product_name;

+------------+--------------+-----------------+-----------------+

| product_id | product_name | product_type_id | typename        |

+------------+--------------+-----------------+-----------------+

|          1 | 产品A        | 1,2             | 类别2,类别1     |

|          2 | 产品B        | 2,3             | 类别2,类别3     |

+------------+--------------+-----------------+-----------------+

2 rows in set (0.01 sec)

root @localhost : bbb 14:56:00>

from:http://topic.csdn.net/u/20120913/15/22d39ecf-da3e-4088-a7d1-aa095cae4b7a.html

2、find_in_set 用法:

有两张表:

a: id name 1  tom 2  terry ... b: fid  ids 1    1,2 ...

如果需要根据表b的ids列找出表a对应的name:

 select name from a where find_in_set (id ,(select ids from b where fid = 1))

http://topic.csdn.net/u/20120921/16/3f940141-7d64-46b9-9a87-c0dbf4ed4ae9.html

3、MySQL group by with rollup的用法:对列做统计

GROUP BY Modifiers  官方手册里面对这个rollup有一个专门的页面介绍 地址在这里,说得非常详细,我这里做一个简单的例子重现

建一个简单的表并插入几条简单的数据

CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
insert into t valeu(11,11),(12,12),(13,13);

先来做一个查询

root@test 03:44:32>select id,sum(id2),avg(id2) from t group by id with rollup;
+——+———-+———-+
| id | sum(id2) | avg(id2) |
+——+———-+———-+
| 11 | 11 | 11.0000 |
| 12 | 12 | 12.0000 |
| 13 | 13 | 13.0000 |
| NULL | 36 | 12.0000 |
+——+———-+———-+
4 rows in set (0.00 sec)

我们可以看到,对于group by的列,with rollup将不会做任何的操作,而是返回一个NULL,而没有group by的列,则根据前面的avg函数和sum函数值产生的列做了统计处理。这样 group by + 聚合函数 统计了行数据,而 with rollup 产生了列数据,即生成了一张行列交错的统计报表。

4、自连接生成id范围段

mysql> select * from table1;
+----+------+
| id | num  |
+----+------+
|  1 | 1001 |
|  2 | 1001 |
|  3 | 1001 |
|  4 | 1001 |
|  5 | 1002 |
|  6 | 1002 |
|  7 | 1001 |
|  8 | 1001 |
|  9 | 1002 |
+----+------+
9 rows in set (0.00 sec)

mysql> select num,
    ->  concat('连续',count(*) ,'次 从', min(sid) ,'至',eid) as cnt
    -> from (
    ->  select a.id as sid,max(b.id) as eid,a.num
    ->  from table1 a , table1 b
    ->  where a.id<=b.id
    ->  and a.num=b.num
    ->  and not exists (select 1 from table1 where id between a.id and b.id and num!=a.num)
    ->  group by a.id
    -> ) t
    -> group by eid;
+------+----------------+
| num  | cnt            |
+------+----------------+
| 1001 | 连续4次 从1至4 |
| 1002 | 连续2次 从5至6 |
| 1001 | 连续2次 从7至8 |
| 1002 | 连续1次 从9至9 |
+------+----------------+
4 rows in set (0.00 sec)

mysql>

http://bbs.csdn.net/topics/390420571

5、MySQL如何查询两列互不重复的记录?

数据如下:

id     date         fromId      toId --------------------------------------  1     2013-01-01     1           2  2     2013-01-02     2           1  3     2013-01-03     1           3  4     2013-01-04     3           1  5     2013-01-05     4           1  6     2013-01-06     1           4

如何才能查询出fromId或toId包含某个值,但fromId和toId不相互重复的数据? 例如,查询fromId或toId包含1,去除fromId和toId中数据互换的列,仅取日期最大的值,查询结果为:

 id     date         fromId      toId --------------------------------------  2     2013-01-02     2           1  4     2013-01-04     3           1  6     2013-01-06     1           4

SELECT max(`date`),maxId,minId FROM (SELECT `date`,IF(fromId>toId,fromId,toId) AS maxId,IF(fromId>toId,toId,fromId) AS minId FROM `table`) AS `tmp` GROUP BY maxId,minId

http://segmentfault.com/q/1010000000191842

6、select sum(case when then end) as group by 按指定维度多行统计求和

我有表table_1 name class score 张三  数学   80 张三  语文   70 李四  数学   70 李四  语文   80 一个sql查询出每个人每科的总分:

select name,
sum(CASE WHEN class ='数学' THEN score END) as `数学`,
sum(CASE WHEN class ='语文' THEN score END) as `语文`,
from table_1 
where name='张三'
group by name

http://bbs.csdn.net/topics/390445500

7、自连接求子节点记录数:

id    pid   (tab) 33    0       55    52      54    52      52    0     

结果:

id  子结点数 33    0 52    2

select id,(select count(*) from tab where pid=t.id) as 子结点数
from tab t
where pid=0

http://bbs.csdn.net/topics/390473594

8、update join 关联条件修改

abc表: 工程ID                 项目                           状态 ---------------------------------------------------------- 1                         东部污水处理             正在进行中 1                         建设路排水                未开始 1                         东方广场                   已完成 1                         德明旅馆装修             已完成 2                         创业大厦                   已完成 2                         星光酒店水电             已完成 3                         丽冬广场                   正在进行中 3                         新世界广场                已完成 xyz表: 工程ID                 全部完成 ------------------------------------------ 1                         否 2                         是 3                         否 判断abc表同一工程ID下全部项目是否标记已完成,如果是,则update xyz表对应的列”全部完成“为”是“

update xyz inner join (select max(if(状态='已完成',0,1)) as K from abc group by 工程ID) b on xyz.工程ID=b.工程ID
set 全部完成=IF(b.K=0,'是','否')

http://bbs.csdn.net/topics/390478379

9、利用函数返回值进行 order by 排序:

CREATE TABLE `goods` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `brand_id` varchar(5)  NOT NULL DEFAULT '0',
  `name` varchar(10) NOT NULL,
  `click_count` varchar(10)  NOT NULL DEFAULT '0',
  `number` varchar(5)  NOT NULL DEFAULT '0',
  `salesnum` varchar(10) NOT NULL DEFAULT '0',
  `desc` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB CHARACTER SET utf8;


INSERT INTO `goods` VALUES (901,  '26', 'T恤---售馨', '939', '1', '', '');
INSERT INTO `goods` VALUES (783,  '26', '雪纺上衣', '639', '5', '', '');
INSERT INTO `goods` VALUES (814,  '26', '雪纺上衣', '1059', '19', '', '');
INSERT INTO `goods` VALUES (1577, '26', '短袖荷叶领', '647', '87', '', '');
INSERT INTO `goods` VALUES (1769, '26', '透气纯色纽扣上衣', '2451', '24', '', '');
INSERT INTO `goods` VALUES (1642, '26', ' 纯色短袖', '534', '64', '', '');

-- 注意 find_in_set 中的待匹配字符串不允许有空格
mysql> SELECT * FROM `goods` WHERE id IN ('783','769','814','1577','1769') ORDER BY FIND_IN_SET(id, '783,769,814,1577,1769' ) ;
+------+----------+--------------------------+-------------+--------+----------+------+
| id   | brand_id | name                     | click_count | number | salesnum | desc |
+------+----------+--------------------------+-------------+--------+----------+------+
|  783 | 26       | 雪纺上衣                 | 639         | 5      |          |      |
|  814 | 26       | 雪纺上衣                 | 1059        | 19     |          |      |
| 1577 | 26       | 短袖荷叶领               | 647         | 87     |          |      |
| 1769 | 26       | 透气纯色纽扣上衣         | 2451        | 24     |          |      |
+------+----------+--------------------------+-------------+--------+----------+------+
4 rows in set (0.02 sec)

mysql> SELECT * FROM `goods` WHERE id IN ('783','769','814','1577','1769') ORDER BY FIND_IN_SET(id, '814,1577,1769,783,769' ) ;
+------+----------+--------------------------+-------------+--------+----------+------+
| id   | brand_id | name                     | click_count | number | salesnum | desc |
+------+----------+--------------------------+-------------+--------+----------+------+
|  814 | 26       | 雪纺上衣                 | 1059        | 19     |          |      |
| 1577 | 26       | 短袖荷叶领               | 647         | 87     |          |      |
| 1769 | 26       | 透气纯色纽扣上衣         | 2451        | 24     |          |      |
|  783 | 26       | 雪纺上衣                 | 639         | 5      |          |      |
+------+----------+--------------------------+-------------+--------+----------+------+
4 rows in set (0.00 sec)

mysql>

http://bbs.csdn.net/topics/390528130

10、left join 左连接、去重、分组统计等

有两个表:

create table t1(
id int,
key1 int,
key2 int,
key3 int,
key4 int
);
insert into t1 values
(1, 112, 222, 333, null),
(2, 112, 222, 333, null),
(3, 134, 222, 333, 444 ),
(4, 134, 000, 333, 444 ),
(5, 178, 212, 312, 412 );

create table t2(
id int
);
insert into t2 values
(1),
(1),
(2),
(4),
(5);

我想要对该表进行信息的统计: 1)查询table2,id去重;得到id=1、2、4、5; 2)利用id与table1关联,并根据key1、key2、key3、key4进行统计:id=1和id=2的几个key都一样,因此这几个key为一组,且出现次数为2 示例结果: key1      key2    key3      key4   times 112       222      333      null     2 134       222      333      444      0 134       000      333      444      1 178       212      312      412      1

SELECT  a.`key1`,a.`key2`,a.`key3`,a.`key4`,COUNT(DISTINCT b.id) FROM t1 a
LEFT JOIN t2 b ON a.`id`=b.`id`
GROUP BY a.`key1`,a.`key2`,a.`key3`,a.`key4`;

http://bbs.csdn.net/topics/390598979

类似的问题:有一个表:

create table tt(
id  int,
`key` int,
type varchar(10)
);
insert into tt values
(123,11111,	  "first"),
(456,11111,	 "second"),
(456,11111,     "second" ),
(789,22222,     "second" ),
(890,22222,     "second" ),
(456,22222,      "first" );

我想要对该表进行信息的统计: 1)根据id要去重; 2)统计key的次数; 3)统计每个key对应的type的次数 示例结果:  key      times    first_times    second_times 11111      2           1               1 22222      3           1               2

select `key`,count(*),sum(if(type='first',1,0)),
sum(if(type='second',1,0))
from (
select distinct id,`key`,type from tt) a
group by `key`;

http://bbs.csdn.net/topics/390598827?page=1#post-395639648

11、 得到一天中每10分钟的时间

DELIMITER $$
DROP PROCEDURE IF EXISTS tt$$
CREATE PROCEDURE tt()
BEGIN
SET @i=1;
SET @a=CONCAT(CURDATE(),' 00:00:00');
insert into lsb values(date_add(@a, interval 10 minute));
 WHILE @i<=144 DO
insert into lsb values(date_add(@a, interval 10 minute));
 
 SET @a=DATE_ADD(@a, INTERVAL 10 MINUTE);
 SET @i=@i+1;
 END WHILE;
 END$$
DELIMITER ;

12、从8月26号到9月25号这段时间内记录了每一天的重量,

现在想按 26-31号,1-5号,6-10号,11-15号,16-20号,21-15号 

这6个时间段内重量的总和 这个sql要怎么写?

要点:使用 TIMESTAMPDIFF 求得每个日期的所属区间范围,进而分组求值。

select TIMESTAMPDIFF(day,recdate,'2013-08-26') div 5,sum(weight)
from tb 
group by  TIMESTAMPDIFF(day,recdate,'2013-08-26') div 5

13、find_in_set 判断字段关系做表间连接条件:

table 1 id  table2_id 1   1,2,3 table 2  id  value1 1    a 2    b 3    c 想要的是 id   value 1    a,b,c 

select table1.id,group_concat(table2.value1)
from table1,table2
where find_in_set(table2.id,table1.table2_id)
group by table1.id

http://bbs.csdn.net/topics/390706383

14、sql 表自连接组间比较大小:

求出所有人中,这次考试比上次考试得分高的有几个 事例表如下 ID Name Score TestOrder 1  小明 90    1 2  小王 80    1 3  小红 80    1 4  小明 60    2 5  小王 90    2 6  小红 80    2

select A.*
from tb A,tb B
where A.name=B.name and A.testorder = B.testorder-1 and A.score>B.score;

或者:

select * from TABLE A
where exists (select 1 from TABLE where A.score> score and A.name = name and testorder = 2)
and A.testorder = 1;

15、多字段更新:set 中使用 case when then else 表达式进行条件更新

CREATE TABLE user_test(
  id int,
  uid int
);

insert into user_test values(11,22),(22, 33),(44, 22),(11,22),(22, 33),(44, 22);

update user_test
set 
    id = case when id in (44) then 4444 else id end,
    uid = case when uid in(22) then 2222 else uid end
where id > 0;

16、mysql 里的二次排序

表 score,包含三个字段 stdno(int),subject(int),score(int) 分别表示 学号、科目、成绩 数据示例:1, 1, 50  表示学号为1的学生,第1个科目成绩为50    2,2,60 表示学号为2的学生,第2个科目的成绩为60 现在要求按照以下规则排序: 1、以科目3的成绩从大到小对学生进行排序 2、单个学生的记录按科目从小到大进行排序 排序后的结果如下: 5、1、xxx 5、2、xxx 5、3、100 5、4、xxx 2、1、xxx 2、2、xxx 2、3、99 2、4、xxx 8、1、xxx 8、2、xxx 8、3、98 8、4、xxx ...

select A.* from (
select stdno, score from score where subjectId = 3 order by score desc) B
inner join score A
on A.stdno = B.stdno order by B.score desc, A.subjectId asc;

select *
from score s
order by (select score from score where stdno=s.stdno and subject=3) desc, a.subject asc;

http://bbs.csdn.net/topics/390803079

17、巧用 ON DUPLICATE KEY UPDATE 实现原值+插入值更新

mysql表如下:

CREATE TABLE `test` (
  `id`int(11) NOT NULL  auto_increment,
  `cnt` double(15,3) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=167 DEFAULT CHARSET=utf8 PACK_KEYS=0;

我想在这张表里批量修改cnt字段,需要在这个字段的原值上加上某一个值。 如果只是修改一行记录,这样写就行了 update  test  set  cnt  =  cnt+某值  where id = 某行id;

分2条SQL执行,
UPDATE、INSERT
或者:
INSERT INTO  test4(id,cnt) VALUES
 (167,2),
 (4,3),
 (6,5),
 (7,9)
ON DUPLICATE KEY UPDATE id = VALUES(id), cnt= VALUES(cnt)+  cnt

http://bbs.csdn.net/topics/390840353

18、常见SQL统计函数示例:

108.聚合去重查询优化:count(1) 在列为 Null 时的陷阱:
	--谈谈分布式Aggregation
	Q1:SELECT A, COUNT(DISTINCT B), SUM(C) FROM T GROUP BY A;
	Q2:SELECT A, COUNT(1), SUM(SC) FROM 
       (SELECT A, COUNT(1), SUM(C) AS SC 
       FROM T GROUP BY A, B) TT GROUP BY A;
	Q1和Q2的语义在B有null的时候并不等价,将Q2改为如下,这样两个查询的语义就是相同的了:
	SELECT A, COUNT(B), SUM(SC) FROM (SELECT A, B, SUM(C) AS SC FROM T GROUP BY A, B) TT GROUP BY A
109.查询一段时间内的用户下单次数分布情况:
	-- 首先计算每个用户的下单次数,然后使用 CASE..WHEN 语法来分组。
	SELECT 
		CASE
			WHEN c < 10 THEN '<10'
			WHEN c < 20 THEN '<20'
			WHEN c < 100 THEN '<100'
			ELSE '>100'
		END,
		COUNT(*)
	FROM (
		SELECT user_id, COUNT(*) AS c FROM events
		WHERE date BETWEEN '2015-09-01' AND '2015-09-20' AND event = 'SubmitOrder'
		GROUP BY 1
	)a 
	GROUP BY 1
110.查询做了行为 A 而没有做行为 B 的用户数
	--使用 LEFT OUTER JOIN 计算差集。
	SELECT a.user_id FROM (
		SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'BuyGold'
	) a
	LEFT OUTER JOIN (
		SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
	) b
	ON a.user_id = b.user_id
	WHERE b.user_id IS NULL
111.计算用户的使用时长
	--使用分析函数,根据每个用户相邻的两个事件的间隔估算累计使用时长,如果两次使用间隔超出10分钟则不计算。
	SELECT
	user_id,
	SUM(
		CASE WHEN
			end_time - begin_time < 600
		THEN
			end_time - begin_time
		ELSE
			0
		END
	) FROM (
		SELECT
			user_id,
			EXTRACT(EPOCH FROM time) AS end_time,
			LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
		FROM events
		WHERE date='2015-5-1'
	) a
	GROUP BY 1
112.查询漏斗第 1 步的流失用户的使用时长
	SELECT
	AVG(
		CASE WHEN
			end_time - begin_time < 600
		THEN
			end_time - begin_time
		ELSE
			0
		END
	) FROM (
		SELECT
			user_id,
			EXTRACT(EPOCH FROM time) AS end_time,
			LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
		FROM events
		WHERE date='2015-5-1' and user_id in (funnel_user(12, '2015-05-01', '2015-05-01', '1.8', true, 0))
	) a

来源:

http://bit.ly/2r0f9mx

https://www.sensorsdata.cn/manual/query.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT技术精选文摘

数据库SQL性能优化(一)

substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate), ...

2206
来自专栏人工智能LeadAI

MySQL的JOIN用法

数据库中的JOIN称为连接,连接的主要作用是根据两个或多个表中的列之间的关系,获取存在于不同表中的数据。连接分为三类:内连接、外连接、全连接。另外还有CROSS...

942
来自专栏码农二狗

mysql实现地理位置搜索

67710
来自专栏杨建荣的学习笔记

MySQL执行计划里面的key_len

以前看MySQL的执行计划,感觉内容有些简陋,平时分析主要就是看是否全表扫描,索引使用是否合理等。基本上也能分析出很多问题来,但是显然有时候会有些疑惑,那就...

3646
来自专栏用户2442861的专栏

好的数据库面试题集合

http://blog.csdn.net/sandyzhs/article/details/4059709

4071
来自专栏面朝大海春暖花开

解决mysql中limit和in不能同时使用的问题

两种方式推荐第一种。避免了in语句。进行explain诊断会发现第一种效率高很多。

872
来自专栏数据库

Java SQL语句优化经验

. (1) 选择最有效率的表名顺序(只在基于规则的seo/' target='_blank'>优化器中有效): ORACLE 的解析器按照从右到左的顺序处理FR...

3069
来自专栏猿人谷

Oracle SQL性能优化

(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最...

3797
来自专栏一名合格java开发的自我修养

mysql,SQL标准,多表查询中内连接,外连接,自然连接等详解之查询结果集的笛卡尔积的演化

a,系统首先执行from子句,这里from子句列出有两个表teacher表和course表,DBMS讲计算这两个表的笛卡尔积,列出这两个表中行的所以可能组合,形...

1912
来自专栏乐沙弥的世界

SQL server 2005 切换分区表

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx

983

扫码关注云+社区

领取腾讯云代金券