Bir mysql tablodaki satırları karşılaştırarak

1 Cevap php

Ok here's the deal I got one table with a bunch of client information. Each client makes up to one purchase a year which is represented by an individual row. there's a column for the year and there's a column the contains a unique identifier for each client. What I need to do is to construct a query that takes last year and this year and shows me which clients were here made a purchase last year but not make a purchase this year.

Ben de müşteri önce son bir satın alma geçen yıl ve yıl yapmak yoktu ama bu yıl satın alma yaptınız bana gösteren bir sorgu oluşturmak gerekir.

1 Cevap

Bu tablo göz önüne alındığında:

Purchase
========
PurchaseID (autoincrement int PK)
ClientID (int FK)
Year (int)
Amount (float)

Örnek veri:

insert into Purchase (ClientID, Year, Amount) values (1, 2009, 123)
insert into Purchase (ClientID, Year, Amount) values (2, 2009, 123)
insert into Purchase (ClientID, Year, Amount) values (2, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (3, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (3, 2007, 123)
insert into Purchase (ClientID, Year, Amount) values (4, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (4, 2008, 123)

2009 yılında bir satın alma yaptı ancak ertesi yıl (2010):

select p1.*
from Purchase p1
left outer join Purchase p2 on p1.ClientID = p2.ClientID and p1.Year = p2.Year - 1
where p2.ClientID is null
    and p1.Year = 2009

Sonuçlar:

PurchaseID  Year        ClientID    Amount
----------- ----------- ----------- ---------------------
1           2009        1           123.00

2010 yılında bir satın alma yaptı, ama önceki iki yıl (2008 veya 2009):

select p3.*
from 
Purchase p3
left outer join Purchase p2 on p3.ClientID = p2.ClientID and p3.Year = p2.Year + 1
left outer join Purchase p1 on p3.ClientID = p1.ClientID and p3.Year = p1.Year + 2
where p2.ClientID is null
    and p1.ClientID is null
    and p3.Year = 2010

Sonuçlar:

PurchaseID  Year        ClientID    Amount
----------- ----------- ----------- ---------------------
4           2010        3           123.00

etiketler