Birlikdan foydalangandan so'ng SQL qatorlarni satrlarga ajratish

Mening stolim

+-----------+---------+
|    Date   |  Letter |
+-----------+---------+
| 13.02.2013|    B    |
| 01.03.2016|    A    |
| 28.12.2003|    C    |
| 12.01.2017|    B    |
| 25.04.2011|    A    |
+-----------+---------+

To'g'ri ma'lumotlarni qaytaradigan so'rovlar yaratdim, lekin kutilganidek emas:

SELECT * from
(
SELECT TOP 1 Date as Date1, Letter as Letter1 from TAB where
Letter = 'A'
order by Date DESC
) TAB
UNION
SELECT * from
(
SELECT TOP 1 Date as Date2, Letter as Letter2 from TAB where 
Letter = 'B'
order by Datum DESC
) TAB

Kutilgan natijalar:

+-----------+---------+-----------+---------+
|    Date1  | Letter1 |    Date2  | Letter2 |
+-----------+---------+-----------+---------+
| 01.03.2016|    A    | 12.01.2017|    B    |
+-----------+---------+-----------+---------+

Chiqish:

+-----------+---------+
|    Date1  | Letter1 |
+-----------+---------+
| 01.03.2016|    A    | 
| 12.01.2017|    B    |
+-----------+---------+

ULARNING yordamida kutilgan barcha 4 qatorni olish mumkinmi?

Rahmat.

1
Qaysi DDB dan foydalanasiz?
qo'shib qo'ydi muallif a_horse_with_no_name, manba
Bu yordam berishi mumkin deb o'ylayman. stackoverflow .com/questions/15931607 /; hellip;
qo'shib qo'ydi muallif Tajinder, manba

6 javoblar

To'g'ri tushunsam, "A" lar va "B" larning ustunlar ro'yxatida bo'lishini xohlaysiz. Bu, albatta, munosabatlarning chiqishi emas, chunki navbatdagi ustunlar bir-biriga aloqasi yo'q. Biroq, siz shartli to'plash yordamida buni qilishingiz mumkin: Top 1 Sana1 deb nomlang, Letter1 sifatida yozing

select max(case when letter = 'A' then date end) as date1,
       'A' as letter1,
       max(case when letter = 'B' then date end) as date2,
       'B' as letter2
from (select t.*,
             row_number() over (partition by letter order by date desc) as seqnum
      from t
      where letter in ('A', 'B')
     ) t
group by seqnum
order by seqnum;
1
qo'shib qo'ydi
Salom Gordon. Katta rahmat. Sizning echimlaringiz yaxshi ishlaydi. Men unga ID qo'shib qo'ydim va kutganimni olaman.
qo'shib qo'ydi muallif user2463808, manba

Muammoni to'g'ri tushunib umid qilaman.

So'rovni tekshirib ko'ring.

select Date1 , Letter1 , Date2 , Letter2
from
(SELECT TOP 1 Date as Date1, Letter as Letter1 from TAB where
Letter = 'A'
order by Date DESC) a join 
(
SELECT TOP 1 Date as Date2, Letter as Letter2 from TAB where 
Letter = 'B'
order by Date DESC
) b;
0
qo'shib qo'ydi

Agar siz bir qatorda natija kerak bo'lsa, birlashma (birlashma emas), masalan:

SELECT T1.Date, T1.Letter, T2.date, T2.Letter 
from  (
    SELECT TOP 1 '1' as id,  Date , Letter 
    from TAB 
    where Letter = 'A'
    order by Date DESC
) T1
INNER JOIN ( 
  SELECT TOP 1  '1' as id, Date , Letter  
  from TAB 
  where   Letter = 'B'
  order by Datum DESC
) T2 on T1.id = T2.id 
0
qo'shib qo'ydi

This is not what UNION does. It simply combines the given two data sets into one, as you can see in your example. You could try to use a CASE like in this answer: SQL - Query same column twice with different dates in where clause

0
qo'shib qo'ydi

You can use an join instead of union all for the two sub queries and call the columns from both the sub queries using alias

0
qo'shib qo'ydi

ikkinchi so'rovni (1-sana va 1-harf kabi) o'zgartirishga urinib, ustunlar bir xil bo'lishi kerak

0
qo'shib qo'ydi