1.用SQL生成有序对非常简单。像下面这样通过交叉连接生成笛卡尔积,就可以得到有序对。
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2;
2.为了去掉(苹果,苹果)这种由相同元素构成的对,需要像下面这样加上一个条件,然后再进行连接运算。
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name <> P2.name;
#在以后多用’<>‘,戒掉’=!‘
3.进一步对(苹果,橘子)和(橘子,苹果)这样只是调换了元素顺序的对进行去重。
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;
#’>‘此处利用首字母
- 补充
- Leetcode习题:180题:连续出现的数字
查找表中至少连续出现三次的数字。
select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2, Logs l3
where
l1.Id = l2.Id-1 #合并规则,按照id+1来合并
and l2.Id = l3.Id-1
and l1.Num = l2.Num #筛选规则
and l2.Num = l3.Num
- 公众号:数据管道
每天的活跃用户数和活跃30天留存用户数
select
A.date,A.日活跃用户数,B.活跃30天留存用户数
from (
select date,count(*) 日活跃用户数
from active
group by date) A
left join (
select a1.date,count(a1.user_id) 活跃30天留存用户数
from active a1,active a2
where a1.user_id=a2.user_id and Date(a1.date)+29<a2.date
group by a1.date) B
on A.date=B.date;
还是有问题,输出的是null不是0