在我们的数据库中,每个客户端最多可以有3个电话号码,每个电话号码都与一个ID_phone_number、一个ID_type_phone和id_person相关联。
所以我需要做一个通用的选择,它返回的电话号码的id_type_phone = '2‘,并具有与此id_person相关的较高的id_phone_number。
这个select需要对我的整个数据库起作用,所以我不能在上面指定id_person
我有:
SELECT
(SELECT collun1.phone WHERE collun1.ID_type_phone = '2')
FROM collun1
WHERE id_person = 4
我想要像这样的东西
SELECT
(SELECT collun1.phone WHERE collun1.ID_type_phone = '2' and max(id_phone_number)
FROM collun1
发布于 2019-03-26 07:15:39
可以将SQL Server的Rank函数与PARTITION和ORDER BY一起使用
就像这样
Select * from (
Select RANK() OVER (
PARTITION BY id_person
ORDER BY id_phone_number desc) AS phoneRank
,*
from collun1
WHERE collun1.ID_type_phone = '2') as tempSub
where phoneRank = 1
发布于 2019-03-26 07:00:52
使用以下语法:
SELECT id_person, phone
FROM collun1
WHERE ID_type_phone = '2' AND id_phone_number = (SELECT MAX(id_phone_number) FROM collun1 c WHERE c.id_person = collun1.id_person)
发布于 2019-03-26 07:03:19
GROUP BY
可能会更快(取决于您的数据):
SELECT id_person, MAX(ID_phone_number)
FROM collun1
WHERE ID_type_phone = '2'
GROUP BY id_person
https://stackoverflow.com/questions/55347533
复制相似问题