miksoft,
вот explain моего запроса
запрос имеет вид
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
explain
SELECT V.VisitID, CME.ClientsMarketingExpID, V.BranchID as BranchID,
V.ClientID, V.`Group` as "Group", V.ClientType, V.ClientSize,
V.VisitType, V.VisitIn as `In`, V.VisitOut as `Out`, V.Balance, V.Rate, V.MaxRisk,
V.Automat, V.MarketingSum, V.MarketingNotes,
V.ChangeTime, V.VersionID, V.UserID, V.Is2ndVisit, V.IsTypicalClient,
CME.Code, CME.Sum, CME.Notes, CME.PlayMachineID,
CME.TimeIn, CME.PlayMachineID, C.GamblerCode,
C.NickName, C.Type, C.TRPID, Date(Date_Format(V.VisitIn, '%Y-%m-%d')) as DateIn, IF(B.BranchName is null, Cast(V.BranchID as char), B.BranchName) as BranchName
FROM Visits V
JOIN Clients C ON V.ClientID=C.ClientID AND V.BranchID=C.BranchID AND C.BranchID<>1
LEFT JOIN ClientsMarketingExp CME ON V.VisitID=CME.VisitID AND V.BranchID=CME.BranchID
LEFT JOIN Branches B ON V.BranchID=B.BranchID
WHERE V.VersionID<>-1 AND V.VisitIn BETWEEN '2013-10-01' AND '2013-10-30' AND C.TRPID ='70010'
ORDER BY V.VisitIn, CME.TimeIn;
структура таблиц
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
delimiter $$
CREATE TABLE `clients` (
`ClientID` int(11) NOT NULL DEFAULT '0',
`BranchID` int(11) NOT NULL DEFAULT '0',
`NickName` varchar(100) DEFAULT NULL,
`Name` varchar(50) DEFAULT NULL,
`Surname` varchar(100) DEFAULT NULL,
`Activity` tinyint(1) DEFAULT NULL,
`GamblerCode` varchar(20) DEFAULT NULL,
`Group` varchar(10) DEFAULT NULL,
`Type` varchar(10) DEFAULT NULL,
`Size` varchar(10) DEFAULT NULL,
`ChangeTime` datetime DEFAULT NULL,
`CreateTime` datetime DEFAULT NULL,
`VersionID` int(11) DEFAULT NULL,
`UserID` varchar(20) DEFAULT NULL,
`UserZales` varchar(50) DEFAULT NULL,
`IsTypicalClient` tinyint(1) DEFAULT NULL,
`Deleted` tinyint(1) DEFAULT NULL,
`DeleteDate` datetime DEFAULT NULL,
`SortOrder` int(11) DEFAULT NULL,
`TRPID` varchar(10) DEFAULT NULL,
`Birthday` datetime DEFAULT NULL,
`NameDay` datetime DEFAULT NULL,
`Age` tinyint(4) DEFAULT NULL,
`Occupation` varchar(100) DEFAULT NULL,
`TRPReport` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`ClientID`,`BranchID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
--------
delimiter $$
CREATE TABLE `visits` (
`VisitID` int(11) NOT NULL DEFAULT '0',
`BranchID` int(11) NOT NULL DEFAULT '0',
`ClientID` int(11) DEFAULT NULL,
`Group` varchar(10) DEFAULT NULL,
`ClientType` varchar(10) DEFAULT NULL,
`ClientSize` varchar(10) DEFAULT NULL,
`VisitType` varchar(1) DEFAULT NULL,
`VisitIN` datetime DEFAULT NULL,
`VisitOUT` datetime DEFAULT NULL,
`Balance` decimal(9,2) DEFAULT NULL,
`Automat` varchar(200) DEFAULT NULL,
`MarketingSum` decimal(9,2) DEFAULT NULL,
`MarketingNotes` varchar(250) DEFAULT NULL,
`ChangeTime` datetime DEFAULT NULL,
`VersionID` int(11) DEFAULT NULL,
`UserID` varchar(20) DEFAULT NULL,
`UserZales` varchar(50) DEFAULT NULL,
`Is2ndVisit` tinyint(1) DEFAULT NULL,
`IsTypicalClient` tinyint(1) DEFAULT NULL,
`Rate` decimal(9,2) DEFAULT NULL,
`MaxRisk` decimal(9,2) DEFAULT NULL,
PRIMARY KEY (`VisitID`,`BranchID`),
KEY `VisitIN` (`VisitIN`),
KEY `VisitIN_ClientID_BranchID` (`VisitIN`,`ClientID`,`BranchID`),
KEY `ClientID_BranchID` (`ClientID`,`BranchID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
----------
delimiter $$
CREATE TABLE `clientsmarketingexp` (
`ClientsMarketingExpID` int(11) NOT NULL DEFAULT '0',
`BranchID` int(11) NOT NULL DEFAULT '0',
`ClientID` int(11) DEFAULT NULL,
`NickName` varchar(100) DEFAULT NULL,
`GamblerCode` varchar(20) DEFAULT NULL,
`Code` varchar(5) DEFAULT NULL,
`ChangeTime` datetime DEFAULT NULL,
`CreateTime` datetime DEFAULT NULL,
`TimeIn` datetime DEFAULT NULL,
`PlayMachineID` tinytext,
`Sum` decimal(9,2) DEFAULT NULL,
`UserID` varchar(20) DEFAULT NULL,
`UserZales` varchar(50) DEFAULT NULL,
`VisitID` int(11) DEFAULT NULL,
`BranchName` varchar(25) DEFAULT NULL,
`VersionID` int(11) DEFAULT NULL,
`Notes` varchar(200) DEFAULT NULL,
`Parbaudits` varchar(100) DEFAULT NULL,
`VideoPiezimes` varchar(100) DEFAULT NULL,
`Rate` decimal(9,2) DEFAULT NULL,
`MaxRisk` decimal(9,2) DEFAULT NULL,
PRIMARY KEY (`ClientsMarketingExpID`,`BranchID`),
KEY `VisitID_BranchID` (`VisitID`,`BranchID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
|
