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

### 批量插入

```
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);
        }
    });
}
```