mysql - MySQL,获取多行集合中一行的位置
问题描述
我有这个查询来计算 apublication
在一组出版物(这里命名为community
)中的位置,根据那里effective_publishing_date
:
SELECT p.publication_id
, p.name publication_name
, IF(p.scheduled_at is not null, p.scheduled_at, p.created_at) effective_publishing_date
, @current_rank := @current_rank + 1 publication_rank
FROM publications p
JOIN (SELECT @current_rank := 0) r
WHERE p.community_id = 8513
ORDER
BY effective_publishing_date ASC;
结果为:
[![在此处输入图像描述][1]][1]
现在我有一个列表,feed_item
其中包含 eachcommunity_id
和publication_id
as 属性,我想为每个 获取feed_item
关联的publication_rank
.
例如,如果我有一个publication_item
with publication_id
= 18 和community_id
= 2,我想要#2的所有出版物中的 # publication_rank
18 。我没有成功在一个查询(或子查询等)中得到它。publication_id
community_id
感谢提前,
解决方案
这是我最终找到的两个解决方案。感谢@Barmar!
- 仅使用连接:
SELECT
g1.community_id,
g1.publication_name,
g1.publication_name,
g1.publication_id,
COUNT(*) AS rank
FROM
(
SELECT
publications.publication_id as publication_id,
publications.name as publication_name,
publications.community_id as community_id,
communities.name as community_name,
IF(
publications.scheduled_at is not null,
publications.scheduled_at, publications.created_at
) as effective_publishing_date
FROM
feed_items
JOIN publications ON feed_items.publication_id = publications.publication_id
JOIN communities ON publications.community_id = communities.community_id
WHERE
feed_items.user_id = 489387
) AS g1
JOIN (
SELECT
publications.publication_id as publication_id,
publications.community_id as community_id,
IF(
publications.scheduled_at is not null,
publications.scheduled_at, publications.created_at
) as effective_publishing_date
FROM
feed_items
JOIN publications ON feed_items.publication_id = publications.publication_id
WHERE
feed_items.user_id = 489387
) AS g2 ON (
g2.effective_publishing_date, g2.publication_id
) <= (
g1.effective_publishing_date, g1.publication_id
)
AND g1.community_id = g2.community_id
GROUP BY
g1.publication_id,
g1.community_id,
g1.effective_publishing_date
ORDER BY
g1.community_id,
rank ASC;
- 使用 SQL 变量
SELECT data_table.publication_id, data_table.publication_name, data_table.community_id, data_table.effective_publishing_date,
@publication := IF(@community <> data_table.community_id, concat(left(@community := data_table.community_id, 0), 0), @publication+1) AS rank
FROM
(SELECT @publication:= -1) p,
(SELECT @community:= -1) c,
(SELECT
publication.name as publication_name,
publication.community_id as community_id,
feed_item.publication_id as publication_id,
IF(publication.scheduled_at is not null, publication.scheduled_at, publication.created_at) as effective_publishing_date
FROM feed_items feed_item
JOIN publications publication ON feed_item.publication_id = publication.publication_id
WHERE feed_item.user_id = 489387
ORDER BY publication.community_id, effective_publishing_date ASC
) data_table;
推荐阅读
- node.js - 为什么我的 mern 应用无法部署到 Heroku?
- javascript - js打开txt文件逐行读取并转为数组
- python - groupby Pandas 限制行数
- c - 这两个环境变量 (a) environ b) envp) 的实际字符串存储在哪里?
- prometheus - Prometheus:0 偏移计数器
- python - 根据 csv 文件中的列表发送具有不同附件的多封电子邮件
- sql - SQL Presto - 星期天的星期功能
- reactjs - 热门反应问题:如何使用 docker-compose 在 Azure 中运行为 reactjs 应用程序提供服务的 docker 容器?
- python - 使用“平均”方法重新采样数据
- node.js - 无法在节点 js 中使用“aes-256-cbc”算法解密 pdf 文件