반응형
MySQL/MariaDB의 다중 조인에서 값이 가장 낮은 요소 선택
그룹별 및 다중 조인이 있는 일부 데이터 세트를 선택하는 방법은 무엇입니까?하위 쿼리나 참여 방법에 대해 읽어 보았지만 유감스럽게도 다른 요구와 결합할 수 없습니다.제 문제는 다른 고객의 출품작이나 두 테이블 사이에 완전한 관계가 없는 출품작을 받는다는 것입니다.왼쪽에는 오른쪽 항목이 있지만 오른쪽에는 가장 낮은 값이 없습니다.아니면 전혀 다른 데이터라도 왜 그런지 이해할 수 없습니다.
예상결과
예를 들어 가장 싼 것이 필요합니다(price_consumer) 모든 모델의 모터(model_id)의client_id = 1000그리고.country_id = 1그리고.pricelist_id = 20. 대부분의 관련 데이터가 표에 있습니다.cc_pricelist_elements. 여기 콜fk_table값 모터가 있는 경우 행만 흥미롭기 때문에 관련이 있습니다.cc_pricelist_elements에 해당 항목이 없는 모델은 결과에 포함되지 않아야 합니다.
동시에, 그에 따른 모델의cc_models테이블이 필요합니다.
(아래에 제시된 데이터 중) 결과 집합은 다음과 같습니다.
cc_models.name | cc_pricelist_elements.price_consumer
---------------|-------------------------------------
Model 2016 | 150
물론 고객당 모델 수는 더 많고(n:1), 모델당 모터 수는 더 많을 수 있습니다).
데이터베이스 구조 및 샘플 데이터
편집: 테이블 구조가 단순화되고 데이터가 감소된 sql fiddle.
일부 연관성이 있어서 쿼리에 필요한지 잘 몰라서 테이블을 몇 개 더 넣었습니다.
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping structure for table audaris_central.cc_lines
CREATE TABLE IF NOT EXISTS `cc_lines` (
`client_id` int(10) unsigned NOT NULL,
`line_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`client_id`,`line_id`),
KEY `line_id` (`line_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
-- Dumping data for table audaris_central.cc_lines: ~11 rows (approximately)
/*!40000 ALTER TABLE `cc_lines` DISABLE KEYS */;
INSERT IGNORE INTO `cc_lines` (`client_id`, `line_id`, `name`) VALUES
(1000, 3, 'Standart'),
(1000, 4, 'Luxus5'),
(1000, 5, 'Standard Plus'),
(1000, 12, 'Comfortline'),
(1000, 13, 'Trendline'),
(1000, 14, 'Highline'),
(1438, 6, 'Trendline'),
(1577, 10, 'Trendline'),
(1604, 15, 'Trikolor'),
(1634, 8, 'Standard'),
(1634, 9, 'Executive');
/*!40000 ALTER TABLE `cc_lines` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_meta_lines_models
CREATE TABLE IF NOT EXISTS `cc_meta_lines_models` (
`model_id` int(10) unsigned NOT NULL,
`line_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`model_id`,`line_id`),
KEY `fk_line_id` (`line_id`),
KEY `fk_model_id` (`model_id`),
CONSTRAINT `fk_line_id` FOREIGN KEY (`line_id`) REFERENCES `cc_lines` (`line_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_model_id` FOREIGN KEY (`model_id`) REFERENCES `cc_models` (`model_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table audaris_central.cc_meta_lines_models: ~23 rows (approximately)
/*!40000 ALTER TABLE `cc_meta_lines_models` DISABLE KEYS */;
INSERT IGNORE INTO `cc_meta_lines_models` (`model_id`, `line_id`) VALUES
(20, 3),
(20, 4),
(22, 3),
(22, 4),
(23, 4),
(23, 5),
(29, 8),
(31, 6),
(34, 8),
(35, 8),
(36, 8),
(36, 9),
(38, 8),
(39, 8),
(39, 9),
(41, 10),
(43, 8),
(46, 8),
(47, 9),
(54, 12),
(54, 13),
(54, 14),
(57, 15);
/*!40000 ALTER TABLE `cc_meta_lines_models` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_meta_motors_variants
CREATE TABLE IF NOT EXISTS `cc_meta_motors_variants` (
`motor_id` int(10) unsigned NOT NULL,
`variant_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`motor_id`,`variant_id`),
KEY `motor_id` (`motor_id`),
KEY `variant_id` (`variant_id`),
CONSTRAINT `FK_cc_meta_motors_variants_cc_motors` FOREIGN KEY (`motor_id`) REFERENCES `cc_motors` (`motor_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_cc_meta_motors_variants_cc_variants` FOREIGN KEY (`variant_id`) REFERENCES `cc_variants` (`variant_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
-- Dumping data for table audaris_central.cc_meta_motors_variants: ~4 rows (approximately)
/*!40000 ALTER TABLE `cc_meta_motors_variants` DISABLE KEYS */;
INSERT IGNORE INTO `cc_meta_motors_variants` (`motor_id`, `variant_id`) VALUES
(6, 22),
(6, 25),
(6, 50),
(8, 22);
/*!40000 ALTER TABLE `cc_meta_motors_variants` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_models
CREATE TABLE IF NOT EXISTS `cc_models` (
`client_id` int(10) unsigned NOT NULL,
`model_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`original_model_id` int(10) unsigned NOT NULL,
`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`client_id`,`model_id`),
KEY `model_id` (`model_id`),
KEY `client_id` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
-- Dumping data for table audaris_central.cc_models: ~34 rows (approximately)
/*!40000 ALTER TABLE `cc_models` DISABLE KEYS */;
INSERT IGNORE INTO `cc_models` (`client_id`, `model_id`, `original_model_id`, `name`) VALUES
(287, 45, 1329, 'MJ2016'),
(1000, 11, 1501, NULL),
(1000, 12, 1501, NULL),
(1000, 19, 2317, 'test'),
(1000, 20, 2288, 'Modell 2016'),
(1000, 21, 2312, '123456'),
(1000, 22, 2288, 'Modell 2015'),
(1000, 23, 2290, 'Modell 2015'),
(1000, 25, 2289, 'normal'),
(1000, 32, 1503, 'Modell 2016'),
(1000, 33, 1500, NULL),
(1000, 54, 2255, 'Modell 2016'),
(1000, 56, 2256, '2016'),
(1438, 31, 2255, '2SJ4A3'),
(1562, 44, 1500, 'Highline'),
(1577, 41, 2253, '2016'),
(1602, 55, 1501, NULL),
(1604, 57, 1973, 'Modell 2015'),
(1631, 58, 2286, NULL),
(1634, 29, 2288, 'Modell 2015'),
(1634, 34, 2291, 'Modell 2016'),
(1634, 35, 2290, 'Modell 2016'),
(1634, 36, 2307, 'Modell 2016'),
(1634, 38, 2295, 'Modell 2016'),
(1634, 39, 2308, 'Modell 2016'),
(1634, 43, 2297, 'Modell 2016'),
(1634, 46, 2288, 'Modell 2016'),
(1634, 47, 2301, 'Modell 2016'),
(1634, 48, 2303, 'Modell 2016'),
(1634, 49, 2312, NULL),
(1634, 50, 2313, NULL),
(1634, 51, 2195, NULL),
(1634, 52, 2317, NULL),
(1634, 53, 2304, 'Modell 2016');
/*!40000 ALTER TABLE `cc_models` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_motors
CREATE TABLE IF NOT EXISTS `cc_motors` (
`client_id` int(10) unsigned NOT NULL,
`motor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`variant_id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`gears` tinyint(3) unsigned NOT NULL DEFAULT '0',
`gearbox` tinyint(3) unsigned NOT NULL DEFAULT '0',
`drive` tinyint(3) unsigned NOT NULL DEFAULT '0',
`kw` int(10) unsigned NOT NULL DEFAULT '0',
`cylinders` tinyint(3) unsigned NOT NULL DEFAULT '0',
`engine_size` int(10) unsigned NOT NULL DEFAULT '0',
`fuel` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ees` tinyint(3) unsigned DEFAULT '0' COMMENT 'exhaust emission standard',
`eec` varchar(2) COLLATE utf8_unicode_ci DEFAULT '' COMMENT 'energy efficiency class',
`emission` int(10) unsigned DEFAULT '0',
`consumption_combined` decimal(3,1) unsigned DEFAULT '0.0',
`consumption_urban` decimal(3,1) unsigned DEFAULT '0.0',
`consumption_extraurban` decimal(3,1) unsigned DEFAULT '0.0',
`weight` int(10) unsigned DEFAULT '0',
`e10` tinyint(3) unsigned DEFAULT '0',
`superplus` tinyint(3) unsigned DEFAULT '0',
`code` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
PRIMARY KEY (`motor_id`,`client_id`),
KEY `variant_id` (`variant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table audaris_central.cc_motors: ~27 rows (approximately)
/*!40000 ALTER TABLE `cc_motors` DISABLE KEYS */;
INSERT IGNORE INTO `cc_motors` (`client_id`, `motor_id`, `variant_id`, `name`, `gears`, `gearbox`, `drive`, `kw`, `cylinders`, `engine_size`, `fuel`, `ees`, `eec`, `emission`, `consumption_combined`, `consumption_urban`, `consumption_extraurban`, `weight`, `e10`, `superplus`, `code`) VALUES
(1000, 6, 0, '3,0 JTDxy', 6, 1, 1, 117, 4, 2998, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''),
(1000, 7, 0, 'FIAT DUCATO SPECIAL X250 - motor 2.0 JTD - 115 PS', 5, 1, 1, 85, 4, 2000, 1, 5, 'A+', 150, 9.5, 11.0, 8.5, 3500, NULL, NULL, 'X250'),
(1000, 8, 0, 'FIAT Ducato EURO5 2,30 Multijet 16V 130 CV - 35 QL', 6, 1, 1, 96, 4, 2300, 1, 5, 'B', 0, 10.4, 11.9, 9.9, 3620, NULL, NULL, ''),
(1000, 9, 0, 'FIAT DUCATO SPECIAL X250 - motor 2.0 JTD - 115 PS', 5, 1, 1, 85, 4, 2000, 1, 5, 'A+', 150, 9.5, 11.0, 8.5, 3500, NULL, NULL, 'X250'),
(1634, 10, 0, 'FIAT 180 Multijet Power', 6, 1, 1, 130, 4, 2999, 2, 0, 'B', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 180 CV'),
(1438, 13, 0, '2,0TDI', 6, 1, 1, 110, 4, 1968, 2, 6, 'A', 150, 10.0, 8.0, 9.0, 2000, NULL, NULL, '123456'),
(1577, 17, 0, '2.0 TDI SCR BMT 62kW / 84PS', 5, 1, 1, 62, 4, 1968, 2, 6, 'B', 155, 6.0, 7.1, 5.3, 2087, NULL, NULL, ''),
(1577, 18, 0, '2.0 TDI SCR BMT 75kW / 102PS', 5, 1, 1, 75, 4, 1968, 2, 6, 'B', 155, 6.0, 7.1, 5.3, 2087, NULL, NULL, ''),
(1577, 19, 0, '2.0 TDI SCR 110kW / 150PS', 6, 1, 1, 110, 4, 1968, 2, 6, 'B', 161, 6.2, 7.4, 5.5, 2117, NULL, NULL, ''),
(1577, 20, 0, '2.0 TDI SCR 4Motion 110kW / 150PS', 6, 1, 3, 110, 4, 1968, 2, 6, 'B', 171, 6.6, 7.8, 5.9, 2267, NULL, NULL, ''),
(1577, 21, 0, '2.0 TDI SCR DSG BMT 110kW / 150PS', 7, 3, 1, 110, 4, 1968, 2, 6, 'A', 155, 6.0, 6.7, 5.6, 2141, NULL, NULL, ''),
(1577, 22, 0, '2.0 TDI SCR DSG 4Motion 110kW / 150PS', 7, 3, 3, 110, 4, 1968, 2, 6, 'A', 163, 6.3, 7.0, 5.9, 2286, NULL, NULL, ''),
(1577, 23, 0, '2.0 TSI 110kW / 150PS', 6, 1, 1, 110, 4, 1984, 1, 6, 'D', 210, 9.2, 12.3, 7.5, 2113, NULL, NULL, ''),
(1634, 24, 0, 'FIAT 115 Multijet', 5, 1, 1, 85, 4, 1958, 2, 5, 'D', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 115 CV'),
(1634, 25, 0, 'FIAT 130 Multijet', 6, 1, 1, 96, 4, 2287, 2, 5, 'D', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 130 CV'),
(1634, 26, 0, 'FIAT 150 Multijet', 6, 1, 1, 110, 4, 2287, 2, 5, 'C', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 150 CV'),
(1634, 27, 0, 'VW 2,0 TDI 140', 5, 1, 1, 103, 4, 1968, 2, 6, 'B', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'VW 140 CV'),
(1634, 28, 0, 'Mercedes Sprinter 316 CD', 6, 1, 1, 118, 4, 2143, 2, 6, 'B', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'MB 316 CD'),
(287, 29, 0, '1.0 EcoTSI', 6, 1, 1, 81, 3, 999, 1, 6, 'B', 99, 4.3, 5.2, 3.8, 1109, 1, NULL, ''),
(1634, 30, 0, 'FIAT 150 Multijet', 6, 1, 1, 110, 4, 2287, 2, 5, 'C', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 150 CV'),
(1634, 31, 0, 'FIAT 115 Multijet', 5, 1, 1, 85, 4, 1958, 2, 5, 'D', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 115 CV'),
(1634, 32, 0, 'FIAT 115 Multijet', 5, 1, 1, 85, 4, 1958, 2, 5, 'D', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 115 CV'),
(1634, 33, 0, 'FIAT 150 Multijet', 6, 1, 1, 110, 4, 2287, 2, 5, 'C', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 150 CV'),
(1634, 34, 0, 'FIAT 180 Multijet Power', 6, 1, 1, 130, 4, 2999, 2, 0, 'B', 0, 0.0, 0.0, 0.0, 0, NULL, NULL, 'FIAT 180 CV'),
(1000, 35, 0, '2,0 TDI 110 KW', 6, 1, 1, 110, 4, 1986, 2, 6, 'B', 145, 8.9, 15.0, 11.0, 2413, NULL, NULL, ''),
(1000, 36, 0, '2,0 TDI 110 KW DSG', 6, 3, 1, 110, 4, 1986, 2, 6, 'B', 145, 8.9, 15.0, 11.0, 2413, NULL, NULL, ''),
(1604, 37, 0, '1.6 GDI 6-Gang 99 kW (135 PS)', 6, 1, 1, 99, 4, 1591, 1, 6, 'C', 140, 6.2, 8.1, 5.1, 1294, NULL, NULL, 'TMA');
/*!40000 ALTER TABLE `cc_motors` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_pricelists
CREATE TABLE IF NOT EXISTS `cc_pricelists` (
`client_id` int(10) unsigned NOT NULL,
`pricelist_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`variant_id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`client_id`,`pricelist_id`),
KEY `variant_id` (`variant_id`),
KEY `client_id` (`client_id`),
KEY `pricelist_id` (`pricelist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table audaris_central.cc_pricelists: ~8 rows (approximately)
/*!40000 ALTER TABLE `cc_pricelists` DISABLE KEYS */;
INSERT IGNORE INTO `cc_pricelists` (`client_id`, `pricelist_id`, `variant_id`, `name`) VALUES
(287, 16, 34, 'Ibiza MJ2016'),
(1000, 9, 15, 'Preisliste 1'),
(1000, 20, 50, 'test2'),
(1438, 10, 24, 'Multivan Trendline 2016'),
(1577, 14, 31, 'VW T6 Multivan Trendline 2016'),
(1634, 17, 40, 'Brownie 1'),
(1634, 18, 43, 'Oasi 540'),
(1634, 19, 45, 'Oasi 610 N');
/*!40000 ALTER TABLE `cc_pricelists` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_pricelist_elements
CREATE TABLE IF NOT EXISTS `cc_pricelist_elements` (
`pricelist_element_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(10) unsigned NOT NULL,
`pricelist_id` int(10) unsigned NOT NULL,
`line_id` int(10) unsigned NOT NULL,
`country_id` int(10) unsigned NOT NULL,
`fk_table` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`fk_id` int(10) unsigned NOT NULL,
`cost` decimal(10,4) NOT NULL DEFAULT '0.0000',
`price_catalog` decimal(10,4) NOT NULL DEFAULT '0.0000',
`price_consumer` decimal(10,4) NOT NULL DEFAULT '0.0000',
`price_dealer` decimal(10,4) NOT NULL DEFAULT '0.0000',
`is_available` tinyint(3) unsigned NOT NULL DEFAULT '0',
`is_standard` tinyint(3) unsigned DEFAULT '0',
`is_default` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`client_id`,`pricelist_id`,`fk_table`,`fk_id`,`line_id`,`country_id`),
KEY `pricelist_element_id` (`pricelist_element_id`),
KEY `client_id_pricelist_id_line_id_country_id` (`client_id`,`pricelist_id`,`line_id`,`country_id`),
KEY `FK_cc_pricelist_elements_cc_pricelists` (`pricelist_id`),
KEY `fk_table` (`fk_table`),
KEY `fk_id` (`fk_id`),
KEY `client_id` (`client_id`),
KEY `line_id` (`line_id`),
KEY `is_available` (`is_available`),
KEY `client_id_line_id_is_available` (`client_id`,`line_id`,`is_available`),
CONSTRAINT `FK_cc_pricelist_elements_cc_pricelists` FOREIGN KEY (`pricelist_id`) REFERENCES `cc_pricelists` (`pricelist_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table audaris_central.cc_pricelist_elements: ~26 rows (approximately)
/*!40000 ALTER TABLE `cc_pricelist_elements` DISABLE KEYS */;
INSERT IGNORE INTO `cc_pricelist_elements` (`pricelist_element_id`, `client_id`, `pricelist_id`, `line_id`, `country_id`, `fk_table`, `fk_id`, `cost`, `price_catalog`, `price_consumer`, `price_dealer`, `is_available`, `is_standard`, `is_default`) VALUES
(118, 1000, 20, 12, 1, 'colors', 35, 0.0000, 228.0000, 96.0000, 0.0000, 1, NULL, 0),
(128, 1000, 20, 12, 1, 'equipments', 3, 0.0000, 212.0000, 83.0000, 0.0000, 1, 0, 0),
(129, 1000, 20, 12, 1, 'equipments', 8, 0.0000, 275.0000, 214.0000, 0.0000, 1, 0, 0),
(122, 1000, 20, 12, 1, 'equipments', 129, 0.0000, 638.0000, 510.0000, 0.0000, 1, 0, 0),
(117, 1000, 20, 14, 1, 'equipments', 129, 0.0000, 468.0000, 401.0000, 0.0000, 0, 1, 0),
(123, 1000, 20, 12, 1, 'interiors', 7, 0.0000, 327.0000, 165.0000, 0.0000, 1, NULL, 0),
(127, 1000, 20, 12, 1, 'interiors', 8, 0.0000, 131.0000, 110.0000, 0.0000, 1, NULL, 0),
(130, 1000, 20, 12, 1, 'motors', 6, 0.0000, 476.0000, 448.0000, 0.0000, 1, NULL, 0),
(133, 1000, 20, 13, 1, 'motors', 6, 0.0000, 0.0000, 150.0000, 0.0000, 0, NULL, 0),
(120, 1000, 20, 12, 1, 'motors', 35, 0.0000, 985.0000, 895.0000, 0.0000, 1, NULL, 0),
(121, 1000, 20, 12, 1, 'motors', 36, 0.0000, 702.0000, 529.0000, 0.0000, 1, NULL, 0),
(41, 1438, 10, 12, 1, 'motors', 13, 0.0000, 456.0000, 949.0000, 0.0000, 1, NULL, 0),
(80, 1577, 14, 10, 1, 'equipments', 19, 0.0000, 972.0000, 845.0000, 0.0000, 1, 0, 0),
(79, 1577, 14, 10, 1, 'motors', 19, 0.0000, 697.0000, 674.0000, 0.0000, 0, NULL, 0),
(110, 1577, 14, 10, 2, 'motors', 19, 0.0000, 468.0000, 323.0000, 0.0000, 0, NULL, 0),
(99, 1634, 17, 9, 1, 'colors', 9, 0.0000, 152.0000, 85.0000, 0.0000, 1, NULL, 1),
(101, 1634, 17, 9, 1, 'colors', 32, 0.0000, 155.0000, 46.0000, 0.0000, 1, NULL, 0),
(96, 1634, 17, 9, 1, 'equipments', 18, 0.0000, 217.0000, 62.0000, 500.0000, 1, 0, 0),
(102, 1634, 17, 9, 1, 'interiors', 1, 0.0000, 523.0000, 462.0000, 0.0000, 0, NULL, 1),
(105, 1634, 17, 9, 1, 'motors', 10, 0.0000, 967.0000, 919.0000, 0.0000, 1, NULL, 0),
(103, 1634, 17, 9, 1, 'motors', 24, 0.0000, 466.0000, 398.0000, 0.0000, 0, NULL, 1),
(106, 1634, 17, 9, 1, 'motors', 25, 0.0000, 230.0000, 225.0000, 0.0000, 1, NULL, 0),
(104, 1634, 17, 9, 1, 'motors', 26, 0.0000, 553.0000, 519.0000, 0.0000, 1, NULL, 0),
(107, 1634, 19, 8, 1, 'motors', 32, 0.0000, 175.0000, 10.0000, 0.0000, 0, NULL, 1),
(108, 1634, 19, 8, 1, 'motors', 33, 0.0000, 916.0000, 782.0000, 0.0000, 1, NULL, 0),
(109, 1634, 19, 8, 1, 'motors', 34, 0.0000, 358.0000, 174.0000, 0.0000, 1, NULL, 0);
/*!40000 ALTER TABLE `cc_pricelist_elements` ENABLE KEYS */;
-- Dumping structure for table audaris_central.cc_variants
CREATE TABLE IF NOT EXISTS `cc_variants` (
`client_id` int(10) unsigned NOT NULL,
`variant_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`model_id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`client_id`,`variant_id`,`model_id`),
KEY `variant_id` (`variant_id`),
KEY `model_id` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
-- Dumping data for table audaris_central.cc_variants: ~19 rows (approximately)
/*!40000 ALTER TABLE `cc_variants` DISABLE KEYS */;
INSERT IGNORE INTO `cc_variants` (`client_id`, `variant_id`, `model_id`, `name`) VALUES
(287, 34, 45, 'FR'),
(1000, 9, 19, 'Reisemobil (Kastenwagen)'),
(1000, 16, 23, 'Standard'),
(1000, 22, 11, 'Gelbgrün'),
(1000, 25, 32, 'Kombi'),
(1000, 50, 54, 'Kombi'),
(1438, 24, 31, 'Kombi'),
(1577, 31, 41, 'Großraumlimousine'),
(1604, 52, 57, 'Kombi'),
(1634, 40, 47, 'Wohnmobil'),
(1634, 41, 43, 'Wohnmobil'),
(1634, 42, 29, 'Wohnmobil'),
(1634, 43, 46, 'Wohnmobil'),
(1634, 44, 35, 'Wohnmobil'),
(1634, 45, 34, 'Wohnmobil'),
(1634, 46, 38, 'Wohnmobil'),
(1634, 47, 36, 'Wohnmobil'),
(1634, 48, 39, 'Wohnmobil'),
(1634, 49, 48, 'Wohnwagen/Caravan');
/*!40000 ALTER TABLE `cc_variants` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
접근
이것이 지금까지 생각해낸 것(최선의 접근법)이지만, 그 결과는 원하는 것과 동떨어진 것입니다.
SELECT
ccm.model_id, ccm.name, ccpe.price_consumer
FROM
cc_models ccm
INNER JOIN
cc_meta_lines_models cclm ON
cclm.model_id = ccm.model_id
INNER JOIN
cc_pricelist_elements ccpe ON
ccpe.client_id = 1000 AND
ccpe.fk_table = 'motors' AND
ccpe.country_id = 1 AND
ccpe.pricelist_id = 20
LEFT JOIN
cc_pricelist_elements ccpe2 ON
ccpe2.pricelist_element_id = ccpe.pricelist_element_id AND
ccpe.price_consumer < ccpe2.price_consumer
WHERE
ccm.client_id = 1000 AND
ccpe2.pricelist_element_id IS NULL
ORDER BY
ccm.name
언급URL : https://stackoverflow.com/questions/33752030/select-element-with-lowest-value-from-multiple-joins-in-mysql-mariadb
반응형
'programing' 카테고리의 다른 글
| OQGRAPH 쿼리에서 노드 간의 관계를 선택할 수 있습니까? (0) | 2023.09.20 |
|---|---|
| VBA를 사용하여 명명된 범위를 정의하려면 어떻게 해야 합니까? (0) | 2023.09.20 |
| wp-config.php에 기본 인증 키 값을 그대로 둔 경우 해커가 데이터베이스에 액세스할 수 있습니까? (0) | 2023.09.20 |
| WC_상품_have_posts()에서 작동하지 않는 쿼리 (0) | 2023.09.20 |
| PHP PDO 준비문 -- MySQL LIKE 쿼리 (0) | 2023.09.20 |