SELECT
a.contact_id AS ContactID
,familyName AS FamilyName
,givenName AS GivenName
,NRIC
,mobilePhoneNumber AS Mobile
,email AS Email, gender AS Gender
, ROUND(a.amount,2) AS TotalSpending
,a.chance AS TotalChances
, GROUP_CONCAT(CONCAT(a.purchase_time, ': ', c.storeName,': $', CAST(b.amount AS CHAR), ' (', paymentTypeName,')') SEPARATOR "") AS purchase_details
FROM
(
SELECT
a.contact_id
,b.purchase_id
,b.purchase_time
, SUM(a.chance) AS chance
, SUM(b.amount) AS amount
FROM new_luckydraw_entry a,
new_purchase b
WHERE a.related_id=b.purchase_id AND a.luckydraw_campaign_id=296 AND a.entry_time BETWEEN '2013-08-01 00:00:00' AND '2014-03-31 23:59:59'
GROUP BY a.contact_id
) a,
new_purchase_detail b,store c,paymentType d,contact e
WHERE a.purchase_id=b.purchase_id AND b.store_id=c.storeId AND b.payment_id=d.paymentTypeId AND a.contact_id=e.contactId
GROUP BY a.contact_id
ORDER BY a.purchase_time ASC
campaign_draw participants list 的sql
a.luckydraw_campaign_id=296 的296取的是再db_central里的luckdraw_campaign表，这个表是所有campaign的表所以类型是从这里取的很多次都在这个地方迷糊，记录一下。
邮件格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Hi Mengchu,
Here with the conduct grand draw link from 01 Aug 2013 to 31 March 2014:
https://hfc.icolumn.com/hfc/draw/909/index.php
Login name: hfcmarcom
Password: harbourfc2013
And attached is the participants list