MySQL SQL 查询相关

发表于 | 分类于

MySql COUNT 加 IF 条件

1
2
3
#以下两条SQL查询结果一致
SELECT COUNT(1) FROM user WHERE account_status = 0;
SELECT COUNT(IF(account_status = 0, true, null)) FROM user;

MySql 排名、并列排名、查询指定用户名次

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 用户表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- 测试数据
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (1, 'A', 7);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (2, 'B', 3);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (3, 'C', 4);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (4, 'D', 0);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (5, 'E', 4);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (6, 'F', 9);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (7, 'G', 2);

-- 说明
@rank := @rank + 1 中, := 是赋值的作用, 意思是先执行 @rank + 1, 然后把值赋给 @rank
(SELECT @rank := 0) r 意思是设置 rank 字段的初始值为0

-- 1. 查询结果后排序
SELECT 
	u.*,
	@rank := @rank + 1 AS rank
FROM 
	(SELECT @rank := 0) r,
	`user` u
ORDER BY u.score DESC;

-- 2. 并列排名
SELECT 
	u.*,
	(CASE 
		WHEN @prev = u.score THEN @rank
		WHEN @prev := u.score THEN @rank := @rank + 1
		WHEN @prev = 0 THEN @rank := @rank + 1
	END) AS rank
FROM 
	(SELECT @rank := 0, @prev := NULL) r,
	`user` u
ORDER BY
	u.score DESC;

-- 3. 指定用户排名
SELECT
	*
FROM 
	(
		SELECT 
			u.*,
			(CASE 
				WHEN @prev = u.score THEN @rank
				WHEN @prev := u.score THEN @rank := @rank + 1
				WHEN @prev = 0 THEN @rank := @rank + 1
			END) AS rank
		FROM 
			(SELECT @rank := 0, @prev := NULL) r,
			`user` u
		ORDER BY
			u.score DESC
	) ur
WHERE
	ur.id = 3;

MySql 查询每件商品最低价格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# 1. 创建表
CREATE TABLE `goods_sku` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'SKU ID',
  `goods_id` int(11) unsigned NOT NULL COMMENT '商品ID',
  `sku_title` varchar(255) NOT NULL COMMENT '集成名称',
  `sku_price` decimal(12,2) NOT NULL COMMENT '价格',
  `sku_stock` int(11) NOT NULL COMMENT '库存',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `goods_id` (`goods_id`) USING BTREE,
  KEY `goods_id_price` (`goods_id`,`sku_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品SKU信息表';

# 2. 插入测试数据
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (1, 1, '颜色:红色 尺寸:l', 0.02, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (2, 1, '颜色:红色 尺寸:xl', 0.01, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (3, 1, '颜色:黄色 尺寸:l', 0.03, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (4, 1, '颜色:黄色 尺寸:xl', 0.04, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (5, 2, '颜色:红色 尺寸:l', 0.02, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (6, 2, '颜色:红色 尺寸:xl', 0.02, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (7, 2, '颜色:黄色 尺寸:l', 0.07, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (8, 2, '颜色:黄色 尺寸:xl', 0.01, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (9, 3, '颜色:红色 尺寸:l', 0.01, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (10, 3, '颜色:红色 尺寸:xl', 0.02, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (11, 3, '颜色:黄色 尺寸:l', 0.03, 10);
INSERT INTO `goods_sku`(`id`, `goods_id`, `sku_title`, `sku_price`, `sku_stock`) VALUES (12, 3, '颜色:黄色 尺寸:xl', 0.04, 10);

# 3.1 查询语句 - 子查询
SELECT
	id,
	sku_price 
FROM
	goods_sku gs 
WHERE
	NOT EXISTS ( SELECT 1 FROM goods_sku gss WHERE gs.goods_id = gss.goods_id AND gs.sku_price > gss.sku_price )
GROUP BY 
	goods_id;
	
# 3.2 查询语句 - JOIN 
SELECT
	gs.id,
	gs.sku_price
	#,gss.id
	#,gss.sku_price
FROM
	goods_sku gs
	LEFT JOIN goods_sku gss ON gss.goods_id = gs.goods_id AND gss.sku_price < gs.sku_price 
WHERE 
    gss.id IS NULL
0%