如何在SQL中获取唯一数据
如何在SQL中获取唯一数据
提问于 2019-04-01 01:48:50
我已经尝试过distinct，但它在我的情况下不起作用。
我有一个包含数据的表格：
qincId ID lc1 lc2 Time SP
------------------------------------------------------------------------
963 544 22.3000526428 73.1743087769 2019-03-31 17:00:46.000 15
965 544 22.2998828888 73.1746368408 2019-03-31 17:01:07.000 2
968 544 22.2998828888 73.1746368408 2019-03-31 17:01:40.000 2
997 544 22.3010215759 73.1744003296 2019-03-31 17:06:11.000 15
998 544 22.3011436462 73.1747131348 2019-03-31 17:06:21.000 17
1010 544 22.3034667969 73.1747512817 2019-03-31 17:08:04.000 0
1011 544 22.3032741547 73.1747512817 2019-03-31 17:08:03.000 0
1012 544 22.3032741547 73.1747512817 2019-03-31 17:08:04.000 0
1028 544 22.3032741547 73.1747512817 2019-03-31 17:11:04.000 0
1563 544 22.3032741547 73.1747512817 2019-03-31 18:45:27.000 0
1564 544 22.3032741547 73.1747512817 2019-03-31 18:45:28.000 0
1565 544 22.3032035828 73.1748123169 2019-03-31 18:45:26.000 0
1567 544 22.3032035828 73.1748123169 2019-03-31 18:45:28.000 0
1571 544 22.3028964996 73.1748123169 2019-03-31 18:46:03.000 16
1573 544 22.3023796082 73.1747131348 2019-03-31 18:46:21.000 15
1575 544 22.3021774292 73.1746444702 2019-03-31 18:46:37.000 0
1577 544 22.3019657135 73.1747665405 2019-03-31 18:46:50.000 15
1586 544 22.3009243011 73.1742477417 2019-03-31 18:47:33.000 5
1591 544 22.2998828888 73.1745300293 2019-03-31 18:48:19.000 5
1592 544 22.2998828888 73.1745300293 2019-03-31 18:48:28.000 5
1593 544 22.2998981476 73.1746063232 2019-03-31 18:48:29.000 4
1597 544 22.3000450134 73.1744232178 2019-03-31 18:49:08.000 0
1611 544 22.3000450134 73.1744232178 2019-03-31 18:51:28.000 0
1616 544 22.3000450134 73.1744232178 2019-03-31 18:52:22.000 0
1677 544 22.3000450134 73.1744232178 2019-03-31 19:03:28.000 0现在我希望所有记录都有一个sp > 0，第一个记录有一个具有相同lc1和lc2的speed = 0。
所以基本上我不想要其lc1和lc2与sp =0重复的数据
上述记录的预期输出：
qincId ID lc1 lc2 Time SP
-------------------------------------------------------------------------
963 544 22.3000526428 73.1743087769 2019-03-31 17:00:46.000 15
965 544 22.2998828888 73.1746368408 2019-03-31 17:01:07.000 2
968 544 22.2998828888 73.1746368408 2019-03-31 17:01:40.000 2
997 544 22.3010215759 73.1744003296 2019-03-31 17:06:11.000 15
998 544 22.3011436462 73.1747131348 2019-03-31 17:06:21.000 17
1010 544 22.3034667969 73.1747512817 2019-03-31 17:08:04.000 0
1011 544 22.3032741547 73.1747512817 2019-03-31 17:08:03.000 0
1565 544 22.3032035828 73.1748123169 2019-03-31 18:45:26.000 0
1571 544 22.3028964996 73.1748123169 2019-03-31 18:46:03.000 16
1573 544 22.3023796082 73.1747131348 2019-03-31 18:46:21.000 15
1575 544 22.3021774292 73.1746444702 2019-03-31 18:46:37.000 0
1577 544 22.3019657135 73.1747665405 2019-03-31 18:46:50.000 15
1586 544 22.3009243011 73.1742477417 2019-03-31 18:47:33.000 5
1591 544 22.2998828888 73.1745300293 2019-03-31 18:48:19.000 5
1592 544 22.2998828888 73.1745300293 2019-03-31 18:48:28.000 5
1593 544 22.2998981476 73.1746063232 2019-03-31 18:48:29.000 4
1597 544 22.3000450134 73.1744232178 2019-03-31 18:49:08.000 0
1677 544 22.3000450134 73.1744232178 2019-03-31 19:03:28.000 0我也尝试过distinct和group by，但我无法获得输出。
我怎样才能得到我期望的输出？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-04-01 01:55:20
尝试使用联合all和row_number()，如下所示
select qincId,ID,lc1,lc2,time,sp from
(select qincId,ID,lc1,lc2,time,sp,
row_number()over(partition by lc1,lc2 order by time) rn
from table_name where sp>0
) t where t.rn=1
union all
select qincId,ID,lc1,lc2,time,sp from
(
select *,row_number()over(partition by lc1,lc2 order by time ) rn
from table_name where sp=0
) a where a.rn=1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55443734
复制相关文章
相似问题