内容比较多,应该很烦,小弟解决不了,向各位求救,拜托各位了要求:当用户通过关键词搜索时,首先查找具有该关键词的用户,然后再比较相应关键词组别金额是否足额(注1),资金合格后再比较资金充值时间先后顺序,资金足额的用户排完后,再排无资金但有关键词的用户,此时比较关键词注册时间的先后顺序,这个排完后,再比较未注册该关键词但该用户其它信息里包括该关键词的用户,此时比较用户注册时间的先后顺序;总体思想是先充值,先注册,先排名。 (直接意思就是先排列有关键词但又有足额资金的用户,再排列有关键词但无足额资金或无资金的用户,最后排无注册关键词但信息内容有相应关键词的用户,各级别之间按时间先后顺序排列。)相关的sql 语句是:$sql="select distinct member.address as address,member.id as id,member.skills as skills,member.products as products,member.website as website,member.nickname as nickname,member.brands as brands,key_info.id as kid from member,key_info where key_info.mid=member.id and key_info.keyname like'%".$keyword."%' and member.ispersonal=".$ispersonal." and member.iflock=0 or (`nickname` like'%".$keyword."%' or `website` like'%".$keyword."%' or `industry_job` like'%".$keyword."%' or `skills` like'%".$keyword."%' or `address` like'%".$keyword."%' or `searchkey` like'%".$keyword."%') order by key_info.gje desc,key_info.rztime desc,key_info.times desc,kid desc,serialno desc";
数据库结构:
表 操作 记录数 类型 整理 大小 多余
account 26 MyISAM utf8_general_ci 2.9 KB -
account_order 8 MyISAM utf8_general_ci 2.4 KB -
article 0 MyISAM utf8_general_ci 1.0 KB -
articleclass 0 MyISAM utf8_general_ci 1.0 KB -
category 0 MyISAM utf8_general_ci 1.0 KB -
column 0 MyISAM utf8_general_ci 1.0 KB -
contact 0 MyISAM utf8_general_ci 1.0 KB -
contact_enterprise 0 MyISAM utf8_general_ci 1.0 KB -
contact_group 0 MyISAM utf8_general_ci 1.0 KB -
contact_personal 0 MyISAM utf8_general_ci 1.0 KB -
enterprise_books 23 MyISAM utf8_general_ci 3.3 KB -
enterprise_books_bm 17 MyISAM utf8_general_ci 2.4 KB 24 字节
guset_book 24 MyISAM utf8_general_ci 7.6 KB -
key_group 11 MyISAM utf8_general_ci 2.2 KB -
key_info 61 MyISAM utf8_general_ci 4.7 KB 44 字节
key_tj 119 MyISAM utf8_general_ci 9.8 KB -
links 8 MyISAM utf8_general_ci 2.7 KB -
member 61 MyISAM utf8_general_ci 159.4 KB -
member_book 0 MyISAM utf8_general_ci 1.0 KB -
member_book_create 43 MyISAM utf8_general_ci 2.4 KB -
member_enterprise 20 MyISAM utf8_general_ci 12.4 KB -
member_friend 1 MyISAM utf8_general_ci 2.0 KB -
member_group 358 MyISAM utf8_general_ci 17.0 KB -
member_job 5 MyISAM utf8_general_ci 2.2 KB -
member_manager 7 MyISAM utf8_general_ci 2.2 KB -
member_personal 30 MyISAM utf8_general_ci 35.0 KB -
member_singlepage 6 MyISAM utf8_general_ci 3.1 KB -
product 10 MyISAM utf8_general_ci 2.7 KB -
productclass 1 MyISAM utf8_general_ci 2.0 KB -
recordinfo 24 MyISAM utf8_general_ci 35.3 KB -
siteinfo 0 MyISAM utf8_general_ci 1.0 KB -
sousuo 77 MyISAM utf8_general_ci 4.5 KB -
webmaster 7 MyISAM utf8_general_ci 3.6 KB -
webmaster_config 1 MyISAM utf8_general_ci 2.6 KB -
webmaster_contact 4 MyISAM utf8_general_ci 6.0 KB -
webmaster_grade 4 MyISAM utf8_general_ci 2.1 KB -
webmaster_hr 5 MyISAM utf8_general_ci 26.4 KB -
webmaster_know 3 MyISAM utf8_general_ci 2.2 KB -
webmaster_links 9 MyISAM utf8_general_ci 2.5 KB -
webmaster_links_wap 3 MyISAM utf8_general_ci 2.1 KB -
webmaster_news 3 MyISAM utf8_general_ci 2.2 KB -
webmaster_news_class 2 MyISAM utf8_general_ci 2.1 KB -
webmaster_rights 3 MyISAM utf8_general_ci 2.1 KB -
webmaster_singlepage 4 MyISAM utf8_general_ci 4.3 KB account表结构
id int(11) 否 auto_increment
mid int(11) 是 NULL
gid int(11) 是 NULL
options int(1) 是 1
money double 否 0
times int(11) 是 NULL
odrid int(11) 是 NULL
kid int(11) 是 NULL member表结构
id int(11) UNSIGNED 否 auto_increment
ispersonal int(1) 是 0
loginid varchar(255) utf8_general_ci 是 NULL
password varchar(255) utf8_general_ci 是 NULL
per_books_rights tinyint(1) 否 1
security int(11) UNSIGNED 是 NULL
nickname varchar(255) utf8_general_ci 是 NULL
img varchar(255) utf8_general_ci 是 none.gif
truename text utf8_general_ci 是 NULL
truenamed text utf8_general_ci 是 NULL
sex text utf8_general_ci 是 NULL
age text utf8_general_ci 是 NULL
place text utf8_general_ci 是 NULL
blood text utf8_general_ci 是 NULL
weight text utf8_general_ci 是 NULL
height text utf8_general_ci 是 NULL
lunar text utf8_general_ci 是 NULL
starts text utf8_general_ci 是 NULL
marriage text utf8_general_ci 是 NULL
qual text utf8_general_ci 是 NULL
polotical text utf8_general_ci 是 NULL
brands text utf8_general_ci 是 NULL
products text utf8_general_ci 是 NULL
about text utf8_general_ci 是 NULL
salesphone text utf8_general_ci 是 NULL
customphone text utf8_general_ci 是 NULL
complaintsphone text utf8_general_ci 是 NULL
afteretphone text utf8_general_ci 是 NULL
industry_job text utf8_general_ci 是 NULL
idnum text utf8_general_ci 是 NULL
skills text utf8_general_ci 是 NULL
msn text utf8_general_ci 是 NULL
qq text utf8_general_ci 是 NULL
mail text utf8_general_ci 是 NULL
officephone text utf8_general_ci 是 NULL
fax text utf8_general_ci 是 NULL
mob text utf8_general_ci 是 NULL
homephone text utf8_general_ci 是 NULL
address text utf8_general_ci 是 NULL
birt text utf8_general_ci 是 NULL
work text utf8_general_ci 是 NULL
workex text utf8_general_ci 是 NULL
university text utf8_general_ci 是 NULL
highschool text utf8_general_ci 是 NULL
middleschool text utf8_general_ci 是 NULL
primaryschool text utf8_general_ci 是 NULL
website text utf8_general_ci 是 NULL
netphone text utf8_general_ci 是 NULL
searchkey text utf8_general_ci 是 NULL
lasttim int(11) 是 NULL
lastip varchar(40) utf8_general_ci 是 NULL
iflock int(1) 是 0
recommend int(1) 是 0
serialno int(11) 是 0
count int(11) UNSIGNED 否 0
updatetime int(11) 是 0
searchkeyword longtext utf8_general_ci 是 NULL
products_skill text utf8_general_ci 是 NULL
recordinfo_state tinyint(1) 否 0 key_info表结构
id int(11) UNSIGNED 否 auto_increment
mid int(11) 是 NULL
groupid int(11) 是 NULL
keyname varchar(255) utf8_general_ci 是 NULL
count int(255) 是 0
price double 是 0
times int(12) 是 NULL
rztime int(12) 是 NULL
gje double 是 NULL
数据库结构:
表 操作 记录数 类型 整理 大小 多余
account 26 MyISAM utf8_general_ci 2.9 KB -
account_order 8 MyISAM utf8_general_ci 2.4 KB -
article 0 MyISAM utf8_general_ci 1.0 KB -
articleclass 0 MyISAM utf8_general_ci 1.0 KB -
category 0 MyISAM utf8_general_ci 1.0 KB -
column 0 MyISAM utf8_general_ci 1.0 KB -
contact 0 MyISAM utf8_general_ci 1.0 KB -
contact_enterprise 0 MyISAM utf8_general_ci 1.0 KB -
contact_group 0 MyISAM utf8_general_ci 1.0 KB -
contact_personal 0 MyISAM utf8_general_ci 1.0 KB -
enterprise_books 23 MyISAM utf8_general_ci 3.3 KB -
enterprise_books_bm 17 MyISAM utf8_general_ci 2.4 KB 24 字节
guset_book 24 MyISAM utf8_general_ci 7.6 KB -
key_group 11 MyISAM utf8_general_ci 2.2 KB -
key_info 61 MyISAM utf8_general_ci 4.7 KB 44 字节
key_tj 119 MyISAM utf8_general_ci 9.8 KB -
links 8 MyISAM utf8_general_ci 2.7 KB -
member 61 MyISAM utf8_general_ci 159.4 KB -
member_book 0 MyISAM utf8_general_ci 1.0 KB -
member_book_create 43 MyISAM utf8_general_ci 2.4 KB -
member_enterprise 20 MyISAM utf8_general_ci 12.4 KB -
member_friend 1 MyISAM utf8_general_ci 2.0 KB -
member_group 358 MyISAM utf8_general_ci 17.0 KB -
member_job 5 MyISAM utf8_general_ci 2.2 KB -
member_manager 7 MyISAM utf8_general_ci 2.2 KB -
member_personal 30 MyISAM utf8_general_ci 35.0 KB -
member_singlepage 6 MyISAM utf8_general_ci 3.1 KB -
product 10 MyISAM utf8_general_ci 2.7 KB -
productclass 1 MyISAM utf8_general_ci 2.0 KB -
recordinfo 24 MyISAM utf8_general_ci 35.3 KB -
siteinfo 0 MyISAM utf8_general_ci 1.0 KB -
sousuo 77 MyISAM utf8_general_ci 4.5 KB -
webmaster 7 MyISAM utf8_general_ci 3.6 KB -
webmaster_config 1 MyISAM utf8_general_ci 2.6 KB -
webmaster_contact 4 MyISAM utf8_general_ci 6.0 KB -
webmaster_grade 4 MyISAM utf8_general_ci 2.1 KB -
webmaster_hr 5 MyISAM utf8_general_ci 26.4 KB -
webmaster_know 3 MyISAM utf8_general_ci 2.2 KB -
webmaster_links 9 MyISAM utf8_general_ci 2.5 KB -
webmaster_links_wap 3 MyISAM utf8_general_ci 2.1 KB -
webmaster_news 3 MyISAM utf8_general_ci 2.2 KB -
webmaster_news_class 2 MyISAM utf8_general_ci 2.1 KB -
webmaster_rights 3 MyISAM utf8_general_ci 2.1 KB -
webmaster_singlepage 4 MyISAM utf8_general_ci 4.3 KB account表结构
id int(11) 否 auto_increment
mid int(11) 是 NULL
gid int(11) 是 NULL
options int(1) 是 1
money double 否 0
times int(11) 是 NULL
odrid int(11) 是 NULL
kid int(11) 是 NULL member表结构
id int(11) UNSIGNED 否 auto_increment
ispersonal int(1) 是 0
loginid varchar(255) utf8_general_ci 是 NULL
password varchar(255) utf8_general_ci 是 NULL
per_books_rights tinyint(1) 否 1
security int(11) UNSIGNED 是 NULL
nickname varchar(255) utf8_general_ci 是 NULL
img varchar(255) utf8_general_ci 是 none.gif
truename text utf8_general_ci 是 NULL
truenamed text utf8_general_ci 是 NULL
sex text utf8_general_ci 是 NULL
age text utf8_general_ci 是 NULL
place text utf8_general_ci 是 NULL
blood text utf8_general_ci 是 NULL
weight text utf8_general_ci 是 NULL
height text utf8_general_ci 是 NULL
lunar text utf8_general_ci 是 NULL
starts text utf8_general_ci 是 NULL
marriage text utf8_general_ci 是 NULL
qual text utf8_general_ci 是 NULL
polotical text utf8_general_ci 是 NULL
brands text utf8_general_ci 是 NULL
products text utf8_general_ci 是 NULL
about text utf8_general_ci 是 NULL
salesphone text utf8_general_ci 是 NULL
customphone text utf8_general_ci 是 NULL
complaintsphone text utf8_general_ci 是 NULL
afteretphone text utf8_general_ci 是 NULL
industry_job text utf8_general_ci 是 NULL
idnum text utf8_general_ci 是 NULL
skills text utf8_general_ci 是 NULL
msn text utf8_general_ci 是 NULL
qq text utf8_general_ci 是 NULL
mail text utf8_general_ci 是 NULL
officephone text utf8_general_ci 是 NULL
fax text utf8_general_ci 是 NULL
mob text utf8_general_ci 是 NULL
homephone text utf8_general_ci 是 NULL
address text utf8_general_ci 是 NULL
birt text utf8_general_ci 是 NULL
work text utf8_general_ci 是 NULL
workex text utf8_general_ci 是 NULL
university text utf8_general_ci 是 NULL
highschool text utf8_general_ci 是 NULL
middleschool text utf8_general_ci 是 NULL
primaryschool text utf8_general_ci 是 NULL
website text utf8_general_ci 是 NULL
netphone text utf8_general_ci 是 NULL
searchkey text utf8_general_ci 是 NULL
lasttim int(11) 是 NULL
lastip varchar(40) utf8_general_ci 是 NULL
iflock int(1) 是 0
recommend int(1) 是 0
serialno int(11) 是 0
count int(11) UNSIGNED 否 0
updatetime int(11) 是 0
searchkeyword longtext utf8_general_ci 是 NULL
products_skill text utf8_general_ci 是 NULL
recordinfo_state tinyint(1) 否 0 key_info表结构
id int(11) UNSIGNED 否 auto_increment
mid int(11) 是 NULL
groupid int(11) 是 NULL
keyname varchar(255) utf8_general_ci 是 NULL
count int(255) 是 0
price double 是 0
times int(12) 是 NULL
rztime int(12) 是 NULL
gje double 是 NULL
1 有关键词但又有足额资金的用户,sql1
2 有关键词但无足额资金或无资金的用户 , sql2
3 无注册关键词但信息内容有相应关键词的用户 sql3(sql1) UNION (sql2) UNION (sql3) ORDER BY xxx DESC
注意sql1,sql2,sql3都为完整的select语句.
id int(11) UNSIGNED 否 auto_increment
mid int(11) 是 NULL
groupid int(11) 是 NULL
keyname varchar(255) utf8_general_ci 是 NULL
count int(255) 是 0
price double 是 0
times int(12) 是 NULL -----这是关键字的注册时间
rztime int(12) 是 NULL -----这是组入账时间
gje double 是 NULL -----这是组金额
union 去除重复数据, union all 无重复取数据
case when 条件1 then 1 else 2 end,
case when 条件2 then 1 else 2 end,
....如果要倒序就把1、2的位置对调