比较复杂的sql

朱雀 2022-06-09 03:16 291阅读 0赞

select from\_base64(o.nick\_name) from activity\_win\_record o left join activity\_win\_recipient p  
on o.id = p.win\_id

SELECT  
o.nick\_name,  
o.prize\_name,  
o.win\_amount,  
o.create\_date,  
o.odd\_num,  
p. NAME,  
p.address,  
p.phone

FROM  
activity\_win\_record o  
LEFT JOIN activity\_win\_recipient p ON o.id = p.win\_id

SELECT

o.nick\_name,o.prize\_name,  
o.win\_amount,  
o.create\_date,  
o.odd\_num,  
p. NAME,  
p.address,  
p.phone,  
(  
CASE o.STATUS  
WHEN 01 THEN ‘待领取’  
WHEN 02 THEN ‘待发放’  
WHEN 04 THEN ‘已发放’  
WHEN 05 THEN ‘已领取’  
WHEN 06 THEN ‘已退款’  
WHEN 07 THEN ‘已锁定’  
END  
) AS status,  
(  
CASE o.prize\_type  
WHEN ‘QUITA\_RED’ THEN ‘定额红包’  
WHEN ‘RANDOM\_RED’ THEN ‘随机红包’  
WHEN ‘INTEGRAL’ THEN ‘积分’  
WHEN ‘MATERIAL’ THEN ‘实物’  
WHEN ‘FORCARTEX’ THEN ‘福卡’  
WHEN ‘THANKS’ THEN ‘谢谢’  
END  
) AS prize\_type  
FROM (SELECT CONCAT(FROM\_BASE64(nick\_name),” “) AS nick\_name,id,prize\_name,win\_amount,create\_date,odd\_num,status,prize\_type,activity\_id FROM activity\_win\_record WHERE o.`activity_id`=’25e1178432d94984ac05a27561061ffd’) o  
LEFT JOIN activity\_win\_recipient p ON o.id = p.win\_id  
WHERE o.`activity_id`=’25e1178432d94984ac05a27561061ffd’

SELECT o.nick\_name,o.prize\_name,  
o.win\_amount,  
o.create\_date,  
o.odd\_num,  
p. NAME,  
p.address,  
p.phone,  
(  
CASE o.STATUS  
WHEN 01 THEN ‘待领取’  
WHEN 02 THEN ‘待发放’  
WHEN 04 THEN ‘已发放’  
WHEN 05 THEN ‘已领取’  
WHEN 06 THEN ‘已退款’  
WHEN 07 THEN ‘已锁定’  
END  
) AS STATUS,(  
CASE o.prize\_type  
WHEN ‘QUITA\_RED’ THEN ‘定额红包’  
WHEN ‘RANDOM\_RED’ THEN ‘随机红包’  
WHEN ‘INTEGRAL’ THEN ‘积分’  
WHEN ‘MATERIAL’ THEN ‘实物’  
WHEN ‘FORCARTEX’ THEN ‘福卡’  
WHEN ‘THANKS’ THEN ‘谢谢’  
END  
) AS prize\_type  
FROM (SELECT CONCAT(FROM\_BASE64(nick\_name)) AS nick\_name,id,prize\_name,win\_amount,create\_date,odd\_num,STATUS,prize\_type,activity\_id FROM activity\_win\_record ) o  
LEFT JOIN activity\_win\_recipient p ON o.id = p.win\_id WHERE o.`activity_id`=’25e1178432d94984ac05a27561061ffd’

SELECT  
o.nick\_name,o.prize\_name,  
(  
CASE o.prize\_type  
WHEN ‘QUITA\_RED’ THEN truncate(o.win\_amount/100,2)  
WHEN ‘RANDOM\_RED’ THEN truncate(o.win\_amount/100,2)  
else o.win\_amount  
END  
) AS win\_amount,  
– truncate(o.win\_amount/100,2) as win\_amount,  
o.create\_date,  
o.odd\_num,  
p. NAME,  
p.address,  
p.phone,  
(  
CASE o.STATUS  
WHEN ‘01’ THEN ‘待领取’  
WHEN ‘02’ THEN ‘待发放’  
WHEN ‘04’ THEN ‘已发放’  
WHEN ‘05’ THEN ‘已领取’  
WHEN ‘06’ THEN ‘已退款’  
WHEN ‘07’ THEN ‘已锁定’  
END  
) AS STATUS,(  
CASE o.prize\_type  
WHEN ‘QUITA\_RED’ THEN ‘定额红包’  
WHEN ‘RANDOM\_RED’ THEN ‘随机红包’  
WHEN ‘INTEGRAL’ THEN ‘积分’  
WHEN ‘MATERIAL’ THEN ‘实物’  
WHEN ‘FORCARTEX’ THEN ‘福卡’  
WHEN ‘THANKS’ THEN ‘谢谢’  
END  
) AS prize\_type  
FROM (SELECT CONCAT(FROM\_BASE64(nick\_name)) AS nick\_name,id,prize\_name,win\_amount,create\_date,odd\_num,STATUS,prize\_type,activity\_id FROM activity\_win\_record ) o  
LEFT JOIN activity\_win\_recipient p ON o.id = p.win\_id WHERE o.`activity_id`=’80ea078faba341e58ac915adcf5a4143’

select \* from user\_activity\_prize where activity\_id in (select id from user\_activity\_base where company\_id = ‘80ea078faba341e58ac915adcf5a4143’)