如何在SQL中为以下问题编写查询 [英] How to write query in SQL for below problem

问题描述

uniqueid visitid pregnancyNo progress
1        8       1           p1
2        8       1           p2
3        8       2           p3
4        9       1           p4
5        9       1           p5
6        9       3           p6

不，我想要的结果如

No I want result like

<pre lang="C#">
uniqueid visitid pregnancyNo progress
3        8       2           p3
4        9       1           p4
5        9       1           p5
6        9       3           p6
</pre>

这意味着同样的怀孕不会有较低的访问ID。但怀孕2的访问次数较少，但上次访问次数不是9次，所以就采取了这种措施。

怀孕3在访问9中是全新的，所以很明显这一点。

只是怀孕1被删除，因为它出现在访问9中。



我尝试过：



我无法尝试任何原因导致我无法编写此问题的算法。

That means same pregnancyNo having lower visit id will not be taken. But pregnancy 2 has lower visit but that is not in upper visit 9,so its taken.
And pregnancy 3 is full new in visit 9,so its obviously taken.
Just pregnancy 1 is deleted because it is present in visit 9.

What I have tried:

I could not try anything cause I'm not able to write the algorithm of this problem.

推荐答案

我已将您的记录插入 tmp_table 表格，并以两种方式达到了预期的结果：





查询1：

I have inserted your records in "tmp_table" table, and achieved the desired result in two ways :


Query 1:

select a.*
from tmp_table a, (select pregnancyno,max(visitid) as visitid from tmp_table group by pregnancyno) b
where a.pregnancyno=b.pregnancyno and a.visitid=b.visitid
order by a.uniqueid;

查询2：

Query 2:

select a.* from tmp_table a where a.visitid in 
(select max(visitid) from tmp_table b where a.pregnancyno=b.pregnancyno)
order by a.uniqueid;

享受!!

Enjoy !!

使用 MAX（）并阅读： MySQL的 - 如何在SQL中用另一列选择MAX（列值），DISTINCT的行？ - 堆栈溢出 [ ^ ]

这篇关于如何在SQL中为以下问题编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！