我如何获得所有具有多个不同地址的唯一ID的唯一ID [英] How can i get all the unique id having more than one id with different address

问题描述

id id2  id3 address 
1   1   0   A   
2   1   0   B
3   2   0   A
4   3   0   A
5   3   0   B
6   4   0   A   

大家好...请解决我的查询...我有这种格式的表格.现在，我想从此表中唯一地获取值"id2".这意味着我希望所有具有地址"B"和"A"的值(但"A"不应与B具有相同的"id2").因此，我将获得"id2"的所有唯一值....伙计们...做点什么

Hello every one ... plz solve my query... i have this table in this format. Now i want to get value from this table accordingly uniquely "id2" . It means I want all the values having address "B" along with "A"(but "A" should not have same "id2" as to B). Thus I'll get all the unique values of "id2"....plz guys...do something

当前我正在使用查询获取"B"

currently I'm using the query for getting "B"

 SELECT * FROM t1 WHERE address=(SELECT MAX(address) FROM t1)

现在我也想要所有的"A"，但它不应该与"B"具有相同的"id2"

now also i want all the "A" but it should not have same "id2" with "B"

我想要它的答案

id id2  id3 address 

2   1   0   B
3   2   0   A

5   3   0   B
6   4   0   A   

任何想法?

推荐答案

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  id2, MAX(ID) max_ID
            FROM    tableName
            GROUP   BY id2
        ) b ON  a.id2 = b.id2 aND
                a.ID = b.max_ID

• SQLFiddle演示
• SQLFiddle Demo

这篇关于我如何获得所有具有多个不同地址的唯一ID的唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！