mysql操作遇到的坑（第二版）
1、通过条件查询出上一条与下一条
sql说明：本表关联本表，然后通过其中一个表，查询出对应的条件，再用另外一个表求出上一条与下一条的数据，求出来的数据是多条的
SELECT
ua.id,
ua.wx_pages,
ua.wx_pages_source,
uaa.id as uaa_id,
uaa.wx_pages,
uaa.wx_pages_source
FROM
ds_user_action AS ua
JOIN ds_user_action AS uaa ON ua.user_id = uaa.user_id // 用会员ID作为关联条件，两张表出来的数据是不同的，其中一张表，专门根据条件用来查询，另外一个没有加条件的表，则用来求上一条与一条数据，切记，不要用主键ID
WHERE
uaa.wx_pages LIKE '%shopping/checkout%'
AND uaa.wx_pages_source LIKE '%goods/goods%'
AND uaa.user_id = 179135
AND ua.id = (
SELECT
max(id)
FROM
ds_user_action as uaaa
WHERE
uaaa.id < uaa.id
AND uaaa.user_id = 179135
)
2、CASE WHEN 判断条件 THEN 值 ELSE 被动值 END 的结果，判断条件排序
（1）ELSE用NULL,count(NULL) == 0 , count(0) == 1
SELECT
ld.*, ldt.order_state,
ldt.uid,
count(
CASE
WHEN (ldt.order_state = 1) && (ldt.uid = 179135) THEN
1
ELSE
NULL
END
) AS onum
FROM
ds_lucky_draw AS ld
LEFT JOIN ds_lucky_deposit AS ldt ON ld.ld_id = ldt.lid
WHERE
ld.`status` = 1
AND ld.lottery_time >= 1564109614
GROUP BY
ld.ld_id
ORDER BY
onum DESC,
ld.lottery_time DESC
（2）这个直接就用的case判断
SELECT
ld.*, ldt.order_state,
g.goods_name,
ldt.uid,
CASE
WHEN (ldt.order_state = 1) && (ldt.uid = '.$user_id.') THEN
1
WHEN ld.start_time <= 1564625127 && ld.lottery_time > 1564625127 THEN
2
WHEN ld.start_time >= 1564625127 THEN
3
ELSE
0
END onum
FROM
ds_lucky_draw AS ld
LEFT JOIN ds_lucky_deposit AS ldt ON ld.ld_id = ldt.lid
LEFT JOIN ds_goods AS g ON ld.goods_id = g.goods_id
WHERE
ld.`status` = 1
AND ld.lottery_time >= 1564625127
GROUP BY
ld.ld_id
ORDER BY
onum DESC,
ld.sort DESC;
3、求出max与本条纪录字段
SELECT
lba_id,
price,
add_time
FROM
ds_live_broadcast_auction AS lba_a
WHERE
lba_a.price = (
SELECT
max(price)
FROM
ds_live_broadcast_auction AS lba_b
ORDER BY add_time asc
)
limit 1; # 如果价格有多条相同的情况
后续还会更新，谢谢各们亲关注！
