Check out all loyalty card numbers with the first order amount exceeding 1000 and their first order amount

Title from: http://www.cnblogs.com/xpivot/p/4143069.html#!comments

There is an ER diagram describing the data structure, simple text described as follows, crossing a variety of brain complement the main foreign key relationship:

A membership Form (account), the field has a member ID (ACCOUNT_ID), membership card number (Account_num) ...

A trading order form (trans), field with Member ID (ACCOUNT_ID), Trading Time (trans_time), transaction amount (sales) ...

Ask for a membership card number with the first order amount exceeding 1000 and its first order amount,

Note:

1. The first order refers to a single order with the minimum transaction time for each member.

2. The membership table has 10 million records

3. If the member does not have any order or the first order amount is less than 1000, the first order amount will return 0.

According to the title description, build the table

The Account table is as follows:

The statements are as follows:

/*Navicat MySQL Data transfersource server:localhostsource Server version:50614source host:local Host:3306source database:amydbtarget Server type:mysqltarget server Version:50614file encoding:65 001date:2014-12-04 19:23:39*/SETForeign_key_checks=0;-- ------------------------------Table structure for ' account '-- ----------------------------DROP TABLE IF EXISTS' account ';CREATE TABLE' account ' (' KeyID ' )int(4) not NULLauto_increment, ' account_id 'varchar( -) not NULL, ' Account_card 'varchar( -) not NULL,  PRIMARY KEY(' KeyID ')) ENGINE=InnoDB auto_increment=3 DEFAULTCHARSET=UTF8;-- ------------------------------Records of account-- ----------------------------INSERT  into' Account 'VALUES('1','Tianxue','[email protected]');INSERT  into' Account 'VALUES('2','Xiaohua','[email protected]');

View Code

The trans table is as follows:

The statements are as follows:

/*Navicat MySQL Data transfersource server:localhostsource Server version:50614source host:local Host:3306source database:amydbtarget Server type:mysqltarget server Version:50614file encoding:65 001date:2014-12-04 19:23:46*/SETForeign_key_checks=0;-- ------------------------------Table structure for ' trans '-- ----------------------------DROP TABLE IF EXISTS' trans ';CREATE TABLE' trans ' (' KeyID ' )int(4) not NULLauto_increment, ' account_id 'varchar( -) not NULL, ' Trans_time 'datetime  not NULL, ' Sales 'int(8) not NULL,  PRIMARY KEY(' KeyID ')) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=UTF8;-- ------------------------------Records of Trans-- ----------------------------INSERT  into' Trans 'VALUES('1','Tianxue','2014-12-22 18:38:20',' -');INSERT  into' Trans 'VALUES('2','Xiaohua','2014-12-08 18:38:38',' $');INSERT  into' Trans 'VALUES('3','Tianxue','2014-11-12 18:38:51',' -');INSERT  into' Trans 'VALUES('4','Xiaohua','2014-12-31 18:39:07',' -');

View Code

According to the condition of the topic only the result should be:

Here is my query statement, the result is right, but not to consider efficiency.

SELECTaccount.account_id, Account.account_card,COALESCE(C.sales,0) Sales fromAccount Left JOIN        (        SELECTa.account_id, A.sales fromTrans asAINNER JOIN            (SELECTACCOUNT_ID,min(Trans_time) asFirsttime fromTransGROUP  byACCOUNT_ID) asb ona.account_id=b.account_idWHEREA.sales>  +  andA.trans_time=B.firsttime) asC onaccount.account_id=c.account_id

Coalesce, what is this? Just looked for it from the Internet.

Is there more efficient?

Check out all loyalty card numbers with the first order amount exceeding 1000 and their first order amount