MySQL，获取多行集合中一行的位置
我有这个查询，根据那里计算出publication一组出版物（在这里称为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其中每个属性都有community_id和publication_id属性，并且我想为每个属性获取feed_item关联的publication_rank。
举例来说，如果我有一个publication_item具有publication_id= 18 community_id= 2，我想要publication_rank的publication_id＃18的所有出版物中的community_id＃2。我在一个查询（或子查询等）中无法获得成功。
谢谢你提前
问题来源于stack overflow
写回答
-
这是我终于找到的两个解决方案。感谢@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;
2019-11-18 10:05:56赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
