SQL Server:返回具有多个不同值的行

时间：2022-05-22 13:03:11

I've been working on this issue for the last day and a half and just can't seem to find another question on here that works for my code.

我已经在这个问题上做了最后一天半的工作了，在这里我似乎找不到另一个对我的代码有用的问题。

I have a table here:

我这里有一张桌子:

Table_D

Table_D

Policynumber|   EntryDate   |  BI_Limit   | P remium
------------------------------------------------------
ABCD100001  |   5/1/16      |   15/30     | 919
ABCD100001  |   5/13/16     |   15/30     | 1008 
ABCD100002  |   5/24/16     |   100/300   | 1380
ABCD100003  |   5/30/16     |   25/50     | 1452
ABCD100003  |   6/2/16      |   25/50     | 1372
ABCD100003  |   6/4/16      |   30/60     | 951
ABCD100004  |   6/11/16     |   100/300   | 1038
ABCD100005  |   6/22/16     |   100/300   | 1333
ABCD100005  |   7/2/16      |   50/100    | 1208
ABCD100006  |   7/10/16     |   250/500   | 1345
ABCD100007  |   7/18/16     |   15/30     | 996

in which I'm trying to extract rows in which a policynumber has multiple listings and a different BI_Limit. So the output should be:

在其中，我尝试提取包含多个列表和不同BI_Limit的保单编号的行。所以输出应该是:

Output

输出

Policynumber    | EntryDate  |  BI_Limit  | Premium
---------------------------------------------------
ABCD100003      | 5/30/16    |  25/50     | 1452
ABCD100003      | 6/2/16     |  25/50     | 1372
ABCD100003      | 6/4/16     |  30/60     | 951
ABCD100005      | 6/22/16    |  100/300   | 1333
ABCD100005      | 7/2/16     |  50/100    | 1208

I'm storing Policynumber as VARCHAR(Max), EntryDate as DATE, BI_Limit as VARCHAR(Max), and Premium as INTEGER.

我将Policynumber存储为VARCHAR(Max)， EntryDate存储为DATE, BI_Limit存储为VARCHAR(Max)， Premium存储为INTEGER。

The code I've want to say should work would be something along the lines of:

我想说的代码应该是:

    SELECT * FROM Table_D
    WHERE BI_Limit IN (
        SELECT BI_Limit
        FROM Table_D
        GROUP BY BI_Limit
        HAVING COUNT(DISTINCT BI_Limit)>1);

But this returns nothing for me. Can anyone help to show me what I'm doing wrong? Thank you.

但这对我来说毫无回报。有人能帮我指出我做错了什么吗?谢谢你！

2 个解决方案

#1

3  

You could also try exists

你也可以尝试存在

select a.*
from Table_D a
where
    exists (
        select 1
        from Table_D b
        where a.Policynumber = b.Policynumber
              and a.BI_Limit <> b.BI_Limit
    )

#2

0  

SELECT d.*
FROM   (   -- find the policy number with multiple listing and diff BI_Limit
           SELECT PolicyNumber
           FROM   TableD
           GROUP BY PolicyNumber
           HAVING   count(*) > 1
           AND      MIN (BI_Limit) <> MAX (BI_Limit)
       ) m -- join back the Table_D to for other information
       INNER JOIN Table_D d 
       ON  m.PolicyNumber = d.PolicyNumber

#1

3  

You could also try exists

你也可以尝试存在

select a.*
from Table_D a
where
    exists (
        select 1
        from Table_D b
        where a.Policynumber = b.Policynumber
              and a.BI_Limit <> b.BI_Limit
    )

#2

0  

SELECT d.*
FROM   (   -- find the policy number with multiple listing and diff BI_Limit
           SELECT PolicyNumber
           FROM   TableD
           GROUP BY PolicyNumber
           HAVING   count(*) > 1
           AND      MIN (BI_Limit) <> MAX (BI_Limit)
       ) m -- join back the Table_D to for other information
       INNER JOIN Table_D d 
       ON  m.PolicyNumber = d.PolicyNumber
标签：

相关文章