Bir necha satrlarga bir nechta batafsil turlarini biriktirish

Ro'yxat ma'lumotlariga oid ma'lumotlar to'plamim bor.

Ro'yxatning batafsil ro'yxati bor. Har bir shaxs uchun ID # raqami va ushbu ID # uchun tafsilotlar soni 50 ga yaqin. PK ID #, batafsil tur va sana.

Tafsilotlargacha qanday turdagi narsalar yo'qolganligini aniqlash uchun roster batafsil jadvalidagi har bir satrga qarshi pastki so'rov natijasiga qo'shilishga harakat qilaman.

Istagan natijalarga individual shaxs bilan erishish mumkin:

select *
from (

    -- a big sub-query yielding 39 lines of the detail types I want

) list_of_details
left join textDetail td
    on td.detailType = list_of_details.detailType
    and td.sid_member = 2071
order by dt.sid_detailType

Yutuqlar:

detailType     ID          detailType     date_start              value_detail
-------------- ----------- -------------- ---------------------------------
1              2071        1              2017-03-14 00:00:00.000 test
1              2071        1              2017-03-14 16:58:50.037 NULL
2              2071        2              2017-03-14 00:00:00.000 test
2              2071        2              2017-03-14 16:58:50.037 NULL
3              NULL        NULL           NULL                    NULL
4              2071        4              2017-03-14 16:58:50.037 NULL
16             NULL        NULL           NULL                    NULL
17             2071        17             2017-03-14 16:58:50.037 NULL
18             2071        18             2017-03-14 16:58:50.037 NULL
19             2071        19             2017-03-14 16:58:50.037 NULL
20             2071        20             2017-03-14 16:58:50.037 NULL
21             2071        21             2017-03-14 16:58:50.037 NULL
22             2071        22             2017-03-14 16:58:50.037 NULL
23             2071        23             2017-03-14 16:58:50.037 NULL
24             2071        24             2017-03-14 16:58:50.037 NULL
25             2071        25             2017-03-14 16:58:50.037 NULL
27             2071        27             2017-03-14 16:58:50.037 NULL
28             2071        28             2017-03-14 16:58:50.037 NULL
33             NULL        NULL           NULL                    NULL
34             NULL        NULL           NULL                    NULL
35             2071        35             2017-03-14 16:58:50.037 NULL
36             2071        36             2017-03-14 16:58:50.037 NULL
37             2071        37             2017-03-14 16:58:50.037 NULL
38             2071        38             2017-03-14 16:58:50.037 NULL
39             2071        39             2017-03-14 16:58:50.037 NULL
40             2071        40             2017-03-14 16:58:50.037 NULL
41             2071        41             2017-03-14 16:58:50.037 NULL
42             2071        42             2017-03-14 16:58:50.037 NULL
46             2071        46             2017-03-14 16:58:50.037 NULL
47             2071        47             2017-03-14 16:58:50.037 NULL
48             2071        48             2017-03-14 16:58:50.037 NULL
51             2071        51             2017-03-14 16:58:50.037 NULL
52             2071        52             2017-03-14 16:58:50.037 NULL
53             2071        53             2017-03-14 16:58:50.037 NULL
54             2071        54             2017-03-14 16:58:50.037 NULL
55             2071        55             2017-03-14 16:58:50.037 NULL
56             2071        56             2017-03-14 16:58:50.037 NULL
57             2071        57             2017-03-14 16:58:50.037 NULL
58             NULL        NULL           NULL                    NULL

value_detail ustunidan NULL haqida tashvishlanmayman, bu sinov foydalanuvchisi.

Ushbu so'rovni bir necha yuz foydalanuvchilarga qaytarishim kerak va ularning qaysi yozuvlari etishmayotganini topib olishim kerak. Masalan, yuqorida ko'rsatilgan shaxs detailType 3, 16, 33, 34, 58 yo'qolgan

- EITI

va td.sid_member = 2071 olib tashlash, faqat mos keladigan satrlarni beradi. Bunday holda, sid_member 2071 uchun natijalar detailType tashkil qilinadigan satrlarni istisno qilmaydi.

qaerda td.sid_member null bo'lsa qo'shilmaydi

0
Quyidagi javobni yangilab oldim. ID va batafsil toifaning asosiy ro'yxatiga ega bo'lishingiz kerak. So'ngra, har bir noyob identifikator uchun sizning etishmayotgan tafsilotlaringizni topish uchun ushbu ro'yxatga qo'shilishingiz mumkin.
qo'shib qo'ydi muallif abraxascarab, manba
Quyidagi javobni yangilab oldim. ID va batafsil toifaning asosiy ro'yxatiga ega bo'lishingiz kerak. So'ngra, har bir noyob identifikator uchun sizning etishmayotgan tafsilotlaringizni topish uchun ushbu ro'yxatga qo'shilishingiz mumkin.
qo'shib qo'ydi muallif abraxascarab, manba

6 javoblar

Mavjud so'rovingizda ushbu qatorni olib tashlang yoki o'zgartiring:

 and td.sid_member = 2071
0
qo'shib qo'ydi

Mavjud so'rovingizda ushbu qatorni olib tashlang yoki o'zgartiring:

 and td.sid_member = 2071
0
qo'shib qo'ydi

Mavjud so'rovingizda ushbu qatorni olib tashlang yoki o'zgartiring:

 and td.sid_member = 2071
0
qo'shib qo'ydi

Siz deyarli u erda ... Siz faqat NULL matnini tekshirishingiz kerak.

left join textDetail td
    on td.detailType = list_of_details.detailType
WHERE td.sid_member IS NULL
order by dt.sid_detailType

Bu, asosan, sizning barcha bo'lmagan narsalarni beradi.

* EDIT * tugmasini bosing

OK ... siz bizga stol tuzilmangizni bermagani bois, bu haqda yana bir bor o'ylab qoldim ...

Agar siz ID raqamingizni etishmayotgan tafsilot turlari bilan birgalikda istasangiz, cross-a'zolikni ishlatib, barcha identifikator turlari bilan identifikatorning asosiy ro'yxatini yaratishingiz kerak. Shundan so'ng siz ushbu asosiy ro'yxatga qo'shilishingiz va noyob identifikator bo'yicha kam ma'lumot turlarini olishingiz mumkin.

SELECT xid.ID, xid.detailType
FROM
   (SELECT i.ID, d.detailType
   FROM
   (SELECT DISTINCT ID FROM textDetail) as i
   cross join (SELECT DISTINCT detailType FROM list_of_details) as d
   ) as xid
left outer join textDetail td
    on xid.ID = td.ID
    AND xid.detailType = td.detailType
WHERE td.detailType IS NULL
ORDER BY xid.ID, xid.detailType

Bu sizning hisobingizga tegishli bo'lmagan identifikatorlarni beradi

0
qo'shib qo'ydi

Siz deyarli u erda ... Siz faqat NULL matnini tekshirishingiz kerak.

left join textDetail td
    on td.detailType = list_of_details.detailType
WHERE td.sid_member IS NULL
order by dt.sid_detailType

Bu, asosan, sizning barcha bo'lmagan narsalarni beradi.

* EDIT * tugmasini bosing

OK ... siz bizga stol tuzilmangizni bermagani bois, bu haqda yana bir bor o'ylab qoldim ...

Agar siz ID raqamingizni etishmayotgan tafsilot turlari bilan birgalikda istasangiz, cross-a'zolikni ishlatib, barcha identifikator turlari bilan identifikatorning asosiy ro'yxatini yaratishingiz kerak. Shundan so'ng siz ushbu asosiy ro'yxatga qo'shilishingiz va noyob identifikator bo'yicha kam ma'lumot turlarini olishingiz mumkin.

SELECT xid.ID, xid.detailType
FROM
   (SELECT i.ID, d.detailType
   FROM
   (SELECT DISTINCT ID FROM textDetail) as i
   cross join (SELECT DISTINCT detailType FROM list_of_details) as d
   ) as xid
left outer join textDetail td
    on xid.ID = td.ID
    AND xid.detailType = td.detailType
WHERE td.detailType IS NULL
ORDER BY xid.ID, xid.detailType

Bu sizning hisobingizga tegishli bo'lmagan identifikatorlarni beradi

0
qo'shib qo'ydi

Siz deyarli u erda ... Siz faqat NULL matnini tekshirishingiz kerak.

left join textDetail td
    on td.detailType = list_of_details.detailType
WHERE td.sid_member IS NULL
order by dt.sid_detailType

Bu, asosan, sizning barcha bo'lmagan narsalarni beradi.

* EDIT * tugmasini bosing

OK ... siz bizga stol tuzilmangizni bermagani bois, bu haqda yana bir bor o'ylab qoldim ...

Agar siz ID raqamingizni etishmayotgan tafsilot turlari bilan birgalikda istasangiz, cross-a'zolikni ishlatib, barcha identifikator turlari bilan identifikatorning asosiy ro'yxatini yaratishingiz kerak. Shundan so'ng siz ushbu asosiy ro'yxatga qo'shilishingiz va noyob identifikator bo'yicha kam ma'lumot turlarini olishingiz mumkin.

SELECT xid.ID, xid.detailType
FROM
   (SELECT i.ID, d.detailType
   FROM
   (SELECT DISTINCT ID FROM textDetail) as i
   cross join (SELECT DISTINCT detailType FROM list_of_details) as d
   ) as xid
left outer join textDetail td
    on xid.ID = td.ID
    AND xid.detailType = td.detailType
WHERE td.detailType IS NULL
ORDER BY xid.ID, xid.detailType

Bu sizning hisobingizga tegishli bo'lmagan identifikatorlarni beradi

0
qo'shib qo'ydi