MasterZivshow create table со всех таблиц и запрос давайте.
Что-то я немного сегодня в шоке, запустила план вроде как последнего запроса, но картина немного изменилась. Почему-то в визитах получается 11 записей.
вот план
В общем последний запрос выглядит так
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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 V.BranchID<>1 AND C.TRPID ='70010'
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'
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.
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`),
KEY `TRPID` (`TRPID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
-------
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$$
----------
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$$
|
