mysql запрос : что можно сделать?(+)
базу делал не я. имею то, что имею, что тут можно сделать:
Код:
CREATE TABLE phone_prices (
ID int(11) NOT NULL auto_increment,
ParentID int(11) NOT NULL default '0',
Model mediumtext NOT NULL,
Price decimal(10,2) default '000000000.00',
Comments mediumtext,
DataUpload date NOT NULL default '0000-00-00',
Cities mediumtext,
Url varchar(255) default NULL,
TraidersOrd int(11) NOT NULL default '0',
SuperP int(3) NOT NULL default '0',
SpecialP int(3) NOT NULL default '0',
Delivery int(3) NOT NULL default '0',
Office int(3) NOT NULL default '0',
Certificate int(3) NOT NULL default '0',
Discount int(3) NOT NULL default '0',
TraiderTrust int(3) NOT NULL default '0',
StatusRow int(3) NOT NULL default '0',
Accessory int(3) NOT NULL default '0',
White int(3) NOT NULL default '0',
Credit int(3) NOT NULL default '0',
PRIMARY KEY (ID),
KEY ParentID (ParentID),
FULLTEXT KEY Model (Model),
FULLTEXT KEY Comments (Comments),
KEY Price (Price),
KEY TraidersOrd (TraidersOrd),
FULLTEXT KEY Cities (Cities),
KEY SuperP (SuperP),
KEY SpecialP (SpecialP),
KEY Delivery (Delivery),
KEY Office (Office),
KEY Certificate (Certificate),
KEY Discount (Discount),
KEY TraiderTrust (TraiderTrust),
KEY StatusRow (StatusRow),
KEY Accessory (Accessory),
KEY White (White),
KEY Credit (Credit)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Структура таблицы `traiders`
#
CREATE TABLE traiders (
ID int(11) NOT NULL auto_increment,
DomenChoose varchar(255) NOT NULL default '',
Name varchar(255) NOT NULL default '',
YourName mediumtext,
Login varchar(255) default NULL,
Password varchar(100) default NULL,
Email varchar(100) default NULL,
Phone varchar(50) default NULL,
Site varchar(255) default NULL,
Description mediumtext,
Adress mediumtext,
Metro mediumtext,
Icq varchar(255) default NULL,
DataFrom date default NULL,
DataTo date default NULL,
TrustHim int(3) NOT NULL default '0',
DataUpload date NOT NULL default '0000-00-00',
Ord int(11) NOT NULL default '0',
TraiderColor varchar(10) default NULL,
HaveShop int(3) NOT NULL default '0',
ShopUrl varchar(255) default NULL,
Logo varchar(255) default NULL,
TraiderTrust int(3) NOT NULL default '0',
Office int(3) NOT NULL default '0',
SuperP int(3) NOT NULL default '0',
SpecialP int(3) NOT NULL default '0',
Region int(3) default '0',
BannersLogin varchar(255) default NULL,
BannersPassword varchar(255) default NULL,
BaseCity int(11) NOT NULL default '0',
InfoShow int(3) NOT NULL default '0',
HaveXmlPhone int(3) default '0',
XmlUrlPhone mediumtext,
HaveXmlKpk int(3) default '0',
XmlUrlKpk mediumtext,
HaveXmlDect int(3) default '0',
XmlUrlDect mediumtext,
HaveXmlDigit int(3) default '0',
XmlUrlDigit mediumtext,
HaveXmlMp3 int(3) default '0',
XmlUrlMp3 mediumtext,
PRIMARY KEY (ID),
FULLTEXT KEY Login (Login),
FULLTEXT KEY Name (Name),
KEY TrustHim (TrustHim),
KEY Ord (Ord),
KEY HaveShop (HaveShop),
KEY TraiderTrust (TraiderTrust),
KEY Office (Office),
KEY SuperP (SuperP),
KEY SpecialP (SpecialP),
FULLTEXT KEY YourName (YourName),
FULLTEXT KEY DomenChoose (DomenChoose),
KEY Region (Region),
KEY BaseCity (BaseCity),
KEY InfoShow (InfoShow)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Структура таблицы `traiders_listing`
#
CREATE TABLE traiders_listing (
ID int(11) NOT NULL auto_increment,
Traider int(11) NOT NULL default '0',
DomenName varchar(255) NOT NULL default '',
DataFrom date NOT NULL default '0000-00-00',
DataTo date NOT NULL default '0000-00-00',
Specially int(3) NOT NULL default '0',
SepOn int(3) NOT NULL default '0',
SepColor varchar(255) default NULL,
Ord int(11) NOT NULL default '0',
Upload date default NULL,
Cities text,
PRIMARY KEY (ID),
KEY Traider (Traider),
FULLTEXT KEY DomenName (DomenName),
KEY DataFrom (DataFrom),
KEY DataTo (DataTo),
KEY Specially (Specially),
KEY SepOn (SepOn),
FULLTEXT KEY SepColor (SepColor),
KEY Ord (Ord),
KEY Upload (Upload),
FULLTEXT KEY Cities (Cities)
) TYPE=MyISAM;
смысл запроса - выбрать цены на телефоны, а также информацию о продавце. рандомно, 5 шт.
проверки:
продавец должен быть "активен"(текущая дата в диапозоне между включением и выключением продавца).
связь модель-цена: модель в тектсовом виде.
запрос:
Код:
select pp.Price, pp.Url,
trds.ID, trds.Name, trds.Site,
pp.DataUpload
FROM phone_prices as pp
inner join traiders_listing as tl
on pp.ParentID = tl.Traider
inner join traiders as trds
on tl.Traider = trds.ID
where pp.Model = 'Alcatel OT 153'
and
tl.DataFrom <= '2005-01-18'
and
tl.DataTo > '2005-01-18'
order by rand() limit 5
|
