SQLinfo.ru - Все о MySQL
Новости Статьи Форум Услуги О проекте

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

Страниц: 1

 

#1 06.06.2016 18:11:36

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Требуется помощь в написании/улучшении sql-запроса

Имеется два запроса:
1-ый запрос - извлекает из базы список всех когда-либо зарегистрированных пользователей (активных, не заблокированных),
2-ой запрос - извлекает из базы список пользователей (активных, не заблокированных), которые хотя что-то когда-либо бронировали (отель, автомобиль и т.д.)

Задача: нужно написать третий запрос или новый самостоятельный запрос, который бы позволил извлечь из первых двух запросов - пользователей, которые никогда не выполняли бронирование.

Подскажите, как можно реализовать, желательно с примером?

1-ый запрос:

SELECT DISTINCT
users.id,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
users
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
-- INNER JOIN bookingsHistory ON (users.id = bookingsHistory.userId)
WHERE
users.activated = 1 AND
users.enabled = 1
ORDER BY
users.id ASC;





2-ой запрос:

SELECT DISTINCT
bookingsHistory.userId,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
bookingsHistory
INNER JOIN users ON (bookingsHistory.userId = users.id)
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
WHERE
users.activated = 1 AND
users.enabled = 1 AND
bookingsHistory.userId<>0 AND
bookingsHistory.action='booking'
ORDER BY
bookingsHistory.userId ASC

Неактивен

 

#2 06.06.2016 18:24:06

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в написании/улучшении sql-запроса

Предполагаю, что нужно двигаться в этом направлении (но пока не хватает навыков довести запрос до работоспособного):


SELECT users . *
FROM users left join
(
SELECT DISTINCT userId
FROM bookingsHistory, users
WHERE
users.id=bookingsHistory.userId AND
users.activated = 1 AND
users.enabled = 1 AND
bookingsHistory.userId<>0

) t using(id) WHERE t.id IS NULL
ORDER BY id
LIMIT 0 , 20

Неактивен

 

#3 06.06.2016 18:43:29

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в написании/улучшении sql-запроса

или возможно можно так:

SELECT *
FROM users A
LEFT JOIN bookingsHistory B
ON A.id = B.userId
WHERE B.userId IS NULL
 


не уверен, что сделал правильно, т.к. запрос долго выполняется (ни разу не дождался до конца). в bookingsHistory около 600 000 строк.
EXPLAIN SELECT *
FROM users A
LEFT JOIN bookingsHistory B
ON A.id = B.userId
WHERE B.userId IS NULL


id    Select_type table    type                    rows      Extra
1    SIMPLE    A    ALL                    5146   
1    SIMPLE    B    ALL                    550486    Using where; Not exists

Неактивен

 

#4 06.06.2016 18:45:09

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: Требуется помощь в написании/улучшении sql-запроса

в лоб:
select .. from (первый запрос) t1 left join (второй запрос) t2 on (`идентификатор пользователя`) where t2.`идентификатор пользователя` is null;

но производительность будет плохая. Приведите пример тестовых данных (create table, insert into) для ваших таблиц и какой должен быть результат, тогда можно будет посоветовать что-нибудь более оптимальное.

Неактивен

 

#5 06.06.2016 18:52:59

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в написании/улучшении sql-запроса

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID ',
  `agencyId` int(11) unsigned NOT NULL COMMENT 'ID агентства',
  `roleId` int(11) unsigned NOT NULL COMMENT 'Ключ роли',
  `login` varchar(20) NOT NULL COMMENT 'Логин',
  `password` varchar(32) NOT NULL COMMENT 'Пароль',
  `apiKey` varchar(32) NOT NULL COMMENT 'Ключ для работы с API',
  `salt` varchar(32) NOT NULL COMMENT 'Соль',
  `fullname` varchar(100) NOT NULL COMMENT 'ФИО',
  `email` varchar(100) NOT NULL COMMENT 'Электропочта',
  `phone` varchar(50) DEFAULT NULL COMMENT 'Телефон',
  `masterTourId` int(11) unsigned DEFAULT NULL COMMENT 'ID пользователя в Мастер Туре',
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Статус (0 - не активен, 1 - активен)',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Выключатель (1 - включен, 2 - выключен)',
  `isLeadManager` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Ведущий менеджер',
  `isCurator` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Куратор агентства',
  `canBookPenaltyItems` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Может бронировать штрафные заказы',
  `canEditOrdersOfMyCompany` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Может изменять заказы своей компании',
  `canEditOrdersOfMyNetwork` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Может изменять заказы своей сети',
  `createTime` int(11) unsigned NOT NULL COMMENT 'Время создания',
  `updateTime` int(11) unsigned NOT NULL COMMENT 'Время изменения',
  `oldId` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  UNIQUE KEY `apiKey` (`apiKey`),
  KEY `agencyId` (`agencyId`),
  KEY `canBookPenaltyOrders` (`canBookPenaltyItems`),
  KEY `canEditOrdersOfMyCompany` (`canEditOrdersOfMyCompany`),
  KEY `canEditOrdersOfMyNetwork` (`canEditOrdersOfMyNetwork`),
  KEY `enabled` (`enabled`),
  KEY `isLeadManager` (`isLeadManager`),
  KEY `roleId` (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=5502 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=910 COMMENT='Пользователи';

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', '1', '10', 'admin', '9d05b68211be3ecb6e46a96b37e50195', '9d05b68211be3ecb6e46a96b37e50195', '5155715fc42626.40951715', 'Администратор', 'admin@mag.travel', '+7 (495) 780-88-80', '20615', '1', '1', '1', '0', '0', '0', '0', '1344934529', '1457958477', '0');
INSERT INTO `users` VALUES ('2', '2', '1', 'rito4_ka', '0a0f69d7dce4455668a90bd2bebcfb3e', 'e0e6056e7d07cfb5cd47bc1f084f4ddb', '5719f1be8d56b0.38434397', 'Гость', 'info@online-express.ru', '+7 (495) 780-88-80', null, '1', '1', '0', '0', '0', '0', '0', '1344934529', '1461318078', '35');
INSERT INTO `users` VALUES ('3', '1', '10', 'vadim', 'de369d88ce87bce829ef28e77ee82802', 'a8a78d771322eda685d6a85af28a65cd', '536b9c03c9b756.99728003', 'Шпильман Вадим', 'v.shpilman@online-express.ru', '+7 (495) 780-88-80 доб. 401', '0', '0', '0', '0', '0', '1', '1', '1', '1344934530', '1462977893', '1119');
INSERT INTO `users` VALUES ('6', '1', '11', 'slava', 'aeac104c14b080116afc884973602c3c', 'a175143b67e4d12f4951c23da0d8e358', '545c82f61accd3.24766403', 'Вячеслав Шатровский', 'v.shatrovsky@online-express.ru', '+7 (495) 780-88-80 доб. 409', '0', '0', '0', '0', '0', '1', '1', '1', '1344938577', '1457958511', '1');
INSERT INTO `users` VALUES ('7', '1', '11', 'koss', '43ee23794837664f64d7947f6b4c0663', '43ee23794837664f64d7947f6b4c0663', '502a2a49e012d2.26268862', 'Константин Петров', 'k.petrov@online-express.ru', '+7 960 47 67 007', null, '0', '0', '0', '0', '0', '0', '0', '1344939485', '1461916965', '0');
INSERT INTO `users` VALUES ('8', '1', '10', 'eugenia', '2b46c572058052cf83795d97c6039116', 'fb73eab8b4d8565d45267cda06cf0f53', '536b9c03c8ba17.18229644', 'Федорова Евгения', 'e.fedorova@online-express.ru', '+7 (495) 780-88-00 доб. 403', null, '0', '0', '0', '0', '0', '0', '0', '1344939574', '1461917012', '1003');
INSERT INTO `users` VALUES ('9', '1', '10', 'rita', 'be0af50070fea9968036d922eb195e9f', 'b6b8c108677db9c5b136e561205b77df', '536b9c03c8f9b0.72486378', 'Губкина Маргарита', 'm.gubkina@online-express.ru', ' +79818455837 мобильный +7(812) 441-31-67, рабочий', null, '0', '0', '0', '0', '1', '0', '0', '1344939676', '1403775115', '1004');
INSERT INTO `users` VALUES ('10', '1', '10', 'ivansirotkin', '87f62d72fb03145b61aebce0275ff231', 'df605e3a5890a54919c452c6cfe1678b', '572377d36a43e1.53149787', 'Сироткин Иван', 'ivan.sirotkin@visastation.ru', '+7 (495) 925-23-45 доб.255', null, '1', '1', '0', '0', '0', '0', '0', '1344939778', '1461942227', '1007');
INSERT INTO `users` VALUES ('11', '1', '10', 'ilya', '7c41cc12fd898718a1f72ed94224c677', '26309e67bee15c9e45eb11e01cf78dcc', '536b9c03ca3778.73607164', 'Каркашов Илья', 'i.korkashov@online-express.ru', '+7 (495) 780-88-80 доб. 413', null, '0', '0', '0', '0', '0', '0', '0', '1344939854', '1403775116', '1287');
INSERT INTO `users` VALUES ('12', '1', '10', 'zhukova', 'a303f6b64b0ec9f1b51ef098c3929906', 'b6b543ff9e584f9bfe643e4514e4f1c3', '536b9c03c939b0.79838568', 'Жукова Наталья', 'n.zhukova@online-express.ru', '+7 (495) 780-88-80 доб. 427', null, '0', '0', '0', '0', '0', '0', '0', '1344939955', '1403775116', '1005');
INSERT INTO `users` VALUES ('13', '1', '10', 'demidenko', '4023157016de52a6392fb88fc18dec9e', '886595083f613b94a6914eb440d0bdea', '53c9146905d944.36072659', 'Демиденко Ольга', 'D.Shabinsky@online-express.ru', '+7 (495) 780-88-00 доб. 429', null, '0', '1', '0', '0', '0', '0', '0', '1344940077', '1461917037', '1594');
INSERT INTO `users` VALUES ('14', '1', '10', 'taras', 'bf03c1f254c051cb051eedcdefb650bb', '1a7cf6b38de272322b8bd768859cdf0c', '5733445044c488.75141624', 'Кобищанов Тарас Юрьевич', 'taras.kobishchanov@r-express.ru', '+7 (495) 925 6699 доб. 777', '20615', '1', '1', '0', '0', '1', '0', '0', '1345025611', '1462977616', '30');
INSERT INTO `users` VALUES ('16', '3', '7', 'megatec', '654d6b7ed6c0ea4518410ad2e4f0be5d', '9165583dd7fcff5b31d27778139e64f3', '559b9c099a64b0.34843890', 'Головченко Виталий', 'ugva@mag.travel', '+7 (495) 602-00-20', null, '1', '1', '0', '0', '0', '0', '0', '1345025611', '1447166974', '0');
INSERT INTO `users` VALUES ('17', '4', '7', 'vedimsk', 'fa0c27e61c07b356316bcacdc5eea022', 'fa0c27e61c07b356316bcacdc5eea022', '50acb9ae7baae8.61702535', 'Веди Тур Групп', 'okunew@megatec.ru', '+7 (495) 228-32-84', null, '1', '1', '0', '0', '0', '0', '0', '1345025611', '1413902581', '0');
INSERT INTO `users` VALUES ('18', '1', '10', 'dasha', '40cdb4b3e62b16e6ac6b1a8725db0da7', 'ce45a436696716b810e1c812c40ff857', '536799d213b6c6.25729928', 'Дарья Азаронок', 'd.azaronok@online-express.ru', '+375 29 501 94 72', '20615', '0', '1', '0', '0', '0', '0', '0', '1345025611', '1461917067', '0');
INSERT INTO `users` VALUES ('19', '1', '1', 'dmitry', '8291c36620ee2f132660d49', '7212f512b8050be4af7aa1227c192df4', '54e9d21bf08951.55858358', 'Дмитрий Шабинский', 'd.shabinsky@online-express.ru', '+7 (495) 780-88-80 доб. 438', '20615', '0', '0', '0', '0', '1', '0', '0', '1345025611', '1424609819', '2166');
INSERT INTO `users` VALUES ('21', '5', '7', 'vizitworld', '4316c6fdb61a399e39030f9227e469e3', '4316c6fdb61a399e39030f9227e469e3', '50f01aaee2ec41.63158837', 'Сутулин Андрей', 'okunev@mag.travel', '+7 (495) 228-32-84', null, '1', '1', '0', '0', '0', '0', '0', '1345025611', '1357912813', '0');
INSERT INTO `users` VALUES ('23', '7', '7', 'dankotest', '1c0a7ddc863e1d94a5b78b2ce6cd0e83', '1c0a7ddc863e1d94a5b78b2ce6cd0e83', '512373b1837fa8.54087549', 'Данко', 'okunev@mag.travel', '+7 (495) 228-32-84', null, '1', '1', '0', '0', '0', '0', '0', '1344938577', '1361277873', '0');
INSERT INTO `users` VALUES ('25', '6', '7', 'devisutest', '23ce1f9291a9f27e7b5be878f2d63491', '23ce1f9291a9f27e7b5be878f2d63491', '51237466b52706.63288010', 'Девизу', 'okunev@mag.travel', '+7 (495) 228-32-84', null, '1', '1', '0', '0', '0', '0', '0', '1344938577', '1361278054', '0');
INSERT INTO `users` VALUES ('26', '9', '7', 'kapriz', '3d036ec19f7d34006fa408f802ec0e08', '3d036ec19f7d34006fa408f802ec0e08', '5152ffe41ccbc6.42163096', 'Торошин Сергей', 'kapriz@kapriz-tourism.ru', '+7 (912) 249-35-21', null, '1', '1', '0', '0', '0', '0', '0', '1344938577', '1361278054', '0');
INSERT INTO `users` VALUES ('27', '10', '7', 'simTravel', 'e16f990f89e2fafa2b097c31e8c57a02', 'c72717e39fc4d86d8955f958bd21a741', '56c7194fb59a00.25626661', 'Компания для тестирования (администратор компании)', 'it@online-express.ru', '(495) 780-88-80', null, '1', '1', '0', '0', '0', '0', '0', '1364553985', '1438596115', '0');
INSERT INTO `users` VALUES ('28', '11', '5', 'test-agency', '0789882eba908f08e918049e01ea7cdb', '0d990716ec5aba142502cccb189f01cf', '537cddec137747.50217961', 'Агентство для тестирования (администратор агентства)', 'v.shatrovsky@online-express.ru', '(495) 780-88-00', null, '1', '1', '0', '0', '0', '0', '0', '1364554079', '1462543836', '0');
INSERT INTO `users` VALUES ('29', '12', '3', 'test-subagency', '0789882eba908f08e918049e01ea7cdb', '135df78fb957138e7dbad2cacc70ae1c', '537cddec137747.50217961', 'Субагентство для тестирования (администратор субагентства)', 'it@online-express.ru', '(495) 780-88-00', null, '1', '1', '0', '0', '0', '0', '0', '1364554658', '1400693465', '0');
INSERT INTO `users` VALUES ('30', '1', '10', 'irinadmitrova', 'f87e425be83d44eb6fe753aaa97f26df', 'bad69d93f248cae4e462847288a9bafc', '54db66cc2cf064.49160189', 'Дмитрова Ирина', 'Irina.Dmitrova@r-express.ru', '+7 (495) 925-66-99 доб. 757', null, '0', '0', '0', '0', '0', '0', '0', '1364554658', '1461920508', '1770');


CREATE TABLE `bookingsHistory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) unsigned NOT NULL,
  `bookingId` int(11) unsigned NOT NULL,
  `action` varchar(10) NOT NULL,
  `xmlRequest` text,
  `xmlResponse` text,
  `createTime` int(11) unsigned NOT NULL,
  `updateTime` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=521023 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=3488;

-- ----------------------------
-- Records of bookingsHistory
-- ----------------------------
INSERT INTO `bookingsHistory` VALUES ('100000', '3', '60000', 'booking', '<?xml version=\"1.0\"?>\n<PurchaseConfirmRQ xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseConfirmRQ.xsd\" xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" echoToken=\"String\"><Language>ENG</Language><Credentials><User>RUSSIANEXPRU81934</User><Password>RUSSIANEXPRU81934</Password></Credentials><ConfirmationData purchaseToken=\"63021406379\"><Holder type=\"AD\"><Name>VAdsd</Name><LastName>tero</LastName></Holder><AgencyReference>ONLINE EXPRESS</AgencyReference><ConfirmationServiceDataList><ServiceData xsi:type=\"ConfirmationServiceDataHotel\" SPUI=\"459#H#1\"><CustomerList><Customer type=\"AD\"><CustomerId>1</CustomerId><Age>41</Age><Name>VAdsd</Name><LastName>tero</LastName></Customer></CustomerList></ServiceData></ConfirmationServiceDataList></ConfirmationData></PurchaseConfirmRQ>\n', '<PurchaseConfirmRS xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseConfirmRS.xsd\" echoToken=\"String\"><AuditData><ProcessTime>485</ProcessTime><Timestamp>2013-05-13 14:14:07.613</Timestamp><RequestHost>5.9.101.87:87</RequestHost><ServerName>LIVE</ServerName><ServerId>43</ServerId><SchemaRelease>2005/06</SchemaRelease><HydraCoreRelease>2.0.201304221213</HydraCoreRelease><HydraEnumerationsRelease>1.0.201304221213</HydraEnumerationsRelease><MerlinRelease>N/A</MerlinRelease></AuditData><Purchase purchaseToken=\"63021406379\" timeToExpiration=\"1799986\"><Reference><FileNumber>58442</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>BOOKING</Status><Agency><Code>81934</Code><Branch>1</Branch></Agency><Language>ENG</Language><CreationDate date=\"20130513\"/><CreationUser>RUSSIANEXPRU81934</CreationUser><Holder type=\"AD\"><Age>0</Age><Name>VADSD</Name><LastName>TERO</LastName></Holder><AgencyReference>ONLINE EXPRESS</AgencyReference><ServiceList><Service xsi:type=\"ServiceHotel\" SPUI=\"459#H#1\"><Reference><FileNumber>58442-H1</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>CONFIRMED</Status><ContractList><Contract><Name>GR-ALL</Name><IncomingOffice code=\"459\"></IncomingOffice></Contract></ContractList><Supplier name=\"HOTELBEDS PRODUCT,S.L.U.\" vatNumber=\"B38877676\"/><CommentList><Comment type=\"SERVICE\">\n</Comment></CommentList><DateFrom date=\"20130527\"/><DateTo date=\"20130528\"/><Currency code=\"EUR\">Euro</Currency><TotalAmount>22.640</TotalAmount><AdditionalCostList><AdditionalCost type=\"AG_COMMISSION\"><Price><Amount>0.000</Amount></Price></AdditionalCost><AdditionalCost type=\"COMMISSION_VAT\"><Price><Amount>0.000</Amount></Price></AdditionalCost></AdditionalCostList><ModificationPolicyList><ModificationPolicy>Cancellation</ModificationPolicy><ModificationPolicy>Confirmation</ModificationPolicy><ModificationPolicy>Modification</ModificationPolicy></ModificationPolicyList><HotelInfo xsi:type=\"ProductHotel\"><Code>48912</Code><Name>Akord</Name><Category type=\"SIMPLE\" code=\"3EST\">3 STARS</Category><Destination type=\"SIMPLE\" code=\"SOF\"><Name>Sofia</Name><ZoneList><Zone type=\"SIMPLE\" code=\"1\">Sofia</Zone></ZoneList></Destination></HotelInfo><AvailableRoom><HotelOccupancy><RoomCount>1</RoomCount><Occupancy><AdultCount>1</AdultCount><ChildCount>0</ChildCount><GuestList><Customer type=\"AD\"><CustomerId>1</CustomerId><Age>41</Age><Name>VAdsd</Name><LastName>tero</LastName></Customer></GuestList></Occupancy></HotelOccupancy><HotelRoom SHRUI=\"Puyt4by0bP2o+Ms+erqiaw==\" availCount=\"1\" status=\"CONFIRMED\"><Board type=\"SIMPLE\" code=\"BB-E10\">BED AND BREAKFAST</Board><RoomType type=\"SIMPLE\" code=\"SGL-E10\" characteristic=\"ST\">SINGLE STANDARD</RoomType><Price><Amount>22.640</Amount></Price><CancellationPolicy><Price><Amount>22.640</Amount><DateTimeFrom date=\"20130524\" time=\"2359\"/><DateTimeTo date=\"20130527\"/></Price></CancellationPolicy><HotelRoomExtraInfo><ExtendedData><Name>INFO_ROOM_AGENCY_BOOKING_STATUS</Name><Value>O</Value></ExtendedData><ExtendedData><Name>INFO_ROOM_INCOMING_BOOKING_STATUS</Name><Value>O</Value></ExtendedData></HotelRoomExtraInfo></HotelRoom></AvailableRoom></Service></ServiceList><Currency code=\"EUR\"></Currency><PaymentData><PaymentType code=\"C\"></PaymentType><Description>The total amount for this pro-forma invoice should be made in full to Hotelbeds, S.L.U, Bank: CITIBANK(Citigroup Centre, Canary Wharf, London, E14 5LB. United Kingdom) Account:GB93 CITI 1850 0811 2527 71,  SWIFT:CITIGB2L,  7 days prior to clients arrival (except group bookings with fixed days in advance at the time of the confirmation) . Please indicate our reference number when making payment. Thank you for your cooperation.</Description></PaymentData><TotalPrice>22.640</TotalPrice></Purchase></PurchaseConfirmRS>\n', '1368447247', '1463115097');
INSERT INTO `bookingsHistory` VALUES ('100001', '3', '60000', 'check', '<?xml version=\"1.0\"?>\n<PurchaseDetailRQ xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseDetailRQ.xsd\" xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" echoToken=\"DummyEchoToken\"><Language>ENG</Language><Credentials><User>RUSSIANEXPRU81934</User><Password>RUSSIANEXPRU81934</Password></Credentials><PurchaseReference><FileNumber>58442</FileNumber><IncomingOffice code=\"459\"/></PurchaseReference></PurchaseDetailRQ>\n', '<PurchaseDetailRS xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseDetailRS.xsd\" echoToken=\"DummyEchoToken\"><AuditData><ProcessTime>118</ProcessTime><Timestamp>2013-05-13 15:09:05.490</Timestamp><RequestHost>5.9.101.87:87</RequestHost><ServerName>LIVE</ServerName><ServerId>41</ServerId><SchemaRelease>2005/06</SchemaRelease><HydraCoreRelease>2.0.201304221213</HydraCoreRelease><HydraEnumerationsRelease>1.0.201304221213</HydraEnumerationsRelease><MerlinRelease>N/A</MerlinRelease></AuditData><Purchase purchaseToken=\"41030905338\" timeToExpiration=\"1799974\"><Reference><FileNumber>58442</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>BOOKING</Status><Agency><Code>81934</Code><Branch>1</Branch></Agency><Language>ENG</Language><CreationDate date=\"20130513\"/><CreationUser>RUSSIANEXPRU81934</CreationUser><Holder type=\"AD\"><Age>0</Age><Name>VADSD</Name><LastName>TERO</LastName></Holder><AgencyReference>ONLINE EXPRESS</AgencyReference><ServiceList><Service xsi:type=\"ServiceHotel\" SPUI=\"459#H#1\"><Reference><FileNumber>58442-H1</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>CONFIRMED</Status><ContractList><Contract><Name>GR-ALL</Name><IncomingOffice code=\"459\"></IncomingOffice></Contract></ContractList><Supplier name=\"HOTELBEDS PRODUCT,S.L.U.\" vatNumber=\"B38877676\"/><CommentList><Comment type=\"SERVICE\">\n</Comment></CommentList><DateFrom date=\"20130527\"/><DateTo date=\"20130528\"/><Currency code=\"EUR\">Euro</Currency><TotalAmount>22.640</TotalAmount><AdditionalCostList><AdditionalCost type=\"AG_COMMISSION\"><Price><Amount>0.000</Amount></Price></AdditionalCost><AdditionalCost type=\"COMMISSION_VAT\"><Price><Amount>0.000</Amount></Price></AdditionalCost></AdditionalCostList><ModificationPolicyList><ModificationPolicy>Cancellation</ModificationPolicy><ModificationPolicy>Confirmation</ModificationPolicy><ModificationPolicy>Modification</ModificationPolicy></ModificationPolicyList><HotelInfo xsi:type=\"ProductHotel\"><Code>48912</Code><Name>Akord</Name><Category type=\"SIMPLE\" code=\"3EST\">3 STARS</Category><Destination type=\"SIMPLE\" code=\"SOF\"><Name>Sofia</Name><ZoneList><Zone type=\"SIMPLE\" code=\"1\">Sofia</Zone></ZoneList></Destination></HotelInfo><AvailableRoom><HotelOccupancy><RoomCount>1</RoomCount><Occupancy><AdultCount>1</AdultCount><ChildCount>0</ChildCount><GuestList><Customer type=\"AD\"><CustomerId>1</CustomerId><Age>41</Age><Name>VAdsd</Name><LastName>tero</LastName></Customer></GuestList></Occupancy></HotelOccupancy><HotelRoom SHRUI=\"Puyt4by0bP2o+Ms+erqiaw==\" availCount=\"1\" status=\"CONFIRMED\"><Board type=\"SIMPLE\" code=\"BB-E10\">BED AND BREAKFAST</Board><RoomType type=\"SIMPLE\" code=\"SGL-E10\" characteristic=\"ST\">SINGLE STANDARD</RoomType><Price><Amount>22.640</Amount></Price><CancellationPolicy><Price><Amount>22.640</Amount><DateTimeFrom date=\"20130524\" time=\"2359\"/><DateTimeTo date=\"20130527\"/></Price></CancellationPolicy><HotelRoomExtraInfo><ExtendedData><Name>INFO_ROOM_AGENCY_BOOKING_STATUS</Name><Value>O</Value></ExtendedData><ExtendedData><Name>INFO_ROOM_INCOMING_BOOKING_STATUS</Name><Value>O</Value></ExtendedData></HotelRoomExtraInfo></HotelRoom></AvailableRoom></Service></ServiceList><Currency code=\"EUR\"></Currency><PaymentData><PaymentType code=\"C\"></PaymentType><Description>The total amount for this pro-forma invoice should be made in full to Hotelbeds, S.L.U, Bank: CITIBANK(Citigroup Centre, Canary Wharf, London, E14 5LB. United Kingdom) Account:GB93 CITI 1850 0811 2527 71,  SWIFT:CITIGB2L,  7 days prior to clients arrival (except group bookings with fixed days in advance at the time of the confirmation) . Please indicate our reference number when making payment. Thank you for your cooperation.</Description></PaymentData><TotalPrice>22.640</TotalPrice></Purchase></PurchaseDetailRS>\n', '1368450545', '1463115097');
INSERT INTO `bookingsHistory` VALUES ('100002', '3', '60000', 'check', '<?xml version=\"1.0\"?>\n<PurchaseDetailRQ xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseDetailRQ.xsd\" xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" echoToken=\"DummyEchoToken\"><Language>ENG</Language><Credentials><User>RUSSIANEXPRU81934</User><Password>RUSSIANEXPRU81934</Password></Credentials><PurchaseReference><FileNumber>58442</FileNumber><IncomingOffice code=\"459\"/></PurchaseReference></PurchaseDetailRQ>\n', '<PurchaseDetailRS xmlns=\"http://www.hotelbeds.com/schemas/2005/06/messages\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.hotelbeds.com/schemas/2005/06/messages PurchaseDetailRS.xsd\" echoToken=\"DummyEchoToken\"><AuditData><ProcessTime>321</ProcessTime><Timestamp>2013-05-13 15:28:14.890</Timestamp><RequestHost>5.9.101.87:87</RequestHost><ServerName>LIVE</ServerName><ServerId>53</ServerId><SchemaRelease>2005/06</SchemaRelease><HydraCoreRelease>2.0.201304221213</HydraCoreRelease><HydraEnumerationsRelease>1.0.201304221213</HydraEnumerationsRelease><MerlinRelease>N/A</MerlinRelease></AuditData><Purchase purchaseToken=\"53032814995\" timeToExpiration=\"1799980\"><Reference><FileNumber>58442</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>BOOKING</Status><Agency><Code>81934</Code><Branch>1</Branch></Agency><Language>ENG</Language><CreationDate date=\"20130513\"/><CreationUser>RUSSIANEXPRU81934</CreationUser><Holder type=\"AD\"><Age>0</Age><Name>VADSD</Name><LastName>TERO</LastName></Holder><AgencyReference>ONLINE EXPRESS</AgencyReference><ServiceList><Service xsi:type=\"ServiceHotel\" SPUI=\"459#H#1\"><Reference><FileNumber>58442-H1</FileNumber><IncomingOffice code=\"459\"></IncomingOffice></Reference><Status>CONFIRMED</Status><ContractList><Contract><Name>GR-ALL</Name><IncomingOffice code=\"459\"></IncomingOffice></Contract></ContractList><Supplier name=\"HOTELBEDS PRODUCT,S.L.U.\" vatNumber=\"B38877676\"/><CommentList><Comment type=\"SERVICE\">\n</Comment></CommentList><DateFrom date=\"20130527\"/><DateTo date=\"20130528\"/><Currency code=\"EUR\">Euro</Currency><TotalAmount>22.640</TotalAmount><AdditionalCostList><AdditionalCost type=\"AG_COMMISSION\"><Price><Amount>0.000</Amount></Price></AdditionalCost><AdditionalCost type=\"COMMISSION_VAT\"><Price><Amount>0.000</Amount></Price></AdditionalCost></AdditionalCostList><ModificationPolicyList><ModificationPolicy>Cancellation</ModificationPolicy><ModificationPolicy>Confirmation</ModificationPolicy><ModificationPolicy>Modification</ModificationPolicy></ModificationPolicyList><HotelInfo xsi:type=\"ProductHotel\"><Code>48912</Code><Name>Akord</Name><Category type=\"SIMPLE\" code=\"3EST\">3 STARS</Category><Destination type=\"SIMPLE\" code=\"SOF\"><Name>Sofia</Name><ZoneList><Zone type=\"SIMPLE\" code=\"1\">Sofia</Zone></ZoneList></Destination></HotelInfo><AvailableRoom><HotelOccupancy><RoomCount>1</RoomCount><Occupancy><AdultCount>1</AdultCount><ChildCount>0</ChildCount><GuestList><Customer type=\"AD\"><CustomerId>1</CustomerId><Age>41</Age><Name>VAdsd</Name><LastName>tero</LastName></Customer></GuestList></Occupancy></HotelOccupancy><HotelRoom SHRUI=\"Puyt4by0bP2o+Ms+erqiaw==\" availCount=\"1\" status=\"CONFIRMED\"><Board type=\"SIMPLE\" code=\"BB-E10\">BED AND BREAKFAST</Board><RoomType type=\"SIMPLE\" code=\"SGL-E10\" characteristic=\"ST\">SINGLE STANDARD</RoomType><Price><Amount>22.640</Amount></Price><CancellationPolicy><Price><Amount>22.640</Amount><DateTimeFrom date=\"20130524\" time=\"2359\"/><DateTimeTo date=\"20130527\"/></Price></CancellationPolicy><HotelRoomExtraInfo><ExtendedData><Name>INFO_ROOM_AGENCY_BOOKING_STATUS</Name><Value>O</Value></ExtendedData><ExtendedData><Name>INFO_ROOM_INCOMING_BOOKING_STATUS</Name><Value>O</Value></ExtendedData></HotelRoomExtraInfo></HotelRoom></AvailableRoom></Service></ServiceList><Currency code=\"EUR\"></Currency><PaymentData><PaymentType code=\"C\"></PaymentType><Description>The total amount for this pro-forma invoice should be made in full to Hotelbeds, S.L.U, Bank: CITIBANK(Citigroup Centre, Canary Wharf, London, E14 5LB. United Kingdom) Account:GB93 CITI 1850 0811 2527 71,  SWIFT:CITIGB2L,  7 days prior to clients arrival (except group bookings with fixed days in advance at the time of the confirmation) . Please indicate our reference number when making payment. Thank you for your cooperation.</Description></PaymentData><TotalPrice>22.640</TotalPrice></Purchase></PurchaseDetailRS>\n', '1368451694', '1463115097');

Неактивен

 

#6 06.06.2016 19:01:35

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: Требуется помощь в написании/улучшении sql-запроса

в bookingsHistory пользователи, которые что-то бронировали? Тогда:

alter table bookingsHistory add index(userId);

SELECT A.*
FROM users A
LEFT JOIN bookingsHistory B
ON A.id = B.userId
WHERE B.userId IS NULL AND A.activated = 1 AND A.enabled = 1;

Неактивен

 

 

Страниц: 1


Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson

© 2007—2024 SQLinfo.ru
Powered by Flede
Реклама: