sql获取分组后的第⼆条数据groupbylimit
做互联⽹⾦融1年多了，每天各种统计sql的写，算投资、算留存、算转化。。。。
今天运营同事有个需求要求获取⼀段时间内每个⽤户的⾸投、复投，⾸投好算，直接groupby就是第⼀条投资，可获取复投的时候被整蒙圈了，掉到group by的坑⾥了，特写个笔记祭奠下此坑。。。
获取每个⽤户复投的sql
SELECT a.* FROM loan_investor a WHERE a.id = (SELECT id FROM loan_investor WHERE investorUserId = a.investorUserId LIMIT 1,1) ORDER BY a.investorUserId
感谢这位哥们⼉给的启发，谢谢！
下边的sql不⽤看，纯属只是做个记录
u.userId,
u.nickName "昵称",
u.idCardNo "⾝份证号",
IFNULL(u.source, "") "来源",
DATE_FORMAT(groupby分组
"%Y-%m-%d %H:%i:%S"
) AS 注册时间,
IFNULL(up.openTime, "") "开户时间",
IFNULL(li.investTime, "") "⾸笔投资时间",
IFNULL(li.investAmount, "") "⾸笔投资⾦额",
count(lii.id) "投资次数",
IFNULL(ur.ZCZ,0) "总充值",
IFNULL(l.ZTZ, 0) "总投资",
IFNULL(us.ZTX, 0) "总提现",
u.cash "可⽤余额",
IFNULL(touzi.sanshitian, 0) "30天项⽬投资次数",
IFNULL(touzi.sanshitiantouzi, 0) "30天项⽬投资⾦额",
IFNULL(touzi.liushitian, 0) "60天项⽬投资次数",
IFNULL(touzi.liushitiantouzi, 0) "60天项⽬投资⾦额",
IFNULL(touzi.jiushitiantouzi, 0) "90天项⽬投资⾦额",
IFNULL(touzi.yibaibashitian, 0) "180天项⽬投资次数",
IFNULL(
touzi.yibaibashitiantouzi,
)
"180天项⽬投资⾦额",
IFNULL(touzi.sanbailiushitian, 0) "360天项⽬投资次数",
IFNULL(
touzi.sanbailiushitiantouzi,
) "360天项⽬投资⾦额",
IFNULL(g.utm_source, "") AS utm_source,
IFNULL(g.utm_medium,"") AS utm_medium,
IFNULL(g.utm_term,"") AS utm_term,
IFNULL(g.utm_content,"") AS utm_content,
IFNULL(g.utm_campaign,"") AS utm_campaign,
IFNULL(g.fromUrl,"") AS fromUrl
FROM
user_mainAS u
LEFT JOIN user_register_pnr_tempup ON up.userId = u.userId
LEFT JOIN (
SELECT
userId,
SUM(amount)AS ZCZ
FROM
recharge_log
WHERE
flag= 1
GROUPBY
userId
) AS ur ON u.userId = ur.userId
userId,
SUM(amount)AS ZTX
FROM
cash_withdraw_request
WHERE
pnrStatus= 1
GROUPBY
userId
)
AS us ON u.userId = us.userId LEFT JOIN (
SELECT
investorUserId,
SUM(investAmount)AS ZTZ
FROM
loan_investor
WHERE
pnrStatus= 1
GROUPBY
investorUserId
) AS l ON u.userId =l.investorUserId LEFT JOIN (
SELECT
*
FROM
loan_investori
WHERE
i.pnrStatus= 1
GROUPBY
i.investorUserId
) AS li ON u.userId =li.investorUserId LEFT JOIN (
SELECT
loan_investorli
WHERE
li.pnrStatus= 1
) AS lii ON u.userId =lii.investorUserId
LEFT JOIN googleanalytics g ONu.userId = g.regNickName LEFT JOIN (
SELECT
sum(
CASE
WHENtermCount >= 1
ANDtermCount <= 30 THEN
1
ELSE
END
)AS sanshitian,
sum(
CASE
WHENtermCount >= 1
ANDtermCount <= 30 THEN
b.investAmount
ELSE
END
)AS sanshitiantouzi,
sum(
CASE
WHENtermCount > 30
ANDtermCount <= 60 THEN
1
ELSE
)AS liushitian,
sum(
CASE
WHENtermCount > 30
ANDtermCount <= 60 THEN              b.investAmount
ELSE
END
)AS liushitiantouzi,
sum(
CASE
WHENtermCount > 60
ANDtermCount <= 90 THEN              1
ELSE
END
)AS jiushitian,
sum(
CASE
WHENtermCount > 60
ANDtermCount <= 90 THEN              b.investAmount
ELSE
END
)AS jiushitiantouzi,
sum(
CASE
WHENtermCount > 90
ANDtermCount <= 180 THEN

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。