Case "0" Response.Write "1 ord" strSQL = "select 1 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 2 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' UNION select 3 as myOrder, * from power where titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 4 as myOrder, * from power where titel like '%" & strTest & "%' and description like '%" & strTest & "%'order by myOrder"
Case "1" Response.Write "2 ord" strSQL = "select 1 as myOrder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' UNION select 2 as myOrder, * from power where description like '%" & strTest & "%' UNION select 3 as myOrder, * from power where description like '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%'UNION select 4 as myOrder, * from power where description like '%" & strKeyword(0) & "%' OR description like '%" & strKeyword(1) & "%' order by myOrder"
Case "2" Response.Write "3 ord" strSQL = "select 1 as myOrder, * from power where description like '%" & strTest & "%' UNION select 2 as myOrder, * from power where description like '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' and description like '%" & strKeyword(2) & "%' UNION select 3 as myOrder, * from power where description like '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' order by myOrder" '3 ord i søgningen
Case Else Response.Write "+3 ord" strSQL = "select 1 as myOrder, * from power where description like '%" & strTest & "%' UNION select 2 as myOrder, * from power where description like '%" & strKeyword(0) & "%' order by myOrder" 'mere end 3 ord i søgningen
End Select
F.eks. skulle søgning med ét keyword sorteres sådan her: 1. poster hvor TITEL, DESCRIPTION og WWW indeholder keyword 2. poster hvor TITEL, DESCRIPTION indeholder keyword
osv...
som jeg har lavet det vil den retunere samme post flere gange :-(
Hvis nogen har en anden idé til at sortere f.eks som her på eksperten.dk hvor man kan få spørgsmål sorteret efter relevans....
Jeg har ikke lige SQL manualen foran mig, men der findes alternativer til "UNION" som giver det samme resultat uden duplicates. Prøv at kigge i en SQL-reference
jeg tror du er noedt til at bruge " AND 1 NOT IN (SELECT ...) AND 1 NOT IN (SELECT ...) " dvs. at du for hver sql-kald ogsaa tjekker at den ikke eksisterer i de andre. Det give nogle meget lange sql-kald, men burde virke.
F.eks. ved case 2: Case "2" Response.Write "3 ord" strSQL = "select 1 as myOrder, * from power where description like '%" & strTest & "%' UNION select 2 as myOrder, * from power where description like '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' and description like '%" & strKeyword(2) & "%' UNION select 3 as myOrder, * from power where description like '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' AND 1 NOT IN (select 1 as myOrder, * from power where description like '%" & strTest & "%' UNION select 2 as myOrder, * from power where description like '%" & strKeyword(0) & "%' ) order by myOrder" '3 ord i søgningen
Det burde jo virke med: AND 1 NOT IN (SELECT ...) AND 1 NOT IN osv... men den bliver sørme tung, derfor vil jeg prøve at arbejde lidt med en temp.. Det har jeg aldrig prøvet så kommer nok til at skulle bruge mere hjælp..
select 1 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 2 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' AND 1 NOT IN (select 1 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' and www like '%" & strTest & "%')UNION select 3 as myOrder, * from power where titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 4 as myOrder, * from power where titel like '%" & strTest & "%' and description like '%" & strTest & "%'order by myOrder"
Og jeg får en fejl:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.
Du skal aendre den til: select 1 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 2 as myorder, * from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' AND 1 NOT IN (select 1 as myorder from power where description like '%" & strTest & "%' and titel like '%" & strTest & "%' and www like '%" & strTest & "%')UNION select 3 as myOrder, * from power where titel like '%" & strTest & "%' and www like '%" & strTest & "%' UNION select 4 as myOrder, * from power where titel like '%" & strTest & "%' and description like '%" & strTest & "%'order by myOrder"
proev med distinct foran alle feltnavnene: select distinct 1 as myorder, * from power...
Synes godt om
Ny brugerNybegynder
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.
