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; # 如果价格有多条相同的情况

后续还会更新,谢谢各们亲关注!

posted @ 2019-09-05 11:27  FcLwYf  阅读(146)  评论(0编辑  收藏  举报