SQL - Виберіть рядки, де один стовпець більше, ніж інший стовпець на ту ж дату

Скажімо, у мене є таблиця, значення , яка виглядає так:

id|field_id|value|date
1 |1       |2    |2013-06-01
2 |2       |5    |2013-06-01
3 |1       |3    |2013-06-02
4 |2       |9    |2013-06-02
5 |1       |6    |2013-06-03
6 |2       |4    |2013-06-03

І інша таблиця, поля , яка виглядає так

id|code
1 |small_value
2 |large_value

I would like to select the rows from values where small_value is larger than large_value on the same date. So for the example above, the query should return the last two rows from since 6, (field_id = 1 == small_value) > 4 (field_id = 2 == large_value).

База даних - Microsoft SQL Server 2012.

Дякуємо за будь-яку допомогу

2
Чи можете ви опублікувати повні дані таблиці полів. ?
додано Автор Vivek Sadh, джерело
поля - це просто ідентифікатор для відображення коду, з звичайними полями created_at і updated_at - чи є що-небудь спеціально додаткове, що ви хотіли б знати про таблиці?
додано Автор rwb, джерело
поля - це просто ідентифікатор для відображення коду, з звичайними полями created_at і updated_at - чи є що-небудь спеціально додаткове, що ви хотіли б знати про таблиці?
додано Автор rwb, джерело
@MarkBannister - ідеально, як один рядок для кожної дати, але мені було б цікаво побачити, якщо не надто багато зусиль, щоб змінити запит
додано Автор rwb, джерело
Бажаєте побачити результати як два окремих рядки для кожної дати збігу або як один рядок для кожної дати?
додано Автор Mark Bannister, джерело
Бажаєте побачити результати як два окремих рядки для кожної дати збігу або як один рядок для кожної дати?
додано Автор Mark Bannister, джерело

9 Відповіді

Як про щось подібне

SELECT * 
FROM [values] v
WHERE EXISTS(
  SELECT 1
  FROM [values] vl
  WHERE vl.FIELD_ID = 2
  AND vl.date = v.date
  AND vl.value < v.value
 )
AND v.FIELD_ID = 1

Демонстраційний скрипт SQL

Ось ще один можливий приклад

SELECT *
FROM [values] vs INNER JOIN
    [values] vl ON vs.date = vl.date AND vs.FIELD_ID = 1 AND vl.FIELD_ID = 2 
WHERE vs.value > vl.value

Демонстрація скриптів SQL

8
додано
Другий приклад досконалий. Дякую
додано Автор rwb, джерело

Як про щось подібне

SELECT * 
FROM [values] v
WHERE EXISTS(
  SELECT 1
  FROM [values] vl
  WHERE vl.FIELD_ID = 2
  AND vl.date = v.date
  AND vl.value < v.value
 )
AND v.FIELD_ID = 1

Демонстраційний скрипт SQL

Ось ще один можливий приклад

SELECT *
FROM [values] vs INNER JOIN
    [values] vl ON vs.date = vl.date AND vs.FIELD_ID = 1 AND vl.FIELD_ID = 2 
WHERE vs.value > vl.value

Демонстрація скриптів SQL

8
додано
Другий приклад досконалий. Дякую
додано Автор rwb, джерело

Подобається це, або я щось відсутній? :

    declare @tmp table (
     id int,
     field_id int,
     value int,
     [date] datetime
    )

    select * from @tmp
    where [date] in (
        select distinct t.[date]
        from @tmp t
        left join (select * from @tmp) as s on s.[Date] = t.[Date]
          and s.field_id = 1
        left join (select * from @tmp) as l on l.[Date] = t.[Date]
          and l.field_id = 2
         where s.value > l.value
    )
1
додано

Односторонній:

select [date],
       max(case field_id when 1 then [value] end) small_value,
       max(case field_id when 2 then [value] end) large_value
from [values]
group by [date]
having max(case field_id when 1 then [value] end) > 
       max(case field_id when 2 then [value] end)

SQLFiddle тут .

Крім того, щоб переглянути записи як окремі рядки, спробуйте:

select v1.*
from [values] v1
join [values] v2 
  on v1.[date] = v2.[date] and 
     v1.field_id = 3-v2.field_id and
     case v1.field_id when 1 then v1.[value] else v2.[value] end >
     case v1.field_id when 2 then v1.[value] else v2.[value] end

SQLFiddle тут

1
додано

Односторонній:

select [date],
       max(case field_id when 1 then [value] end) small_value,
       max(case field_id when 2 then [value] end) large_value
from [values]
group by [date]
having max(case field_id when 1 then [value] end) > 
       max(case field_id when 2 then [value] end)

SQLFiddle тут .

Крім того, щоб переглянути записи як окремі рядки, спробуйте:

select v1.*
from [values] v1
join [values] v2 
  on v1.[date] = v2.[date] and 
     v1.field_id = 3-v2.field_id and
     case v1.field_id when 1 then v1.[value] else v2.[value] end >
     case v1.field_id when 2 then v1.[value] else v2.[value] end

SQLFiddle тут

1
додано
SELECT 
  small.[Date]
, small.ID as SmallID
, small.Value as SmallValue
, large.ID as LargeID
, large.Value as LargeValue
FROM [values] small
INNER JOIN [values] large ON small.[Date] = large.[Date] AND small.field_id =1 AND large.field_id =2
WHERE small.Value > large.Value

Демо-версія SQLFiddle

1
додано
SELECT 
  small.[Date]
, small.ID as SmallID
, small.Value as SmallValue
, large.ID as LargeID
, large.Value as LargeValue
FROM [values] small
INNER JOIN [values] large ON small.[Date] = large.[Date] AND small.field_id =1 AND large.field_id =2
WHERE small.Value > large.Value

Демо-версія SQLFiddle

1
додано

Це зробить справу. Зверніть увагу на те, що я опублікував таблицю полів у відповіді, оскільки мені не потрібно використовувати fields.code для умов WHERE, але замість цього можна використовувати fields.id . Це те саме, що field_id у таблиці значень .

SELECT V.id, V.field_id, V.value, V.date
FROM [values] AS V 
  INNER JOIN [values] AS V1 
    ON V.date = V1.date
WHERE (((V.field_id)=1) AND ((V.value)>[V1].[value]) AND ((V1.field_id)=2)) 
   OR (((V.field_id)=2) AND ((V.value)<[V1].[value]) AND ((V1.field_id)=1))
ORDER BY V.id;

Або, якщо вам потрібна вся інформація в одному рядку:

SELECT V.id, V.field_id, V.value, V1.id, V1.field_id, V1.value, V.date
FROM [values] AS V INNER JOIN [values] AS V1 ON V.date = V1.date
WHERE (((V.field_id)=1) AND ((V.value)>[V1].[value]) AND ((V1.field_id)=2))
ORDER BY V.id;
0
додано

Це зробить справу. Зверніть увагу на те, що я опублікував таблицю полів у відповіді, оскільки мені не потрібно використовувати fields.code для умов WHERE, але замість цього можна використовувати fields.id . Це те саме, що field_id у таблиці значень .

SELECT V.id, V.field_id, V.value, V.date
FROM [values] AS V 
  INNER JOIN [values] AS V1 
    ON V.date = V1.date
WHERE (((V.field_id)=1) AND ((V.value)>[V1].[value]) AND ((V1.field_id)=2)) 
   OR (((V.field_id)=2) AND ((V.value)<[V1].[value]) AND ((V1.field_id)=1))
ORDER BY V.id;

Або, якщо вам потрібна вся інформація в одному рядку:

SELECT V.id, V.field_id, V.value, V1.id, V1.field_id, V1.value, V.date
FROM [values] AS V INNER JOIN [values] AS V1 ON V.date = V1.date
WHERE (((V.field_id)=1) AND ((V.value)>[V1].[value]) AND ((V1.field_id)=2))
ORDER BY V.id;
0
додано