mysql - Mysql 5.7 - 如何执行需要前一个子查询结果的多个子查询?
问题描述
希望有人可以帮助我解决我认为是子查询问题 - 我无法解决的问题。
我有一个包含用户推荐信息的表,推荐可以是 3 层。例如。
吉姆提到史蒂夫
瑞安提到吉姆
斯蒂芬妮提到瑞恩
马克提到斯蒂芬妮
作为更大查询的一部分,我试图做的是检索用户“史蒂夫”的推荐人列表所以理想情况下,查询返回“
- 吉姆
- 瑞安
- 斯蒂芬妮
- 标记
让您了解“user_referrals”表,如果史蒂夫是用户 ID 6211
--
mysql> select * from user_referrals where referee = '6211';
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| id | referrer | referee | created | modified | isActualReferrer | deleted | version |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| 4934 | 6155 | 6211 | 2021-02-28 04:22:35.798832 | 2021-02-28 04:22:35.798832 | 1 | NULL | 1 |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
1 row in set (0.00 sec)
mysql> select* from user_referrals where referee = '6155';
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| id | referrer | referee | created | modified | isActualReferrer | deleted | version |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| 4881 | 5757 | 6155 | 2021-02-25 17:44:42.103963 | 2021-02-25 17:44:42.103963 | 1 | NULL | 1 |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
1 row in set (0.00 sec)
mysql> select * from user_referrals where referee = '5757';
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| id | referrer | referee | created | modified | isActualReferrer | deleted | version |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| 4589 | 29 | 5757 | 2020-12-30 05:02:23.831882 | 2020-12-30 05:02:23.831882 | 1 | NULL | 1 |
+------+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
1 row in set (0.00 sec)
mysql> select * from user_referrals where referee = '29';
+----+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| id | referrer | referee | created | modified | isActualReferrer | deleted | version |
+----+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
| 7 | 24 | 29 | 2019-02-02 00:22:12.000000 | 2019-02-02 00:22:12.000000 | 1 | NULL | 1 |
+----+----------+---------+----------------------------+----------------------------+------------------+---------+---------+
1 row in set (0.01 sec)
主查询的子查询部分如下所示
LEFT JOIN
( select u1.referrer from user_referrals as u1 ) as u ON (u.id = u.referrer)
LEFT JOIN
( select u1.referrer from user_referrals as u1 ) as u3 ON (u3.referrer = (
select u2.referrer from user_referrals as u2 WHERE u.referrer = u2.referee)
)
我不认为这是处理它的最佳方法,在主查询中使用上述内容,到目前为止我得到的结果如下:
示例查询
select distinct(u.id),u.created,u.first_name,p.amount,u.referrer,u3.referrer from users as u
LEFT JOIN
( Select p1.* FROM paymentledger as p1 LEFT JOIN paymentledger as p2 ON p1.userID = p2.userID AND p1.createdAt > p2.createdAt WHERE p2.userID IS NULL) as p ON (u.id = p.userID)
LEFT JOIN
( select u1.referrer from user_referrals as u1 ) as u ON (u.id = u.referrer)
LEFT JOIN
( select u1.referrer from user_referrals as u1 ) as u3 ON (u3.referrer = (
select u2.referrer from user_referrals as u2 WHERE u.referrer = u2.referee)
)
WHERE u.created LIKE '2021-02-28%';
样本输出
+------+----------------------------+------------------+--------+----------+----------+
| id | created | first_name | amount | referrer | referrer |
+------+----------------------------+------------------+--------+----------+----------+
| 6202 | 2021-02-28 00:00:12.903938 | Brian | NULL | NULL | NULL |
| 6204 | 2021-02-28 00:44:52.317175 | Katie | NULL | NULL | NULL |
| 6205 | 2021-02-28 01:28:14.379496 | Stephanie | 159.00 | NULL | NULL |
| 6206 | 2021-02-28 01:32:45.655260 | Christina | NULL | NULL | NULL |
| 6207 | 2021-02-28 01:54:22.954110 | R | NULL | NULL | NULL |
| 6208 | 2021-02-28 02:45:01.010985 | Lillie | NULL | NULL | NULL |
| 6209 | 2021-02-28 02:53:10.819064 | Deborah | NULL | NULL | NULL |
| 6210 | 2021-02-28 04:05:24.937563 | Danielle | NULL | NULL | NULL |
| 6211 | 2021-02-28 04:22:35.103951 | Linda | 159.00 | 6211 | 6155 |
| 6212 | 2021-02-28 04:32:53.045521 | Kathy | NULL | NULL | NULL |
| 6213 | 2021-02-28 05:05:17.063022 | malekmuneerah786 | NULL | NULL | NULL |
| 6214 | 2021-02-28 07:47:12.725790 | James | NULL | NULL | NULL |
| 6215 | 2021-02-28 08:10:01.976589 | Wendy | NULL | NULL | NULL |
| 6216 | 2021-02-28 08:49:09.520211 | Barbara | NULL | NULL | NULL |
| 6217 | 2021-02-28 08:53:31.673631 | Tammy | NULL | NULL | NULL |
| 6218 | 2021-02-28 09:07:59.438093 | San | NULL | NULL | NULL |
| 6219 | 2021-02-28 09:30:26.966068 | Joan R | NULL | NULL | NULL |
| 6220 | 2021-02-28 14:58:11.305224 | Moses | NULL | NULL | NULL |
| 6221 | 2021-02-28 17:26:51.210201 | De Ana | NULL | NULL | NULL |
| 6222 | 2021-02-28 17:27:25.950142 | Faisal | NULL | NULL | NULL |
| 6223 | 2021-02-28 19:25:43.374723 | Pauline | NULL | NULL | NULL |
| 6224 | 2021-02-28 19:55:34.650488 | Jana | 159.00 | 6224 | 5757 |
| 6225 | 2021-02-28 20:13:23.158743 | Authentically | NULL | NULL | NULL |
| 6226 | 2021-02-28 20:17:04.178635 | J | NULL | NULL | NULL |
| 6227 | 2021-02-28 21:49:59.988693 | JACOBINA | NULL | NULL | NULL |
| 6228 | 2021-02-28 21:59:00.075468 | Jess | NULL | NULL | NULL |
| 6229 | 2021-02-28 22:55:16.655047 | Junie | NULL | NULL | NULL |
+------+----------------------------+------------------+--------+----------+----------+
可能只是我对加入感到困惑,希望有人可以在这里提供一些指导。
提前谢谢你。
- 根据要求 - 为 user_referrals 和示例输出/所需输出添加创建表。
CREATE TABLE `user_referrals` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`referrer` int(11) DEFAULT NULL,
`referee` int(11) DEFAULT NULL,
`created` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
`modified` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
`isActualReferrer` tinyint(4) NOT NULL DEFAULT '1',
`deleted` datetime(6) DEFAULT NULL,
`version` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
);
+------+----------------------------+------------------+--------+----------+----------+----------+----------+
| id | created | first_name | amount | referrer | referrer | referrer | referrer |
+------+----------------------------+------------------+--------+----------+----------+----------+----------+
| 6210 | 2021-02-28 04:05:24.937563 | Danielle | NULL | NULL | NULL | NULL | NULL |
| 6211 | 2021-02-28 04:22:35.103951 | Linda | 159.00 | 6211 | 6155 | 5757 | 29 |
| 6212 | 2021-02-28 04:32:53.045521 | Kathy | NULL | NULL | NULL | NULL | NULL |
+------+----------------------------+------------------+--------+----------+----------+----------+----------+
27 rows in set (0.04 sec)
这是user_referrals表的demo表,创建代码+插入demo数据
CREATE TABLE `user_referrals_demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`referrer` int(11) DEFAULT NULL,
`referee` int(11) DEFAULT NULL,
`created` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
`modified` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
`isActualReferrer` tinyint(4) NOT NULL DEFAULT '1',
`deleted` datetime(6) DEFAULT NULL,
`version` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
);
INSERT INTO user_referrals_demo(referrer,referee) VALUES ('6155','6211'),('5757','6155'),('29','5757'),('24','29');
输出应该是
用户名 | 推荐人 1 | 推荐人 2 | 推荐人 3 | 推荐人 4 6211 | 6155 | 5757 | 29 | 24
我正在尝试一个 sql 查询(我认为我需要一个子查询)来递归地遍历记录以提取所需的数据。
解决方案
mysql> select u3.referrer as referrer3,
-> u2.referrer as referrer2,
-> u1.referrer as referrer1
-> from user_referrals_demo as u1
->
-> left join user_referrals as u2 ON (u2.referee = u1.referrer)
-> left join user_referrals as u3 ON (u3.referee = u2.referrer)
-> left join user_referrals as u4 ON (u4.referee = u3.referrer)
->
-> where u1.referee = '6211';
上面的 sql 提供了所需的输出,通过这段代码,我能够将它用于父查询。
输出
+-----------+-----------+-----------+
| referrer3 | referrer2 | referrer1 |
+-----------+-----------+-----------+
| 29 | 5757 | 6155 |
+-----------+-----------+-----------+
感谢帮助我解决此问题的有关此问题的私人消息。
推荐阅读
- java - 有没有办法让 videoview 不是默认视图?
- flutter - Flutter Pubnub 版本解决失败
- javascript - 如何在Javascript中从另一个类调用方法函数
- pine-script - Pine 脚本:如何删除“输入‘交叉’时的语法错误”
- django - Django 错误:无法将类型日期转换为没有时区的时间
- python - 通过python中的名称获取数组条目?
- excel - 减去包含特定文本的行之间的空白行 - Excel
- python - Tkinter 没有正确接收 IP 字符串
- javascript - firebase.auth().onAuthStateChanged 在每次选项卡更改时调用
- javascript - 在上传到数据库之前显示预览图像