order by na podstawie dwóch pól z dwóch różnych tabel Opcje

[darko]

Witam!

Chciałbym wyciągnąć 10 idków produktów (top 10), sortując wyniki jednocześnie według ilości zamówionych (sprzedanych) produktów oraz nazwy produktu, poniższe zapytanie wyciąga idki, ale nie działa sortowanie:

Wykonuję następujące zapytanie SQL

[SQL] pobierz, plaintext 
SELECT DISTINCT orderdetails.id_cms_prod 
 
FROM 
 
cms_prod_order_info orderdetails,
cms_prod_order orders,
cms_prod_lang prodlang
 
WHERE 
 
prodlang.id_cms_prod=orderdetails.id_cms_prod AND 
orderdetails.id_cms_prod_order_info NOT LIKE 1 AND
orders.cms_prod_order_status NOT LIKE 0
ORDER BY orderdetails.cms_prod_order_info_amount DESC, prodlang.cms_prod_lang_name ASC LIMIT 10
[SQL] pobierz, plaintext 

W jaki sposób połączyć ze sobą warunki:

- order by ilosc_zamowionych_produktow
- order by nazwa produktu


Struktura tabel:

cms_prod_order_info

[SQL] pobierz, plaintext 
CREATE TABLE  `xyz`.`cms_prod_order_info` (
  `id_cms_prod_order_info` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_cms_prod_order` int(10) UNSIGNED NOT NULL,
  `id_cms_customer` int(10) UNSIGNED NOT NULL,
  `id_cms_manufacturer` int(10) UNSIGNED NOT NULL,
  `id_cms_prod` int(10) UNSIGNED NOT NULL,
  `id_cms_size` int(10) NOT NULL,
  `cms_prod_order_info_size_status` tinyint(1) NOT NULL COMMENT 'status rozmiarow',
  `cms_prod_order_info_amount` int(10) UNSIGNED NOT NULL COMMENT 'ilosc zamawianych produktow',
  `cms_prod_order_info_price_brutto` float NOT NULL COMMENT 'cena brutto',
  `cms_prod_order_info_vat` float NOT NULL COMMENT 'stawka podatku VAT',
  `cms_prod_order_info_promo_status` tinyint(1) NOT NULL COMMENT 'status promocji produktu',
  `cms_prod_order_info_status` tinyint(1) NOT NULL,
  `cms_prod_order_info_desc` text COMMENT 'opcjonalne uwagi do zamowienia',
  `cms_prod_order_info_add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cms_prod_order_info_add_login` varchar(55) NOT NULL,
  `cms_prod_order_info_mod_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cms_prod_order_info_mod_login` varchar(55) DEFAULT NULL,
  PRIMARY KEY  (`id_cms_prod_order_info`),
  KEY `FK_cms_prod_order_info_1` (`id_cms_prod_order`),
  KEY `FK_cms_prod_order_info_2` (`id_cms_customer`),
  KEY `FK_cms_prod_order_info_3` (`id_cms_manufacturer`),
  KEY `FK_cms_prod_order_info_4` (`id_cms_prod`),
  CONSTRAINT `FK_cms_prod_order_info_1` FOREIGN KEY (`id_cms_prod_order`) REFERENCES `cms_prod_order` (`id_cms_prod_order`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_cms_prod_order_info_2` FOREIGN KEY (`id_cms_customer`) REFERENCES `cms_customer` (`id_cms_customer`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_cms_prod_order_info_3` FOREIGN KEY (`id_cms_manufacturer`) REFERENCES `cms_manufacturer` (`id_cms_manufacturer`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_cms_prod_order_info_4` FOREIGN KEY (`id_cms_prod`) REFERENCES `cms_prod` (`id_cms_prod`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Zamowienia - szczegoly'
[SQL] pobierz, plaintext 

cms_prod_order

[SQL] pobierz, plaintext 
CREATE TABLE  `xyz`.`cms_prod_order` (
  `id_cms_prod_order` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_cms_customer` int(10) UNSIGNED NOT NULL,
  `id_cms_courier` int(10) UNSIGNED NOT NULL,
  `id_cms_courier_provider` int(10) NOT NULL,
  `cms_prod_order_number` varchar(145) NOT NULL COMMENT 'numer zamowienia',
  `cms_prod_order_status` int(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT '0 - anulowane, 1 - nowe, 2 - uregulowane, 3 - wyslane',
  `cms_prod_order_desc` varchar(255) DEFAULT NULL COMMENT 'uwagi do zamowienia lub powod anulowania',
  `cms_prod_order_add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cms_prod_order_add_login` varchar(55) NOT NULL,
  `cms_prod_order_mod_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cms_prod_order_mod_login` varchar(55) DEFAULT NULL,
  PRIMARY KEY  (`id_cms_prod_order`),
  KEY `FK_cms_prod_order_1` (`id_cms_customer`),
  KEY `FK_cms_prod_order_2` (`id_cms_courier`),
  CONSTRAINT `FK_cms_prod_order_1` FOREIGN KEY (`id_cms_customer`) REFERENCES `cms_customer` (`id_cms_customer`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_cms_prod_order_2` FOREIGN KEY (`id_cms_courier`) REFERENCES `cms_courier` (`id_cms_courier`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8 COMMENT='Zamowienia - tabela glowna'
[SQL] pobierz, plaintext 

tabela cms_prod_lang (to tabela słownikowa produktu)

[SQL] pobierz, plaintext 
CREATE TABLE  `xyz`.`cms_prod_lang` (
  `id_cms_prod_lang` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_cms_lang` int(10) UNSIGNED NOT NULL,
  `id_cms_prod` int(10) UNSIGNED NOT NULL,
  `id_cms_dept` int(10) UNSIGNED NOT NULL,
  `id_cms_art` int(10) UNSIGNED NOT NULL,
  `id_cms_serie` int(10) UNSIGNED NOT NULL,
  `id_cms_manufacturer` int(10) UNSIGNED NOT NULL,
  `cms_prod_lang_name` varchar(145) NOT NULL COMMENT 'nazwa produktu',
  `cms_prod_lang_1_desc` text COMMENT 'opis',
  `cms_prod_lang_2_desc` text COMMENT 'dodatkowy (opcjonalny) opis',
  `cms_prod_lang_status` tinyint(1) NOT NULL,
  `cms_prod_lang_add_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cms_prod_lang_add_login` varchar(55) NOT NULL,
  `cms_prod_lang_mod_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cms_prod_lang_mod_login` varchar(55) DEFAULT NULL,
  PRIMARY KEY  (`id_cms_prod_lang`),
  KEY `FK_cms_prod_lang_1` (`id_cms_lang`),
  KEY `FK_cms_prod_lang_2` (`id_cms_prod`),
  KEY `FK_cms_prod_lang_3` (`id_cms_dept`),
  KEY `FK_cms_prod_lang_4` (`id_cms_art`),
  KEY `FK_cms_prod_lang_5` (`id_cms_serie`),
  KEY `FK_cms_prod_lang_6` (`id_cms_manufacturer`),
  CONSTRAINT `FK_cms_prod_lang_1` FOREIGN KEY (`id_cms_lang`) REFERENCES `cms_lang` (`id_cms_lang`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_cms_prod_lang_2` FOREIGN KEY (`id_cms_prod`) REFERENCES `cms_prod` (`id_cms_prod`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_cms_prod_lang_3` FOREIGN KEY (`id_cms_dept`) REFERENCES `cms_dept` (`id_cms_dept`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK_cms_prod_lang_4` FOREIGN KEY (`id_cms_art`) REFERENCES `cms_art` (`id_cms_art`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_cms_prod_lang_5` FOREIGN KEY (`id_cms_serie`) REFERENCES `cms_serie` (`id_cms_serie`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_cms_prod_lang_6` FOREIGN KEY (`id_cms_manufacturer`) REFERENCES `cms_manufacturer` (`id_cms_manufacturer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7494 DEFAULT CHARSET=utf8 COMMENT='Dane slownikowe produktow'
[SQL] pobierz, plaintext 

[mooschka]

Przerzuć warunek złączenia do

[SQL] pobierz, plaintext 
JOIN cms_prod_lang ON prodlang.id_cms_prod=orderdetails.id_cms_prod
[SQL] pobierz, plaintext 

Poza tym po co stosujesz warunek LIKE do pól typu INT? I czy masz aż tak duże ilości danych, że stosujesz INT jako PRIMARY_KEY?
Jeśli chodzi o sortowanie to spróbuj tak:

[SQL] pobierz, plaintext 
SELECT pole1, pole2 ... FROM (SELECT pole1, pole2 ... FROM tabela1 JOIN tabela2 ON warunek_złączenia JOIN ... WHERE warunki) AS T
ORDER BY pole1 ASC, pole2 ASC
[SQL] pobierz, plaintext 

Ten post edytował mooschka 16.11.2009, 08:33:41