Avatar billede cjep Nybegynder
28. januar 2009 - 20:32 Der er 13 kommentarer og
1 løsning

Sammenlægning af 2 SQL kald

Hejsa,

Jeg bruger et CMS system med et forum, hvor jeg ønsker at vise de 25 seneste indlæg. Da forum'et sondrer mellem THREADS og REPLY/QUOTES har jeg behov for at sammensætte listen af seneste REPLY/QUOTES + nyeste THREADS hvorpå der endnu ikke er replies.

Det har jeg sådan set løst i 2 separate SQL kald, men jeg vil selvfølgelig gerne kunne gøre det hele i et hug. Resultatet er alstå en blanding af replies og nye threads.

Tabellen væsentligste felter er disse:

subject_id    (integer)
parent_id    (integer)
subject            (tekst)
message            (tekst)
posted_by    (tekst)
posted_date    (dato)
type            (T, R, Q) ..hhv Thread/Reply/Quote
category    (THREAD, REPLY, QUOTE)
page_id        (integer; altid = 111)

SQL 1: Her trækkes seneste indlæg af enten typen Reply eller Quote, dvs. laves niveau i Forum>Tråd>Indlæg:
---------------------------------------------------------------
SELECT    TOP (25) t1.parent_id AS topic_id, t1.subject, ISNULL(t2.posted_date, t1.posted_date) AS last_post_date, ISNULL(t3.subject, t1.subject) AS forum_subject,
                      ISNULL(t3.category, t1.category) AS forum_category, t1.message, t1.posted_by, t1.posted_date, t1.type, t3.parent_id
FROM        discussion AS t1 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (type IN ('R', 'Q')) AND (page_id = 111)
                            GROUP BY parent_id) AS t2 ON t1.subject_id = t2.topic_id LEFT OUTER JOIN
                      discussion AS t3 ON t1.parent_id = t3.subject_id
WHERE    (t1.type IN ('R', 'Q')) AND (t1.page_id = 111)
ORDER BY last_post_date DESC


SQL 2: Her findes alle de THREADS der er oprettet, hvorpå der ikke er Reply eller Quotes; dvs. det er et niveau højere: FORUM>TRÅD. De skal naturligvis kun tages med, hvis det datomæssigt er relevante i top 25 sammenholdt med SQL1.
--------------------------------------------------------------
SELECT    t4.subject_id AS [t4.subject_id], t4.subject AS [t4.subject], t4.message AS [t4.message], t4.parent_id AS [t4.parent_id], t4.type AS [t4.type],
                      t4.category AS [t4.category], t5.topic_id
FROM        discussion AS t4 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (page_id = 111)
                            GROUP BY parent_id) AS t5 ON t4.subject_id = t5.topic_id
WHERE    (t4.type IN ('T', 'R', 'Q')) AND (t4.page_id = 111) AND (t5.topic_id <> t4.parent_id)



Kan nogle give et bud på 1 samlet SQL query, der kan levere top 25 af hhv. 'reply/quotes' og 'threads uden reply/quotes' sorteret efter last_post_date ???
Avatar billede arne_v Ekspert
28. januar 2009 - 23:02 #1
prøv:

SELECT TOP 25
FROM ((SELECT ...) UNION (SELECT ...)) x
ORDER BY last_post_date
Avatar billede cjep Nybegynder
29. januar 2009 - 19:55 #2
Så kommer SQL'en til at se sådan ud, men jeg får en syntax fejl 'near ORDER BY' ... nogen ide hvorfor?

SELECT TOP(25) *
FROM
(
(SELECT  t1.parent_id AS topic_id, t1.subject, ISNULL(t2.posted_date, t1.posted_date) AS last_post_date, ISNULL(t3.subject, t1.subject) AS forum_subject,
                      ISNULL(t3.category, t1.category) AS forum_category, t1.message, t1.posted_by, t1.posted_date, t1.type, t3.parent_id
FROM        discussion AS t1 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (type IN ('R', 'Q')) AND (page_id = 111)
                            GROUP BY parent_id) AS t2 ON t1.subject_id = t2.topic_id LEFT OUTER JOIN
                      discussion AS t3 ON t1.parent_id = t3.subject_id
WHERE    (t1.type IN ('R', 'Q')) AND (t1.page_id = 111)
)

UNION
(SELECT    t4.subject_id AS [t4.subject_id], t4.subject AS [t4.subject], t4.message AS [t4.message], t4.parent_id AS [t4.parent_id], t4.type AS [t4.type],
                      t4.category AS [t4.category], t5.topic_id
FROM        discussion AS t4 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (page_id = 111)
                            GROUP BY parent_id) AS t5 ON t4.subject_id = t5.topic_id
WHERE    (t4.type IN ('T', 'R', 'Q')) AND (t4.page_id = 111) AND (t5.topic_id <> t4.parent_id)
)

)
ORDER BY posted_date
Avatar billede arne_v Ekspert
29. januar 2009 - 20:04 #3
glemte du ikke x'et ?
Avatar billede berglund Nybegynder
29. januar 2009 - 20:15 #4
hej cjep

Du skal passe på med at sætte de rigtige paranteser. Det kan let blive uoverskueligt. Jeg har fjernet nogle fra den første SQL-sætning og formateret koden lidt så det er lettere at læse.

Overvej om det er nødvendigt at sætte paranteserne (se i dine WHERE-sætninger). Det bliver hurtigt svært at læse:


SELECT
    TOP(25) *
FROM
            (
                SELECT 
                    t1.parent_id AS topic_id,
                    t1.subject,
                    ISNULL(t2.posted_date,
                    t1.posted_date) AS last_post_date,
                    ISNULL(t3.subject, t1.subject) AS forum_subject,
                    ISNULL(t3.category, t1.category) AS forum_category,
                    t1.message,
                    t1.posted_by,
                    t1.posted_date,
                    t1.type,
                    t3.parent_id
                    FROM  discussion AS t1 LEFT OUTER JOIN
                          (
                                SELECT   
                                    parent_id AS topic_id,
                                    MAX(posted_date) AS posted_date
                                FROM 
                                    discussion
                                WHERE
                                      (type IN ('R', 'Q')) AND (page_id = 111)
                                GROUP BY parent_id
                            ) AS t2
                            ON t1.subject_id = t2.topic_id
                      LEFT OUTER JOIN discussion AS t3
                      ON t1.parent_id = t3.subject_id
            WHERE    (t1.type IN ('R', 'Q')) AND (t1.page_id = 111)
) AS TTT
UNION
SELECT   
        t4.subject_id AS [t4.subject_id],
        t4.subject AS [t4.subject],
        t4.message AS [t4.message],
        t4.parent_id AS [t4.parent_id],
        t4.type AS [t4.type],
        t4.category AS [t4.category],
        t5.topic_id
FROM   
        discussion AS t4 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (page_id = 111)
                            GROUP BY parent_id
                          ) AS t5
                            ON t4.subject_id = t5.topic_id
WHERE   
    (t4.type IN ('T', 'R', 'Q'))
    AND (t4.page_id = 111)
    AND (t5.topic_id <> t4.parent_id)
ORDER BY posted_date
Avatar billede cjep Nybegynder
29. januar 2009 - 20:53 #5
Argh, er der næsten. Har to problemer.

1) Vil gerne SELECT'e message, men den brokker sig over at man ikke kan lave DISTINCT eller UNION på et ntext felt. (i eksemplet har jeg fjernet 'message' for at isolere anden fejl).

2) Nu får jeg et svar, men kolonnen 'forum_parent_id' viser forkerte data i forhold til tabellen. Fornemmer at jeg får overskrevet værdien fra t1 i t4 eller noget i den stil. Kan nogle af jer se hvad der går galt?

SQL
----
SELECT top(9999)
    topic_id, subject, forum_last_post_date, forum_subject, forum_posted_by, forum_posted_date, forum_type, forum_parent_id
FROM
            (
                SELECT 
                    t1.parent_id AS topic_id,
                    t1.subject,
                    ISNULL(t2.posted_date,
                    t1.posted_date) AS forum_last_post_date,
                    ISNULL(t3.subject, t1.subject) AS forum_subject,
                    t1.posted_by as forum_posted_by,
                    t1.posted_date as forum_posted_date,
                    t1.type as forum_type,
                    t3.parent_id as forum_parent_id
                    FROM  discussion AS t1 LEFT OUTER JOIN
                          (
                                SELECT   
                                    parent_id AS topic_id,
                                    MAX(posted_date) AS posted_date
                                FROM 
                                    discussion
                                WHERE
                                      (type IN ('R', 'Q')) AND (page_id = 111)
                                GROUP BY parent_id
                            ) AS t2
                            ON t1.subject_id = t2.topic_id
                      LEFT OUTER JOIN discussion AS t3
                      ON t1.parent_id = t3.subject_id
            WHERE    (t1.type IN ('R', 'Q')) AND (t1.page_id = 111)
) AS TTT
UNION
SELECT   
        t4.subject_id AS topic_id,
        t4.subject AS subject,
        t4.posted_date AS last_posted_date,
        t4.subject AS forum_subject,
        t4.posted_by as forum_posted_by,
        t4.posted_date as forum_posted_date,
        t4.type as forum_type,
        t4.parent_id as forum_parent_id


FROM   
        discussion AS t4 LEFT OUTER JOIN
                          (SELECT    parent_id AS topic_id, MAX(posted_date) AS posted_date
                            FROM          discussion
                            WHERE      (page_id = 111)
                            GROUP BY parent_id
                          ) AS t5
                            ON t4.subject_id = t5.topic_id
WHERE   
    (t4.type IN ('T', 'R', 'Q'))
    AND (t4.page_id = 111)
    AND (t5.topic_id <> t4.parent_id)
ORDER BY forum_last_post_date


Resultat
--------

topic_id, subject, forum_last_post_date, forum_subject, forum_posted_by, forum_posted_date, forum_posted_date, forum_type, forum_parent_id

19    SV:Velkommen    2009-01-12 16:01:18.527    Velkommen    0150    2009-01-12 16:01:18.527    R    18
19    SV:Velkommen    2009-01-12 16:49:49.873    Velkommen    0186    2009-01-12 16:49:49.873    R    18
34    Medlemslisten    2009-01-12 16:55:05.043    Medlemslisten    0186    2009-01-12 16:55:05.043    T    18
34    SV:Medlemslisten    2009-01-12 16:59:33.780    Medlemslisten    0099    2009-01-12 16:59:33.780    R    18
36    Medlems Galleriet    2009-01-13 16:34:57.123    Medlems Galleriet    0099    2009-01-13 16:34:57.123    T    18
37    Sodavands ansvarlig???    2009-01-14 10:44:10.497    Sodavands ansvarlig???    0194    2009-01-14 10:44:10.497    T    6
20    SV:Fejl på sitet    2009-01-14 15:22:06.950    Fejl på sitet    0099    2009-01-14 15:22:06.950    R    18
20    SV:Fejl på sitet    2009-01-14 15:28:44.547    Fejl på sitet    0185    2009-01-14 15:28:44.547    R    18
37    SV:Sodavands ansvarlig???    2009-01-15 02:19:32.700    Sodavands ansvarlig???    0095    2009-01-15 02:19:32.700    R    6
19    SV:Velkommen    2009-01-15 02:35:14.233    Velkommen    0095    2009-01-15 02:35:14.233    Q    18
Avatar billede cjep Nybegynder
29. januar 2009 - 20:57 #6
og topic_id bliver tydeligvis også overskrevet.

Her er eksempel fra tabellen:
subject_id, parent_id, subject,

20    18    Fejl på sitet
60    20    SV:Fejl på sitet
58    20    SV:Fejl på sitet
59    20    SV:Fejl på sitet
Avatar billede berglund Nybegynder
29. januar 2009 - 22:00 #7
Ad 1) Prøv at CAST'e message til et NVARCHAR(MAX) eller andet passende. Skal du alligevel kun bruge noget af teksten, brug SUBSTRING og CAST til relevant længde. Det vil også give mindre data i den sidste ende.

Ad 2) sørg for at ALLE de kolonner du nævner i den første SELECT-sætning er foranstillet med korrekt alias jf. den tabel som du rent faktisk ønsker at hente data fra. Dvs.:

SELECT top(9999)
    TTT.topic_id, TTT.subject, TTT.forum_last_post ... etc.

3) tabellen discussion optræder både med og uden alias. Sørg for at ALLE instancer af discussion efterfølges af alias, og brug det alias i dine SELECT-sætninger. Så er du helt sikker på at du "peger" på det som du ønsker. Løser det ikke noget, kan du så starte med fejlfinding derfra.

/thomas
Avatar billede janus_007 Nybegynder
29. januar 2009 - 22:46 #8
Du skal hellere være glad for at du kan trække det ud af 2 gange, set udfra performance og serverload vil små hurtige db-forespørgsler altid være at foretrækkke fremfor store og lange.

DB-serverlicenser er dyre, så jo mere man kan trække ud og klare i businesslogiclayer dg stadig med hensynstagen til datamængde, vil være at foretrække!!

(En teoretisk DB-purist vil sikkert ikke være enig med mig *GG*)
Avatar billede berglund Nybegynder
30. januar 2009 - 08:59 #9
>>> janus_007
Kommer det ikke lidt an på om man "pakker" forspørgslerne ind i en SP?

Og så er der spørgsmålet om samtidighed (flerbrugersystem med mange samtidige brugere?). Hvis man roder i sådan et system skal man sørme være varsom og tænke sig rigtig, rigtig godt om hvis man deler sætningerne op.
Avatar billede cjep Nybegynder
30. januar 2009 - 12:48 #10
Nu er det et site med 100 medlemmer og max 4-5 samtidige brugere, så load er til at overse.
Jeg vil lige dimse videre med det her i weekenden på baggrund af jeres udemærkede kommentarer!
/Claus
Avatar billede arne_v Ekspert
31. januar 2009 - 03:07 #11
Hvis man vil lave det som 2 simple(re) forespørgsler så vil en SP formindske latency en del men formentligt ikke øge throughput.

UNION performer ikke altid godt.

Jeg tror ikke at der er flere samtidigheds problemer ved 2 selvstændige SELECT end ved
1 UNION af samme 2 SELECT. Transaktionens isolation level gælder. Også for en enkelt
SQL sætning.
Avatar billede cjep Nybegynder
22. februar 2009 - 22:04 #12
Så fik jeg tid til at komme videre. Jeg er kommet frem til at løse det delvist med 1 SQL, der dog godt nok giver mig nogle uønskede rækker (hver forum overskrift) med i svaret.

De uønskede rækker kan identificeres således:

t_id    p_id    subject
35    34    1. indl&#230;g i 22-forum
36    35    SV:1. indl&#230;g i 22-forum
19    2    NYT emne
33    19    SV:NYT emne
3    2    Nikon objektiver

Regel: Hvis t_id findes i p_id, skal rækken t_id slettes.

Jeg har gang i noget vb.net kode der kan gøre det, men er gået kold i arrays'ene:

For Each dr As DataRow In ds.Tables(0).Select()
For Each dr2 as DataRow In ds.Tables(0).Select()
  if dr2("parent_id") = dr("topic_id") then
  dr.Delete()
  End If
Next
Next

Det fejler naturligvis, fordi den reelt checker på samme række; men er der nogen der kan vise mig hvordan sådan et array-check skal se ud for at virke?

Takker mange gange...
Avatar billede cjep Nybegynder
04. marts 2009 - 20:20 #13
Lukker og opretter under programmering > vb.net
Avatar billede cjep Nybegynder
05. marts 2009 - 22:30 #14
Arne_v hjalp mig på vej ved brug af Views. Se svaret her: http://www.eksperten.dk/spm/866370
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester