MySQL select запросы

354

14 июня 2017, 01:28

Есть таблицы:

orders(поля OrderId, Price, WorkerId)
typographies(TypographyId, DistrictId, TypographyName)
workers(WorkerId, TypographyId)
districts(DistrictId, DistrictName)

Необходимо сделать 2 запроса:

для каждого района выводит типографию с максимальной суммой заказов среди района
выводит типографию с максимальной суммой заказов в целом.

Для второго запроса придумал такое:

SELECT d.DistrictId, d.DistrictName, t.TypographyId, t.TypografyName, SUM(Price) FROM orders
    INNER JOIN workers w ON orders.WorkerId = w.WorkerId 
    INNER JOIN typografies t ON w.TypographyId = t.TypographyId
    INNER JOIN districts d ON d.DistrictId = t.DistrictId
    GROUP BY t.TypographyId
    HAVING SUM(Price)=MAX(SUM(Price));

но Error Code: 1111. Invalid use of group function, как осуществить первый вообще не представляю.

Пример для первого запроса: Исходно:

DistictId DistrictName  TypographyId TypographyName Sum(Price)
    1      Киевский          1       Принтэкспресс  12000
    2      Центральный       2       Ротапринт      5000
    3      Буденновский      3       Принтимус      12000
    1      Киевский          4       Принтхаус      10000

Выполнение первого запроса:

DistictId DistrictName  TypographyId TypographyName Sum(Price)
    1      Киевский          1       Принтэкспресс  12000
    2      Центральный       2       Ротапринт      5000
    3      Буденновский      3       Принтимус      12000

DDL:

CREATE TABLE `orders` (
  `OrderId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `WorkerId` int(11) unsigned DEFAULT NULL,
  `Price` double unsigned NOT NULL,
  PRIMARY KEY (`OrderId`),
  UNIQUE KEY `OrderId_UNIQUE` (`OrderId`),
  KEY `FK_Orders_Workers_idx` (`WorkerId`),
  CONSTRAINT `FK_Orders_Workers` FOREIGN KEY (`WorkerId`) REFERENCES `workers` (`WorkerId`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `districts` (
  `DistrictId` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `DistrictName` varchar(25) NOT NULL,
  PRIMARY KEY (`DistrictId`),
  UNIQUE KEY `DistrictId_UNIQUE` (`DistrictId`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
CREATE TABLE `workers` (
  `WorkerId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `TypographyId` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`WorkerId`),
  UNIQUE KEY `WorkerId_UNIQUE` (`WorkerId`),
  KEY `TypographyWorker_idx` (`TypographyId`),
  CONSTRAINT `FK_Worker_Typography` FOREIGN KEY (`TypographyId`) REFERENCES `typografies` (`TypographyId`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `typografies` (
  `TypographyId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `TypografyName` varchar(15) NOT NULL,
  `DistrictId` int(11) unsigned NOT NULL,
  PRIMARY KEY (`TypographyId`),
  UNIQUE KEY `TypographyId_UNIQUE` (`TypographyId`),
  KEY `DistrictId_idx` (`DistrictId`),
  CONSTRAINT `FK_Typographie_District` FOREIGN KEY (`DistrictId`) REFERENCES `districts` (`DistrictId`) ON DELETE NO ACTION ON UPDATE CASCADE,
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Answer 1

SELECT *
FROM   (SELECT d.districtid, d.districtname, t.typographyid, t.typographyname, Sum(price) as price
        FROM   orders 
        INNER JOIN workers w ON orders.WorkerId = w.WorkerId 
        INNER JOIN typographies t ON w.typographyid = t.typographyid
        INNER JOIN districts d ON d.DistrictId = t.DistrictId
        GROUP  BY t.districtid, t.typographyid) as w
WHERE  price = (SELECT Max(price) FROM 
                    (SELECT d.districtid,d.districtname,t.typographyid,t.typographyname,Sum(price) as price
                    FROM   orders 
                    INNER JOIN workers w ON orders.WorkerId = w.WorkerId 
                    INNER JOIN typographies t ON w.typographyid = t.typographyid
                    INNER JOIN districts d ON d.DistrictId = t.DistrictId
                    GROUP  BY t.districtid, t.typographyid) as x
                WHERE  districtid = w.districtid)

SELECT d.DistrictId, d.DistrictName, t.TypographyId, t.TypografyName, SUM(Price) FROM orders
    INNER JOIN workers w ON orders.WorkerId = w.WorkerId 
    INNER JOIN typografies t ON w.TypographyId = t.TypographyId
    INNER JOIN districts d ON d.DistrictId = t.DistrictId
    GROUP BY t.TypographyId
    ORDER BY SUM(Price) DESC
    LIMIT 1;