当前位置：首页 > news >正文

mysql 一些有意思的sql语句，备忘

news 2025/7/1 8:14:37

### 批量插入

```
INSERT INTO 表名（字段列表） VALUES （字段对应的值）,（字段对应的值）,（字段对应的值）,（字段对应的值）,
```

js 代码示例:
```
function batchAddOrderRecords(recordArr, callBack) {
callBack = callBack == null ? nop : callBack;

const partSql = 'INSERT INTO shop_order ( order_id, goods_id, user_id, number_all, vip_level, batch_id ) VALUES ';

// 拼接各行的值
let partSql1 = '';

for (let i = 0; i < recordArr.length; i += 1) {
const one = recordArr[i];
partSql1 += `('${one.orderId}', ${one.goodsId}, ${one.userId}, ${one.numberAll}, ${one.vipLevel}, ${one.batchId})`;

if (i + 1 < recordArr.length) {
partSql1 += ',';
}
}

const sql = `${partSql} ${partSql1}`;

query(sql, (err, rows) => {
if (err) {
callBack(null);
} else {
callBack(rows.affectedRows > 0);
}
});
}
```

### 两个表组合，获得笛卡尔积
比如商品表 + 玩家表，我需要获得每个人对应所有商品的所有记录

```
SELECT a.*, b.* FROM shop_goods_config a, tbl_user_info b
```

### 承接上一个，找出所有vip用户，时间段内，尚未发出的权益列表
会员lv1，每月1次优先购。会员lv2，每月2次优先购

```
function getNotSendRightByTime(startTimeStr, endTimeStr, callBack) {
callBack = callBack == null ? nop : callBack;

const sql = `SELECT
alldata.*
FROM
(
// 这部分是获取所有人应该分得的所有商品权益记录
SELECT
a.goods_id,
b.user_id,
a.goods_name,
a.vip_level,
a.number,
a.loop_type,
b.user_name,
cast( AES_DECRYPT( FROM_BASE64 ( b.mobile ), 'jeo8lD320uu298wF' ) AS CHAR ) AS mobile
FROM
// 这里是组合两个表，10 * 10 = 100条记录
shop_goods_config a,
tbl_user_info b
WHERE
// 筛选 vip用户
b.vip_level > 0
// 筛选只保留比玩家vip 等级低的商品记录
AND b.vip_level >= a.vip_level
) alldata
LEFT JOIN (
// 这部分是获取玩家本月已发放的记录
SELECT
user_id,
goods_id
FROM
shop_order
WHERE
user_id IN ( SELECT user_id FROM tbl_user_info WHERE vip_level > 0 )
AND vip_level > 0
// 限制月份
AND create_time >= '${startTimeStr}'
AND create_time <= '${endTimeStr}'
) senddata
// 这里是玩家Id+商品Id，确认唯一记录
ON alldata.goods_id = senddata.goods_id AND alldata.user_id = senddata.user_id
WHERE
// 这里是筛选历史记录的，就是没发过的
senddata.user_id IS NULL;`;

query(sql, (err, rows) => {
if (err) {
callBack(null);
} else {
callBack(rows);
}
});
}
```

### 递归查找我的上级

表名: tbl_user_invite
我的邀请码字段: invite_code
我的上级邀请码字段: parent_invite_code
参数，就是我的邀请码 : aaaaaaaa
```
SELECT
   lv,
   user_id,
   invite_code,
   parent_invite_code
FROM
   (
   SELECT
       @r AS _id,
       @l := @l + 1 AS lv,
       ( SELECT @r := parent_invite_code FROM tbl_user_invite WHERE invite_code = _id LIMIT 1 ) AS parent_id
   FROM
       ( SELECT @r := 'aaaaaaaa', @l := 0 ) vars,
       tbl_user_invite h
   ) T1
   JOIN tbl_user_invite T2 ON T1._id = T2.invite_code
GROUP BY
   invite_code
ORDER BY
   lv;
```

### 递归查找我的下级，各层级都有谁

表名: tbl_user_invite
我的邀请码字段: invite_code
我的上级邀请码字段: parent_invite_code
参数，就是我的邀请码 : aaaaaaaa
```
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( invite_code ) FROM tbl_user_invite WHERE FIND_IN_SET( parent_invite_code, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
tbl_user_invite,
( SELECT @ids := 'aaaaaaaa', @l := 0 ) b
WHERE
@ids IS NOT NULL

```

### 查询记录时，把时间字段格式化

```
SELECT
DATE_FORMAT( update_time, '%Y-%m-%d %H:%i:%s' ) AS create_time
FROM
tbl_user_info
```

### 汇总玩家战绩
count + if 根据条件统计条数
CASE WHEN 根据游戏类型，换算分数
TO_DAYS 筛选今天。但这个效率偏低，需要数据库试试去计算。最好还是根据时间字符串，进行范围检索
```
SELECT
user_id AS userId,
user_name AS userName,
head_icon AS headIcon,
count( id ) AS gameCount,
count(IF( big_win = 1, 1, NULL )) AS bigWinCount,
IFNULL(sum(CASE
WHEN tab.game_type = 8
OR tab.game_type = 17
OR tab.game_type = 18
OR tab.game_type = 19
OR tab.game_type = 20
OR tab.game_type = 25
OR tab.game_type = 27
OR tab.game_type = 28
OR tab.game_type = 30
OR tab.game_type = 31
THEN
tab.score_win / 10
WHEN tab.game_type = 29
THEN
tab.score_win / 1000
ELSE tab.score_win
END),0) AS scoreAll
FROM
friends_circle_game_record_card tab
WHERE
TO_DAYS(now()) - TO_DAYS( create_time ) = 0
GROUP BY
user_id
```

### 查禁止同桌记录里边，是否有2个玩家的记录

表名: friends_circle_forbid_intab
字段:user_id_1, user_id_2, user_id_3, user_id_4
4个字段用于存放最多限制4个玩家不能出现在同一个桌子内

思路是两个玩家ID, 3025,1261813 再四个字段里边出现的次数，如果出现 2次，则表示该条记录中同时存在两个玩家(前提是同一条记录，id不能重复)
```
SELECT count(*) as allCount from
(
SELECT
// 判断字段是否出现两个id，出现则为1
CASE WHEN user_id_1 = 3025 OR user_id_1 = 1261813 THEN 1 ELSE 0 END AS find_1,
CASE WHEN user_id_2 = 3025 OR user_id_2 = 1261813 THEN 1 ELSE 0 END AS find_2,
CASE WHEN user_id_3 = 3025 OR user_id_3 = 1261813 THEN 1 ELSE 0 END AS find_3,
CASE WHEN user_id_4 = 3025 OR user_id_4 = 1261813 THEN 1 ELSE 0 END AS find_4
FROM friends_circle_forbid_intab
) a
// 把四个字段判断的结果相加，看是否大于等于2
WHERE a.find_1 + a.find_2 + a.find_3 + a.find_4 >= 2;
```

### 接上条，根据一个玩家ID，清空记录里边该玩家的id字段，以及信息字段

技巧就是通过 CASE WHEN THEN 来实现根据字段进行条件判定，并同时进行update操作的效果
```
UPDATE friends_circle_forbid_intab set
user_info_1 = case when user_id_1 = 3024 then '' else user_info_1 end,
user_info_2 = case when user_id_2 = 3024 then '' else user_info_2 end,
user_info_3 = case when user_id_3 = 3024 then '' else user_info_3 end,
user_info_4 = case when user_id_4 = 3024 then '' else user_info_4 end,
user_id_1 = case when user_id_1 = 3024 then 0 else user_id_1 end,
user_id_2 = case when user_id_2 = 3024 then 0 else user_id_2 end,
user_id_3 = case when user_id_3 = 3024 then 0 else user_id_3 end,
user_id_4 = case when user_id_4 = 3024 then 0 else user_id_4 end
where user_id_1 = 3024 or user_id_2 = 3024 or user_id_3 = 3024 or user_id_4 = 3024
```

### 存在更新，不存在插入
ON DUPLICATE KEY
设计表的时候，设计好唯一字段，就可以将先判定记录是否存在，再插入/更新记录。变成一条sql完成所有

### 统计积分表，按总分排名次，并插入/更新到排行榜表
排行榜表:
tchl_game_score_rank
字段:
user_id, score, rank

分数表:
tchl_game_score
字段:
user_id, max_score

```
function createGameRank(callBack) {
callBack = callBack == null ? nop : callBack;

const sql = `INSERT INTO tchl_game_score_rank ( user_id, score, rank )
SELECT
a.user_id AS userId,
a.max_score AS score,
@rk := @rk + 1 AS rank
FROM
tchl_game_score a,
( SELECT @rk := 0 ) b
ORDER BY
a.max_score DESC,
a.user_id
ON DUPLICATE KEY UPDATE
user_id = VALUES( user_id ),
score = VALUES(score);`;

query(sql, (err, rows) => {
if (err) {
callBack(null);
} else {
callBack(rows.affectedRows > 0);
}
});
}
```

查看全文

http://www.lryc.cn/news/8644.html