首页 > 试题广场 >

数据源： 使用MySQL语言写出以下查

[问答题]
数据源：
使用MySQL语言写出以下查询算法步骤：
1、统计20150101当天各等级非挂机用户的消费总金额。
2、统计20150101各等级用户中，在次日（20150102）仍维持该等级用户的占比。
（注：不能使用本地IDE）
添加回答
爱在冬天晒太阳的懒虫头像 爱在冬天晒太阳的懒虫
select level,sum(payum) as amount from table1,table2,table3
where table1.uid=table3.uid and table3.uid != table2.uid
and dt='20150101'
group by level

发表于 2018-10-29 10:39:47 回复(0)
陈大莓🍓头像 陈大莓🍓
select a.level,sum(c.payum) 
from table1 a left join table3 c 
on a.uid =c.uid 
where dt='20150101' and a.uid not in 
(select uid from table2 where dt='20150101') 
group by level;
发表于 2020-12-25 23:01:09 回复(0)
ldd633头像 ldd633
(1)select sum(payum)
from table3
where uid not in (select uid from table2) and  dt='20150101'
(2)select count(if(if a.level =b.level),a.uid,null)/count(a.uid) as rate
from(select * from table1 where dt='20150101') as a left join (select * from table1 where dt='20150102') as b on a.uid =b.uid

编辑于 2019-03-04 14:21:53 回复(0)
张维云头像 张维云
(2) 因为是昨天level的复现率（今天level计数/昨天level计数），所以用昨天左连接今天，今天新出现的level不必考虑，而今天没有复现的level为null ，用when处理使复现率为0.
select a.level,(case when ( b.c/a.c )  is null then 0 else  ( b.c/a.c ) end) as ratio
from (select level,count(uid) as c  from table1 where dt='20150101' group by level)a
left join (select level,count(uid) as c  from table1 where dt='20150102' group by level)b
on a.level=b.level
发表于 2020-08-11 15:43:48 回复(0)
温暖的僵尸粉头像 温暖的僵尸粉
SELECT IFNULL(SUM(b.payum),0), a.level
FROM g_t1 a
LEFT JOIN g_t3 b
ON a.`uid`=b.`uid`
WHERE a.uid NOT IN (SELECT uid FROM g_t2 WHERE dt = '20150101')
AND a.dt = '20150101'
GROUP BY a.`level`
;

SELECT SUM(IF(a.level=b.level,1,0))/COUNT(b.uid)
FROM (SELECT uid,LEVEL FROM g_t1 WHERE dt='20150101') AS a
RIGHT JOIN (SELECT uid,LEVEL FROM g_t1 WHERE dt='20150102') AS b
ON a.uid =b.uid;
编辑于 2020-04-29 15:54:12 回复(1)
牛客959817854号头像 牛客959817854号
select t1.level,sum(t3.payum) from table1 t1 join table3 t3 on t1.uid = t3.uid where dt='20150101' and uid not in (select uid from table2) group by level
发表于 2020-02-26 20:45:32 回复(0)
单单单单单单头像 单单单单单单
1.select a.level,sum(c.payum)
  from (select * from table1 where dt = '20190101') a
 left join(select * from table2 where dt = '20190101' ) b
 on a.uid = b.uid
 left join(select uid,sum(payum) as payum from table3 where dt = '20190101' group by uid) c 
 on a.uid = c.uid
where b.uid is null
group by a.level

2.
select tmp.level,concat(round(tmp.number/tmp.total *100.00,2),"%") as percent
from
(select a.level,count(if(a.level=b.level),a.uid,null) as number,count(a.uid) as total
from(select * from table1 where dt='20150101') as a 
left join (select * from table1 where dt='20150102') as b
on a.uid =b.uid
group by a.level
)tmp


发表于 2019-08-19 14:54:40 回复(0)
爬行的小蜗牛201809020938333头像 爬行的小蜗牛201809020938333
SELECT SUM(payum)
FROM tables3
where uid not in(
SELECT uid
FROM     table2)
AND dt='20150101'
编辑于 2018-09-30 20:25:02 回复(0)
提交观点

问题信息

C++工程师 iOS工程师 安卓工程师 运维工程师 前端工程师 算法工程师 欢聚集团 2018 PHP工程师 运营 Java工程师
上传者：小小
难度：
8条回答 1661浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号