如何在SQL表中的2列中获取不同的值 [英] How to get distinct values in 2 Column in a SQL Table

问题描述

表1

Table 1

  APPID  | CERTID       | 	CERTNAME
--------------------------------
11111    |	1	|	ABCD	
22222    |	2	|	EFGH
33333    |	3	|	IJKL
11111    |	4	|	XYZ
44444    |	5	|	JHJ
22222    |	6	|	KJHK
11111    |	7	|	TUHJ

注意： - 此处证书是PK且不可重复，因为App Id是可重复的列值



在此表格中如何获得 AppId 和相应的 certId ，



这是我的查询，这里仍然是我的重复AppId的

Note:- here cert is is PK and its not repeatable where as App Id is repeatable columns values

In this table How do I get distinct AppId and corresponding certId ,

This is my query , here still I am getting duplicate AppId's

Select distinct(APPID),CERTID from Table1

请大家帮忙。



代码块添加 - OriginalGriff [/ edit]

Please Please Can any one help me.

[edit]Code block added - OriginalGriff[/edit]

推荐答案

试试这个：

Try this:

SELECT APPID, CERTID
FROM (SELECT APPID, CERTID,
             ROW_NUMBER() OVER (PARTITION BY APPID ORDER BY APPID) AS RowNumber
      FROM   Table1) AS a
WHERE   a.RowNumber = 1

对于每个不同的APPID，有不同的CERTID，对于APPID 11111，有三个CERTID 1,4,7。你必须拍照其中一个，否则APPID会重复。



根据你的要求，我认为这对你有用。

For each distinct APPID there are Different CERTID, like for APPID 11111 there are three CERTID 1,4,7. You have to pic one of them otherwise APPID will repeat.

As per your requirement I think this will work for you.

select APPID, MAX(CERTID) from Table1
group by APPID

Select distinct APPID,CERTID 
from Table1
group by APPID,CERTID 

这篇关于如何在SQL表中的2列中获取不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！