There is a table: membership card
Fields: membership number, card number and issuing date of commissioning date, shop
Each member may have more than one card, now want to according to the [shop] listed member card in his hand out to enable the membership card during a long period of the first opening date whether the query term, in order to confirm whether the member is an effective new members, said the possibility of repetitive, main purpose is to statistics each shop in a certain period of new active member, the new definition of active member: all members of the membership card of the earliest opening date for a while during the statistical period,

The SQL beats me, please expert help, be urgent!!!!!!

CodePudding user response:

No database on this machine now, but in general use group by should be yes, try to write basic query statement as follows, with the help of reference:

Date of the SELECT membership number, MIN (enabled) AS to enable the earliest date FROM membership card GROUP BY membership number

CodePudding user response:

For example
# # # # # # member store card issuing date enable date
Record 1: zhang SAN 1 store a0001 2013/5/2 2015/2/1
Record 2: zhang 3 shop c0033 2015/2/5 9999/12/31
Record three: zhang SAN 2 shop b0008 2013/3/6 2014/3/31
Record 4: li si 1 store a0002 2013/4/5 2015/1/28
Record 5; Two 2 shop b0005 2014/9/10 2015/1/31
Assumes that the extraction of 2014 new members effectively, should be recorded after extracting 3
Extracting new active member 2015, should be recorded after 4, record 5
The SQL should be how to write? Thank you!

CodePudding user response:

Date is to choose to open an account in between the two date, right?
Use the select AA from the TABLE where the date between date1 and date2

CodePudding user response:

The

reference 3 floor WallesCai response:

is to choose to open an account date between the two date, right?
Use the select AA from the TABLE where the date between date1 and date2

[member] will be repeated with a distinct and do not have complete records (all fields)

CodePudding user response:

Manual is
1 get all enable membership card during the period of a member (no repetition)
2 to 1 member all membership card enabled the earliest date
3 if 2 minimum opening date in a certain period, confirmation is added during the period of the active member
Step 1 is optional, but it can decrease the 2nd query (I think)
Real bcrun given statement basically achieved the required functionality, but group by cannot obtain records all of the fields, and how to filter in the group by minimum opening date and I haven't been able to solve

CodePudding user response:

Dizzy,
This requirement should be in accordance with the membership card number to the column, not according to the member name, because there is "different people with the same situation,
According to the membership card number to the list, the corresponding simple many...

CodePudding user response:

Extracting new active member 2014, should be recorded after 3

 select * from membership card t 
Where commissioning date between # 2014/1/1 # and # # 2014/12/31 
And not the exists (select 1 from the membership card where the member card number=t. membership number and commissioning date & lt; # # 2014/1/1) 

Extracting new active member 2015, should be recorded after 4, record 5

 select * from membership card t 
Where commissioning date between # 2015/1/1 # and # # 2015/12/31 
And not the exists (select 1 from the membership card where the member card number=t. membership number and commissioning date & lt; # # 2015/1/1) 

CodePudding user response:

reference 4 floor fzx4936 response:

Quote: reference WallesCai reply: 3/f

Date is to choose to open an account in between the two date, right?
Use the select AA from the TABLE where the date between date1 and date2

[member] will be repeated with a distinct and can not get the complete records (all fields)

The

reference

Posting said "there is a table: membership card
Fields: membership number, card number and issuing date of commissioning date, shop "

Membership number or the card number should not be repeated

CodePudding user response:

refer to the eighth floor WallesCai response:

Quote: refer to 4th floor fzx4936 response:

Quote: refer to the third floor WallesCai response:

Date is to choose to open an account in between the two date, right?
Use the select AA from the TABLE where the date between date1 and date2

[member] will be repeated with a distinct and can not get the complete records (all fields)

The

reference

Posting said "there is a table: membership card
Fields: membership number, card number and issuing date of commissioning date, shop "

Membership number or the card number should not be repeated.

Card number without repetitive membership number has a man may have multiple Numbers historical reasons

CodePudding user response:

refer to 7th floor ACMAIN_CHM response:

extract 2014 new members effectively, should be recorded after extraction of 3

 select * from membership card t 
Where commissioning date between # 2014/1/1 # and # # 2014/12/31 
And not the exists (select 1 from the membership card where the member card number=t. membership number and commissioning date & lt; # # 2014/1/1) 

Extracting new active member 2015, should be recorded after 4, record 5

 select * from membership card t 
Where commissioning date between # 2015/1/1 # and # # 2015/12/31 
And not the exists (select 1 from the membership card where the member card number=t. membership number and commissioning date & lt; # # 2015/1/1) 

T watch is?

CodePudding user response:

What to do if someone of the same name, haha

CodePudding user response:

Too esoteric SQL statements I won't, but I think can delete the redundant records with the same first get a temporary table, then much easier