SQL Server Select top 2 different foreign keys

kq0g1dla 于 2023-08-02 发布在其他

关注(0)|答案(4)|浏览(152)

PhoneBill table

Id, Number,   PersonId, Bill
---------------------------
1,  123456789,  1001,    50
2,  123456789,  1001,    38
3,  123456789,  1002,    77

It's a Phone-Bill table, there are columns for phone number, the phone belonged to whom (PersonId), per-month charge (Bill).

I want to do a check if a certain phone belonged to more than 1 person. I know a way:

SELECT COUNT(PersonId) 
FROM PhoneBill 
WHERE Number = 'xxx' 
GROUP BY PersonId

But this is low performance, since it would look for all of the PersonId s. I just need to know if there are more than 1 person use this phone.

sql-server

来源：https://stackoverflow.com/questions/76650342/select-top-2-different-foreign-keys

4条答案

按热度按时间

wqnecbli1#

Can you pl check this? Here you are not grouping first for all personid's so it needs less compute. Also, I am sure that postgres allows this syntax, but I am not sure about sql-server though.

SELECT COUNT(DISTINCT PersonId)
FROM PhoneBill
WHERE Number = 'xxx'
HAVING COUNT(DISTINCT PersonId) > 1

赞(0）回复(0）举报 2023-08-02

epfja78i2#

I highly doubt that the GROUP BY query would hve a significant performance impact, unless there were a very, very large number of rows for that Number value.

But you can also do this using TOP (2) WITH TIES , which will give you all tied results for the first two PersonId values.

SELECT
  CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END AS HasDuplicates
FROM (
    SELECT TOP (2) WITH TIES
      pb.Id
    FROM PhoneBill pb
    WHERE pb.Number = 'xxx'
    ORDER BY
      pb.Number,
      pb.PersonId
) pb;

赞(0）回复(0）举报 2023-08-02

lymnna713#

SELECT DISTINCT TOP 2 PersonId FROM PhoneBill WHERE Number = '123456789'

Stupid I was, that's quite a simple solution.

赞(0）回复(0）举报 2023-08-02

qjp7pelc4#

SELECT TOP 2 Id 
FROM PhoneBill a LEFT JOIN PhoneBill b 
ON a.Number = b.Number AND a.PersonId != b.PersonId 
WHERE a.Number = 'xxx'

You can join the table itself on same number but different PersonId, then check top 2 different ids.

Edit:

Considering Charlieface's suggestion, after some meditation, I think moving the condition a.Number = 'xxx' to JOIN ON clause could be better. Actually just need TOP 1 to know there are different Person own a same phone.

SELECT TOP 1 a.Id, b.Id
FROM PhoneBill a LEFT JOIN PhoneBill b 
ON a.Number = b.Number AND a.PersonId != b.PersonId AND a.Number = 'xxx'
--WHERE a.Number = 'xxx'

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Select top 2 different foreign keys

4条答案

相关问题

热门标签

最新问答