sql使用技巧1

1.记录sql使用技巧

• 建表语句：
• CREATE TABLEuser1` (

CREATE TABLE user1 (
id int(11) NOT NULL COMMENT ‘主键’,
user_name varchar(255) DEFAULT NULL COMMENT ‘姓名’,
over varchar(255) DEFAULT NULL COMMENT ‘结局’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO blog.user1(id, user_name, over) VALUES (1, ‘唐僧’, ‘旃檀功德佛’);
INSERT INTO blog.user1(id, user_name, over) VALUES (2, ‘猪八戒’, ‘净坛使者’);
INSERT INTO blog.user1(id, user_name, over) VALUES (3, ‘孙悟空’, ‘斗战胜佛’);
INSERT INTO blog.user1(id, user_name, over) VALUES (4, ‘沙僧’, ‘金身罗汉’);

CREATE TABLE user2 (
id int(11) NOT NULL COMMENT ‘主键’,
user_name varchar(255) DEFAULT NULL COMMENT ‘姓名’,
over varchar(255) DEFAULT NULL COMMENT ‘结局’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO blog.user2(id, user_name, over) VALUES (1, ‘孙悟空’, ‘成佛’);
INSERT INTO blog.user2(id, user_name, over) VALUES (2, ‘牛魔王’, ‘被降服’);
INSERT INTO blog.user2(id, user_name, over) VALUES (3, ‘蛟魔王’, ‘被降服’);
INSERT INTO blog.user2(id, user_name, over) VALUES (4, ‘鹏魔王’, ‘被降服’);
INSERT INTO blog.user2(id, user_name, over) VALUES (5, ‘狮驼王’, ‘被降服’);

CREATE TABLE user_kills (
id int(11) NOT NULL,
user_id int(11) DEFAULT NULL,
timestr datetime DEFAULT NULL,
kills int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (1, 2, ‘2013-01-10 00:00:00’, 10);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (2, 2, ‘2013-02-10 00:00:00’, 2);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (3, 2, ‘2013-02-05 00:00:00’, 12);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (4, 4, ‘2013-01-10 00:00:00’, 3);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (5, 2, ‘2013-02-11 00:00:00’, 5);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (6, 2, ‘2013-02-06 00:00:00’, 1);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (7, 3, ‘2013-01-11 00:00:00’, 20);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (8, 2, ‘2013-02-12 00:00:00’, 10);
INSERT INTO blog.user_kills(id, user_id, timestr, kills) VALUES (9, 2, ‘2013-02-07 00:00:00’, 17);
`

如何查询出四人组中打怪最多的日期？

子查询：
select
  a.user_name,
  b.timestr,
  b.kills 
from
  user1 a
  join user_kills b on a.id = b.user_id 
  where kills = (
	select max(kills) from user_kills c
	where c.user_id =b.user_id
  );
  
优化后：连接查询
SELECT
	a.user_name,
	b.timestr,
	b.kills 
FROM
	user1 a
	JOIN user_kills b ON a.id = b.user_id
	JOIN user_kills c ON c.user_id = b.user_id 
GROUP BY
	a.user_name,
	b.timestr,
	b.kills 
HAVING
	b.kills = max(
	c.kills 
	)
	
mysql 特有的多行过滤：
	  select a.user_name, b.timestr, kills from user1 a join user_kills b on  a.`id` = b.`user_id` where 
  (b.`user_id`, b.`kills`) in (select user_id,max(kills) from user_kills group by user_id);