Sql case when 小例

SELECT I.uname,

C.consume,

O.name,O.dis_count,O.memberType,

D.name,D.dis_count,D.up,D.down,

 

CASE

WHEN D.dis_count IS NULL THEN O.dis_count

ELSE D.dis_count

END AS discount

 

FROM (

sdb_membermanage_members AS I

LEFT JOIN sdb_membermanage_moneyanddiscount AS C

ON I.member_id = C.member_id

)

LEFT JOIN (

sdb_membermanage_member_lv AS O

LEFT JOIN sdb_membermanage_lv_detail AS D

-- using (member_lv_id)

ON O.member_lv_id = D.member_lv_id

)

ON I.member_lv_id = O.member_lv_id

 

WHERE O.memberType = '1'

OR (O.memberType = '0'

AND C.consume >= D.down

AND C.consume < D.up

)

 

解释一下, 目的是 根据会员id 查询出会员的折扣.

涉及到的表: 会员表,会员消费表,会员等级表,会员等级详情表

会员等级表有两种类型的会员，自定义会员和等级会员．会员等级详情

会员等级表里的等级会员是一整套的会员方案 类似于京东的金牌银牌,淘宝的钻石皇冠(即,金牌会员 和银牌会员 的会员等级id相同,具体区别在会员等级详情表里)

会员表里存了　会员消费表表id, 会员等级id

如果是等级会员,则还要继续查询等级会员详情表,根据消费金额计算出消费区间属于哪个等级详情,取出折扣.