SQL检查何时配对不匹配 [英] SQL to check when pairs don't match

问题描述

我正在使用SQL Server 2012，我具有以下示例数据

I am using SQL Server 2012 I have the following sample data

Date        Type    Symbol      Price
6/30/1995   gaus    313586U72   109.25
6/30/1995   gbus    313586U72   108.94
6/30/1995   csus    NES         34.5
6/30/1995   lcus    NES         34.5
6/30/1995   lcus    NYN         40.25
6/30/1995   uaus    NYN         40.25
6/30/1995   agus    SRR         10.25
6/30/1995   lcus    SRR         0.45
7/1/1995    gaus    313586U72   109.25
7/1/1995    gbus    313586U72   108.94

我想过滤掉符号和价格匹配。类型不匹配也可以。因此，根据以上数据，我希望只能看到

I want to filter out when symbol and price match. It's ok if type doesn't match. Thus with the above data I would expect to only see

Date        Type    Symbol      Price
6/30/1995   gaus    313586U72   109.25
6/30/1995   gbus    313586U72   108.94
6/30/1995   agus    SRR         10.25
6/30/1995   lcus    SRR         0.45
7/1/1995    gaus    313586U72   109.25
7/1/1995    gbus    313586U72   108.94

由于其符号，NES和NYN已被滤除和价格匹配。

NES and NYN have been filtered out because their symbol and price matches.

我当时在考虑使用分区和行号，但是我不确定如何使用分区或行号来配对和过滤行。

I was thinking of using Partition and row number, but I am not sure how to pair and filter rows using that or another function.

* ** UPDATE 我将测试答复。我应该提到，我只想查看同一天出现的符号和价格重复项。该表也称为duppri

* **UPDATE I will be testing the replies. I should have mentioned I just want to see duplicates for symbol and price that occur on the same date. Also the table is called duppri

推荐答案

一种方法是使用 exists 带有相关子查询的谓词，该子查询检查特定符号的价格是否超过一个。

One way is to use the exists predicate with a correlated subquery that checks that the specific symbol have more than one price.:

select * from table1 t
where exists (
  select 1
  from table1
  where symbol = t.symbol
  and price <> t.price);

示例SQL小提琴

这将返回：

|                   Date | Type |    Symbol |  Price |
|------------------------|------|-----------|--------|
| June, 30 1995 02:00:00 | gaus | 313586U72 | 109.25 |
| June, 30 1995 02:00:00 | gbus | 313586U72 | 108.94 |
| June, 30 1995 02:00:00 | agus |       SRR |  10.25 |
| June, 30 1995 02:00:00 | lcus |       SRR |   0.45 |
| July, 01 1995 02:00:00 | gaus | 313586U72 | 109.25 |
| July, 01 1995 02:00:00 | gbus | 313586U72 | 108.94 |

编辑：受Gordon Linoffs启发，聪明的答案是使用 avg （）作为窗口函数：

inspiried by Gordon Linoffs clever answer another option could be to use avg() as a windowed function:

select Date, Type, Symbol, Price  
from (
  select Date, Type, Symbol, Price, avg = avg(price) over (partition by symbol) 
  from table1) a
where avg <> price;

编辑：带有检查以确保仅返回同一日期的重复项： http://www.sqlfiddle.com/#!6/29d67/1

with a check to ensure only duplicates on the same date are returned: http://www.sqlfiddle.com/#!6/29d67/1

这篇关于SQL检查何时配对不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！