OT: heftige Querys

Christian Fein

Christian Fein

Erfahrenes Mitglied
euer heftigstes mysql Query ?

Meins -> PS: Dynamisch generiert das was ihr da seht ist letztendlich das erstellte :


SELECT umfragen_umfragen.id as umfragenid, umfragen_umfragen.typ as umfragentyp,umfrage_fragen.parentid as fragenparentid, umfrage_fragen.id as fragenid, umfragen_umfragen.name as umfragenname, umfrage_fragen.typ as fragetyp, umfrage_fragen.neueseite as neueseite,umfrage_fragen.notwendig as notwendig, starttext, media, subtitle FROM umfragen_umfragen , umfrage_fragen WHERE umfrage_fragen.ordnung >= 5 AND umfragen_umfragen.id=umfrage_fragen.parentid AND umfragen_umfragen.id=1 ORDER BY umfrage_fragen.ordnung LIMIT 6
 
Ist zwar MS-SQL, aber es passt ganz schön:

Ist eigentlich noch ein simpler Cursor, aber war mein erster ;), allerdings mit etwas Hilfestellung erstellt...

Declare @FirmaID int

declare curTmp cursor local for
select distinct firmaid from tblinkproduct
where firmaid not in (select firmaid from tblinkproduct where productid='1')

open curTmp
fetch next from curTmp into @FirmaID

while @@Fetch_Status = 0
BEGIN
begin tran

Insert into tblLnkProduct(ProductID, dtCreationDate, FirmaID, bActive) values (1, getdate(),@FirmaID,0)
if @@error<> 0
BEGIN
Rollback Tran
select cast(@FirmaID as varchar(10)) + ' NICHT ok'
End
else
begin
Commit tran
select cast(@FirmaID as varchar(10)) + ' ok'
end
fetch next from curTmp into @FirmaID
END

close curTmp
deallocate curTmp

Und hier waren ein paar joins notwendig...:

select firma1r,nachnamer,vornamer,telefonfr,plzf,tbldetext.stext as Produkt from firmen
inner join tbllnkproduct on idf=firmaid
inner join tblproduct on tbllnkproduct.productid=tblproduct.productid
inner join tbldetext on tblproduct.textid=tbldetext.textid
where left(plzf,2) in ('51','52','53','54','55','56','57','58','42','35') and country=2
and language=2 and tbllnkproduct.bactive=1 and aktivf=1
group by firma1f,nachnamef,vornamef,telefonf,plzf,tbldetext.stext
order by plzf
 
Zuletzt bearbeitet:
Achja ist das net schön mit solchen Querys zu arbeiten. Vor allem wenn man sie noch über zig IF-Abfragen zusammensetzt. Leider habe ich hier Zuhause keine solche tollen Querys anzubieten. Aber in der Arbeit muss ich mich täglich damit rumschlagen. *g*

Gruss Homer (der es liebt stundenlang einen Query zusammezuschustern, denn danach kein Mensch versteht. *g* Nich mal ich selbst *g*)
 
Query

Noch ein dynamischer MS spezifischer Query:

SELECT DISTINCT room_txt.idroom, texts.id_txt FROM texts INNER JOIN (((((room_txt INNER JOIN room ON room_txt.idroom = room.idroom) INNER JOIN (program INNER JOIN program_txt ON program.idprogram = program_txt.idprogram) ON room.idmain = program.idmain) INNER JOIN mainarticles ON room.idmain = mainarticles.idmain) INNER JOIN item ON mainarticles.iditem = item.id_item) INNER JOIN (dek_lis_txt INNER JOIN dek_lis ON dek_lis_txt.iddek_lis = dek_lis.iddek_lis) ON mainarticles.idmain = dek_lis.idmain) ON texts.id_txt = room_txt.room_txt WHERE program_txt.idprogram=1 AND item.id_item=60 AND dek_lis_txt.iddek_lis=30 AND dek_lis_txt.dek_lis=2 AND mainarticles.height=200
 
kinderkram

ich hätt noch mehr davon =)

stored procedures die erst einmal eine query zusammengenerieren... ms sql server...


CREATE PROCEDURE sp_article_list_fromSubStruct(@fetchLAid varchar(15), @fetchSIid varchar(15), @fetchFields varchar(250), @fetchFragment varchar(250), @sortFields varchar(250), @fetchUSid varchar(15), @TOPcnt varchar(10))

AS

DECLARE @canView varchar(8000)
set @canView = "0"

if (len(@fetchUSid)) > 0
BEGIN

DECLARE @selectedUC int
DECLARE @canViewUC int
DECLARE @asUC int
DECLARE UC_Cursor CURSOR FOR select SK2UC from Sitestruktur2Kategorie inner join UserCategory on SK2UC=UCid inner join Sitestruktur on SK2SI=SIid where SIparent=@fetchSIid and not UCdisabled is null
OPEN UC_Cursor
FETCH NEXT FROM UC_cursor into @selectedUC
WHILE @@FETCH_STATUS = 0
BEGIN
set @canViewUC = 0
set @asUC = @SelectedUC
WHILE @selectedUC <> 0 and @selectedUC <> 9999999
BEGIN
IF (Select count(UAid) from User2UserCategory where UA2US=@fetchUSid and UA2UC=@selectedUC) > 0
BEGIN
set @canView = @canView + "," + CAST(@asUC as varchar)
END
SELECT @selectedUC = UCparent from UserCategory where UCid=@selectedUC
END

FETCH NEXT FROM UC_cursor into @selectedUC
END
CLOSE UC_Cursor
DEALLOCATE UC_Cursor
END




if (len(@SortFields)) = 0
BEGIN
set @SortFields = "ARdisplayStart desc"
END


DECLARE @fetchCTid varchar(250)
set @fetchCTid = null
DECLARE @fetchCTtablename varchar(250)
select @fetchCTtablename = CTtablename,@fetchCTid = CTid from SiteFragment inner join ContentTables on SF2CT=CTid where SFname like @fetchFragment
set @fetchCTtablename = "_" + @fetchCTtablename


if (@fetchCTid) is null
begin
select "Fragment nicht gefunden!" as Error
end
else
begin
if (@TOPcnt) = "0"
BEGIN
execute("select SIid,SNname,UCid,UCname,ARid,ARname,ARowner," +@fetchFields+ " from Sitestruktur2Kategorie inner join UserCategory on SK2UC=UCid inner join Artikel on AR2UC=UCid inner join Content on CO2AR=ARid inner join " + @fetchCTtablename + " on COchildID=XTid inner join Sitestruktur on SK2SI=SIid inner join SitestrukturName on SN2SI=SIid inner join VisibleContent on VC2CO=COid and VC2AR=ARid where CO2LA=" + @fetchLAid + " and SIparent=" + @fetchSIid +" and ARactive=1 and GETDATE() between ARdisplayStart and ARdisplayEnd and AR2CT=" + @fetchCTid + " and SN2LA="+ @fetchLAid+" and (UCdisabled is null or UCid in (" + @canView +")) order by SIpriority,SKpriority," + @SortFields)
END
ELSE
BEGIN
execute("select top " + @TOPcnt + " SIid,SNname,UCid,UCname,ARid,ARname,ARowner," +@fetchFields+ " from Sitestruktur2Kategorie inner join UserCategory on SK2UC=UCid inner join Artikel on AR2UC=UCid inner join Content on CO2AR=ARid inner join " + @fetchCTtablename + " on COchildID=XTid inner join Sitestruktur on SK2SI=SIid inner join SitestrukturName on SN2SI=SIid inner join VisibleContent on VC2CO=COid and VC2AR=ARid where CO2LA=" + @fetchLAid + " and SIparent=" + @fetchSIid +" and ARactive=1 and GETDATE() between ARdisplayStart and ARdisplayEnd and AR2CT=" + @fetchCTid + " and SN2LA="+ @fetchLAid+" and (UCdisabled is null or UCid in (" + @canView +")) order by SIpriority,SKpriority," + @SortFields)
END
end
Zum Vergrößern anklicken....
hihi... gut ist vermutlich nicht perfekt.. vielleicht kann man das ja auch optimieren (sobald man das system dahinter verstanden hat :rolleyes: ) aber hey für einen lehrling nicht schlecht finde ich *g* ;-)
 
Da würde ich lieber mit einer temporären Tabelle arbeiten...
Denn dann musst du dir den Stress nicht in dieser Form machen, du kannst erstmal alles nötige selektieren, und die Einschränkungen danach verarbeiten, und dann ein rs als Rückgabe geben...
 
Hihi, hab da auch was gaaaanz tolles was bei der Performancekontrolle einer Fremdapplikation aufgefallen ist:

( Musste das Statement hier zum posten von 65k Zeichen auf 15k abschneiden :p )

Code: 
SELECT al_T_NODE_0.D_ID AS nodeId, al_T_NODE_0.D_NODE_TYPE_ID AS nodeTypeId, 
  al_T_NODE_0.D_PARENT_ID AS parentId, al_T_NODE_0.D_NAME_RES_ID AS nameResId,
   al_T_NODE_0.D_DESCRIPTION_RES_ID AS descrResId, 
  al_T_NODE_0.D_EXTERNAL_NUMBER AS externalNumber, 
  al_T_NODE_0.D_RESOURCE_SET_ID AS resourceSetId, al_T_NODE_0.D_ORDER AS 
  nodeOrder, al_T_NODE_TYPE_0.D_INTERNAL_DESCRIPTION AS nodeTypeInternalDescr,
   al_T_NODE_TYPE_0.D_NAME_RES_ID AS nodeTypeNameResId, al_NODE_NAME_0.D_TEXT 
  AS nodeName, al_NODE_NAME_0.D_LANGUAGE_ID AS NameDefaultLangId 
FROM
 MC_LID.T_NODE al_T_NODE_0, MC_LID.T_NODE_TYPE al_T_NODE_TYPE_0, (SELECT * 
  FROM MC_LID.T_RESOURCE_VERSION WHERE D_LANGUAGE_ID = :"SYS_B_0000") 
  al_NODE_NAME_0 WHERE al_T_NODE_0.D_NODE_TYPE_ID = al_T_NODE_TYPE_0.D_ID AND 
  al_T_NODE_0.D_NAME_RES_ID = al_NODE_NAME_0.D_RESOURCE_ID(+) AND 
  (al_T_NODE_0.D_ID IN (:"SYS_B_0001", :"SYS_B_0002", :"SYS_B_0003", 
  :"SYS_B_0004", :"SYS_B_0005", :"SYS_B_0006", :"SYS_B_0007", :"SYS_B_0008", 
  :"SYS_B_0009", :"SYS_B_0010", :"SYS_B_0011", :"SYS_B_0012", :"SYS_B_0013", 
  :"SYS_B_0014", :"SYS_B_0015", :"SYS_B_0016", :"SYS_B_0017", :"SYS_B_0018", 
  :"SYS_B_0019", :"SYS_B_0020", :"SYS_B_0021", :"SYS_B_0022", :"SYS_B_0023", 
  :"SYS_B_0024", :"SYS_B_0025", :"SYS_B_0026", :"SYS_B_0027", :"SYS_B_0028", 
  :"SYS_B_0029", :"SYS_B_0030", :"SYS_B_0031", :"SYS_B_0032", :"SYS_B_0033", 
  :"SYS_B_0034", :"SYS_B_0035", :"SYS_B_0036", :"SYS_B_0037", :"SYS_B_0038", 
  :"SYS_B_0039", :"SYS_B_0040", :"SYS_B_0041", :"SYS_B_0042", :"SYS_B_0043", 
  :"SYS_B_0044", :"SYS_B_0045", :"SYS_B_0046", :"SYS_B_0047", :"SYS_B_0048", 
  :"SYS_B_0049", :"SYS_B_0050", :"SYS_B_0051", :"SYS_B_0052", :"SYS_B_0053", 
  :"SYS_B_0054", :"SYS_B_0055", :"SYS_B_0056", :"SYS_B_0057", :"SYS_B_0058", 
  :"SYS_B_0059", :"SYS_B_0060", :"SYS_B_0061", :"SYS_B_0062", :"SYS_B_0063", 
  :"SYS_B_0064", :"SYS_B_0065", :"SYS_B_0066", :"SYS_B_0067", :"SYS_B_0068", 
  :"SYS_B_0069", :"SYS_B_0070", :"SYS_B_0071", :"SYS_B_0072", :"SYS_B_0073", 
  :"SYS_B_0074", :"SYS_B_0075", :"SYS_B_0076", :"SYS_B_0077", :"SYS_B_0078", 
  :"SYS_B_0079", :"SYS_B_0080", :"SYS_B_0081", :"SYS_B_0082", :"SYS_B_0083", 
  :"SYS_B_0084", :"SYS_B_0085", :"SYS_B_0086", :"SYS_B_0087", :"SYS_B_0088", 
  :"SYS_B_0089", :"SYS_B_0090", :"SYS_B_0091", :"SYS_B_0092", :"SYS_B_0093", 
  :"SYS_B_0094", :"SYS_B_0095", :"SYS_B_0096", :"SYS_B_0097", :"SYS_B_0098", 
  :"SYS_B_0099", :"SYS_B_0100", :"SYS_B_0101", :"SYS_B_0102", :"SYS_B_0103", 
   :"SYS_B_3669", :"SYS_B_3670", :"SYS_B_3671", :"SYS_B_3672", :"SYS_B_3673", 
  :"SYS_B_3674", :"SYS_B_3675", :"SYS_B_3676", :"SYS_B_3677", :"SYS_B_3678", 
  :"SYS_B_3679", :"SYS_B_3680", :"SYS_B_3681", :"SYS_B_3682", :"SYS_B_3683", 
  :"SYS_B_3684", :"SYS_B_3685", :"SYS_B_3686", :"SYS_B_3687", :"SYS_B_3688", 
  :"SYS_B_3689", :"SYS_B_3690", :"SYS_B_3691", :"SYS_B_3692", :"SYS_B_3693", 
  :"SYS_B_3694", :"SYS_B_3695", :"SYS_B_3696", :"SYS_B_3697", :"SYS_B_3698", 
  :"SYS_B_3699", :"SYS_B_3700", :"SYS_B_3701", :"SYS_B_3702", :"SYS_B_3703", 
  :"SYS_B_3704", :"SYS_B_3705", :"SYS_B_3706", :"SYS_B_3707", :"SYS_B_3708", 
  :"SYS_B_3709", :"SYS_B_3710", :"SYS_B_3711", :"SYS_B_3712", :"SYS_B_3713", 
  :"SYS_B_3714", :"SYS_B_3715", :"SYS_B_3716", :"SYS_B_3717", :"SYS_B_3718", 
  :"SYS_B_3719", :"SYS_B_3720", :"SYS_B_3721", :"SYS_B_3722", :"SYS_B_3723", 
  :"SYS_B_3724", :"SYS_B_3725", :"SYS_B_3726", :"SYS_B_3727", :"SYS_B_3728", 
  :"SYS_B_3729", :"SYS_B_3730", :"SYS_B_3731", :"SYS_B_3732", :"SYS_B_3733", 
  :"SYS_B_3734", :"SYS_B_3735", :"SYS_B_3736", :"SYS_B_3737", :"SYS_B_3738", 
  :"SYS_B_3739", :"SYS_B_3740", :"SYS_B_3741", :"SYS_B_3742", :"SYS_B_3743", 
  :"SYS_B_3744", :"SYS_B_3745", :"SYS_B_3746", :"SYS_B_3747", :"SYS_B_3748", 
  :"SYS_B_3749", :"SYS_B_3750", :"SYS_B_3751", :"SYS_B_3752", :"SYS_B_3753", 
  :"SYS_B_3754", :"SYS_B_3755", :"SYS_B_3756", :"SYS_B_3757", :"SYS_B_3758", 
  :"SYS_B_3759", :"SYS_B_3760", :"SYS_B_3761", :"SYS_B_3762", :"SYS_B_3763", 
  :"SYS_B_3764", :"SYS_B_3765", :"SYS_B_3766", :"SYS_B_3767", :"SYS_B_3768", 
  :"SYS_B_3769", :"SYS_B_3770", :"SYS_B_3771", :"SYS_B_3772", :"SYS_B_3773", 
  :"SYS_B_3774", :"SYS_B_3775", :"SYS_B_3776", :"SYS_B_3777", :"SYS_B_3778", 
  :"SYS_B_3779", :"SYS_B_3780", :"SYS_B_3781", :"SYS_B_3782", :"SYS_B_3783", 
  :"SYS_B_3784", :"SYS_B_3785", :"SYS_B_3786", :"SYS_B_3787", :"SYS_B_3788", 
  :"SYS_B_3789", :"SYS_B_3790", :"SYS_B_3791", :"SYS_B_3792", :"SYS_B_3793", 
  :"SYS_B_3794", :"SYS_B_3795", :"SYS_B_3796", :"SYS_B_3797", :"SYS_B_3798", 
  :"SYS_B_3799", :"SYS_B_3800", :"SYS_B_3801", :"SYS_B_3802", :"SYS_B_3803", 
  :"SYS_B_3804", :"SYS_B_3805", :"SYS_B_3806", :"SYS_B_3807", :"SYS_B_3808", 
  :"SYS_B_3809", :"SYS_B_3810", :"SYS_B_3811", :"SYS_B_3812", :"SYS_B_3813", 
  :"SYS_B_3814", :"SYS_B_3815", :"SYS_B_3816", :"SYS_B_3817", :"SYS_B_3818", 
  :"SYS_B_3819", :"SYS_B_3820", :"SYS_B_3821", :"SYS_B_3822", :"SYS_B_3823", 
  :"SYS_B_3824", :"SYS_B_3825", :"SYS_B_3826", :"SYS_B_3827", :"SYS_B_3828", 
  :"SYS_B_3829", :"SYS_B_3830", :"SYS_B_3831", :"SYS_B_3832", :"SYS_B_3833", 
  :"SYS_B_3834", :"SYS_B_3835", :"SYS_B_3836", :"SYS_B_3837", :"SYS_B_3838", 
  :"SYS_B_3839", :"SYS_B_3840", :"SYS_B_3841", :"SYS_B_3842", :"SYS_B_3843", 
  :"SYS_B_3844", :"SYS_B_3845", :"SYS_B_3846", :"SYS_B_3847", :"SYS_B_3848", 
  :"SYS_B_3849", :"SYS_B_3850", :"SYS_B_3851", :"SYS_B_3852", :"SYS_B_3853", 
  :"SYS_B_3854", :"SYS_B_3855", :"SYS_B_3856", :"SYS_B_3857", :"SYS_B_3858", 
  :"SYS_B_3859", :"SYS_B_3860", :"SYS_B_3861", :"SYS_B_3862", :"SYS_B_3863", 
  :"SYS_B_3864", :"SYS_B_3865", :"SYS_B_3866", :"SYS_B_3867", :"SYS_B_3868", 
  :"SYS_B_3869", :"SYS_B_3870", :"SYS_B_3871", :"SYS_B_3872", :"SYS_B_3873", 
  :"SYS_B_3874", :"SYS_B_3875", :"SYS_B_3876", :"SYS_B_3877", :"SYS_B_3878", 
  :"SYS_B_3879", :"SYS_B_3880", :"SYS_B_3881", :"SYS_B_3882", :"SYS_B_3883", 
  :"SYS_B_3884", :"SYS_B_3885", :"SYS_B_3886", :"SYS_B_3887", :"SYS_B_3888", 
  :"SYS_B_3889", :"SYS_B_3890", :"SYS_B_3891", :"SYS_B_3892", :"SYS_B_3893", 
  :"SYS_B_3894", :"SYS_B_3895", :"SYS_B_3896", :"SYS_B_3897", :"SYS_B_3898", 
  :"SYS_B_3899", :"SYS_B_3900", :"SYS_B_3901", :"SYS_B_3902", :"SYS_B_3903", 
  :"SYS_B_3904", :"SYS_B_3905", :"SYS_B_3906", :"SYS_B_3907", :"SYS_B_3908", 
  :"SYS_B_3909", :"SYS_B_3910", :"SYS_B_3911", :"SYS_B_3912", :"SYS_B_3913", 
  :"SYS_B_3914", :"SYS_B_3915", :"SYS_B_3916", :"SYS_B_3917", :"SYS_B_3918", 
  :"SYS_B_3919", :"SYS_B_3920", :"SYS_B_3921", :"SYS_B_3922", :"SYS_B_3923", 
  :"SYS_B_3924", :"SYS_B_3925", :"SYS_B_3926", :"SYS_B_3927", :"SYS_B_3928", 
  :"SYS_B_3929", :"SYS_B_3930", :"SYS_B_3931", :"SYS_B_3932", :"SYS_B_3933", 
  :"SYS_B_3934", :"SYS_B_3935", :"SYS_B_3936", :"SYS_B_3937", :"SYS_B_3938", 
  :"SYS_B_3939", :"SYS_B_3940", :"SYS_B_3941", :"SYS_B_3942", :"SYS_B_3943", 
  :"SYS_B_3944", :"SYS_B_3945", :"SYS_B_3946", :"SYS_B_3947", :"SYS_B_3948", 
  :"SYS_B_3949", :"SYS_B_3950", :"SYS_B_3951", :"SYS_B_3952", :"SYS_B_3953", 
  :"SYS_B_3954", :"SYS_B_3955", :"SYS_B_3956", :"SYS_B_3957", :"SYS_B_3958", 
  :"SYS_B_3959", :"SYS_B_3960", :"SYS_B_3961", :"SYS_B_3962", :"SYS_B_3963", 
  :"SYS_B_3964", :"SYS_B_3965", :"SYS_B_3966", :"SYS_B_3967", :"SYS_B_3968"))
 
Mein vermutlich "heftigstes" MySQL-Query ist hier im Forum entstanden:
Distanzberechnung auf Basis geographischer Längen- und Breitengrade (gespeichert als double)
Daraus ist dann in Heimarbeit noch das geworden:
Code: 
SELECT
(
  ROUND(
    (
      ABS(
        6378137*2*PI(
        )
        *
        (
          360/(
            2*PI(
            )
          )
          *
          (
            ATAN(
              SQRT(
                1
                -
                (
                  (
                    (
                      SIN(
                        (
                          u2.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      SIN(
                        (
                          u1.user_latitude/360*2*PI(
                          )
                        )
                      )
                    )
                    +
                    (
                      COS(
                        (
                          u2.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      COS(
                        (
                          u1.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      COS(
                        (
                          u1.user_longitude/360*2*PI(
                          )
                        )
                        -
                        (
                          u2.user_longitude/360*2*PI(
                          )
                        )
                      )
                    )
                  )*(
                    (
                      SIN(
                        (
                          u2.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      SIN(
                        (
                          u1.user_latitude/360*2*PI(
                          )
                        )
                      )
                    )
                    +
                    (
                      COS(
                        (
                          u2.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      COS(
                        (
                          u1.user_latitude/360*2*PI(
                          )
                        )
                      )
                      *
                      COS(
                        (
                          u1.user_longitude/360*2*PI(
                          )
                        )
                        -
                        (
                          u2.user_longitude/360*2*PI(
                          )
                        )
                      )
                    )
                  )
                )
              )
              /
              (
                (
                  SIN(
                    (
                      u2.user_latitude/360*2*PI(
                      )
                    )
                  )
                  *
                  SIN(
                    (
                      u1.user_latitude/360*2*PI(
                      )
                    )
                  )

                )
                +
                (
                  COS(
                    (
                      u2.user_latitude/360*2*PI(
                      )
                    )
                  )
                  *
                  COS(
                    (
                      u1.user_latitude/360*2*PI(
                      )
                    )
                  )
                  *
                  COS(
                    (
                      u1.user_longitude/360*2*PI(
                      )
                    )
                    -
                    (
                      u2.user_longitude/360*2*PI(
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
      /
      360
    )
  /
  1000
  )
) AS `Distanz in km`,
u1.user_id AS `ID User 1`,
u1.user_membername AS `Name User 1`,
CONCAT(
  FLOOR(ABS(u1.user_latitude))
  ,'° '
  ,FLOOR((ABS(u1.user_latitude)
    -FLOOR(ABS(u1.user_latitude)))* 60)
  ,'\' '
  ,FLOOR(
    (((ABS(u1.user_latitude)-FLOOR(
      ABS(u1.user_latitude)))* 60)
    -FLOOR((ABS(u1.user_latitude)-FLOOR(
      ABS(u1.user_latitude)))* 60))*60
  )
  ,IF(u1.user_latitude<0,'\" S','\" N')
) AS `Latitude User 1`,
CONCAT(
  FLOOR(ABS(u1.user_longitude))
  ,'° '
  ,FLOOR((ABS(u1.user_longitude)-FLOOR(
    ABS(u1.user_longitude)))* 60)
  ,'\' '
  ,FLOOR(
    (((ABS(u1.user_longitude)-FLOOR(
      ABS(u1.user_longitude)))* 60)
    -FLOOR((ABS(u1.user_longitude)-FLOOR(
      ABS(u1.user_longitude)))* 60))*60
  )
  ,IF(u1.user_longitude<0,'\" W','\" E')
) AS `Longitude User 1`,
u2.user_id AS `ID User 2`,
u2.user_membername AS `Name User 2`,
CONCAT(
  FLOOR(ABS(u2.user_latitude))
  ,'° '
  ,FLOOR((ABS(u2.user_latitude)
    -FLOOR(ABS(u2.user_latitude)))* 60)
  ,'\' '
  ,FLOOR(
    (((ABS(u2.user_latitude)-FLOOR(
      ABS(u2.user_latitude)))* 60)
    -FLOOR((ABS(u2.user_latitude)-FLOOR(
      ABS(u2.user_latitude)))* 60))*60
  )
  ,IF(u2.user_latitude<0,'\" S','\" N')
) AS `Latitude User 2`,
CONCAT(
  FLOOR(ABS(u2.user_longitude))
  ,'° '
  ,FLOOR((ABS(u2.user_longitude)-FLOOR(
    ABS(u2.user_longitude)))* 60)
  ,'\' '
  ,FLOOR(
    (((ABS(u2.user_longitude)-FLOOR(
      ABS(u2.user_longitude)))* 60)
    -FLOOR((ABS(u2.user_longitude)-FLOOR(
      ABS(u2.user_longitude)))* 60))*60
  )
  ,IF(u2.user_longitude<0,'\" W','\" E')
) AS `Longitude User 2`
FROM users AS u1
JOIN users AS u2
ON (u1.user_id < u2.user_id)
ORDER BY `Distanz in km`
Ohne JOIN kann ich nicht leben :D
Damit hätte man schon mal die Daten für eine symmetrische Distanzmatrix, z.B. zur Lösung eines TSP. Aber das sollte man dann wohl doch besser nicht in SQL lösen ;)

Gruß hpvw
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück