как отобразить две детали таблиц из коррелированного подзапроса. #mysql

ProgramBox

как отобразить две детали таблиц из коррелированного подзапроса. #mysql

Post author:admin
Запись опубликована:22 февраля, 2023
Post category:Вопросы по программированию

#mysql #sql #subquery #inner-join #greatest-n-per-group

#mysql #sql #подзапрос #внутреннее соединение #наибольшее число на группу

Вопрос:

Я хочу отобразить детали пакета вместе с деталями его наименее дорогостоящих подактивностей. Предположим, что есть A01, A02, A03 A01 — это пакет, а A03 — наименее дорогая дочерняя активность, поэтому мой оператор select должен отображать детали A01, A03

Однако я не понимаю, как я могу отобразить детали наименее дорогостоящего подзадачи [A03] вместе с A01.

Я знаю, что должен использовать коррелированный подзапрос, но все еще не могу заставить его работать.

Прямо сейчас он отображает только результат пакета с наименьшими затратами.

DDLs

 CREATE TABLE IF NOT EXISTS `Activity` (
  `ActivityID` VARCHAR(45) NOT NULL,
  `ActivityName` VARCHAR(45) NULL,
  `ActivityCost` float(2) NULL,
  `ActivityType` VARCHAR(45) NULL);

CREATE TABLE IF NOT EXISTS `Package` (
  `PackageActivityID` VARCHAR(45) NOT NULL,
  `ChildActivityID` VARCHAR(45) NOT NULL,
  INDEX `fk_Package_Activity1_idx` (`PackageActivityID` ASC) VISIBLE,
  INDEX `fk_Package_Activity2_idx` (`ChildActivityID` ASC) VISIBLE,
  CONSTRAINT `fk_Package_Activity1`
    FOREIGN KEY (`PackageActivityID`)
    REFERENCES `mydb`.`Activity` (`ActivityID`)
  CONSTRAINT `fk_Package_Activity2`
    FOREIGN KEY (`ChildActivityID`)
    REFERENCES `mydb`.`Activity` (`ActivityID`);

insert into activity values ('A01', 'Kayaking', 120, 'B');
insert into activity values ('B01', 'Seaking', 420, 'I');
insert into activity values ('C01', 'maya', 220, 'O');
insert into activity values ('D01', 'bing', 170, 'B');

insert into package (packageactivityid, childactivityid) values ('A01','C01');
insert into package (packageactivityid, childactivityid) values ('F01','F01');
insert into package (packageactivityid, childactivityid) values ('D01','D01');
insert into package (packageactivityid, childactivityid) values ('E01','E01');
insert into package (packageactivityid, childactivityid) values ('B01','B01');

Подводя итог, я надеюсь достичь этого результата:

  ------------------- ----------------- ------------ -------------- -------------- -------------- 
| PackageActivityID | ChildActivityID | ActivityID | ActivityName | ActivityCost | ActivityType |
 ------------------- ----------------- ------------ -------------- -------------- -------------- 
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
 ------------------- ----------------- ------------ -------------- -------------- --------------

Моя попытка запроса:

 select activityid, activityname, activitycost 
from package p, activity a
where p.PackageActivityID = a.ActivityID
and p.ChildActivityID = (select ActivityID
from package p,activity a
where a.activityid = p.ChildActivityID
having min(activitycost));

В моем запросе отображаются только сведения о packageactivity, но не сведения о childactivity .

1. Добавьте activity определение таблицы. Удалите все ключи / ограничения из DDLS.

2. having min(activitycost) просто проверяет, не min(activitycost) является ли это нулем (или пустой строкой или нулем).

3. Обратите внимание, что существует очень мало проблем, которые требуют, чтобы вы «должны были сделать» определенную вещь. Коррелированный подзапрос — это просто выбор, и иногда это лучший выбор.

Ответ №1:

Если я правильно понимаю, вы можете использовать оконные функции:

 select p.*, a.*
from package p
inner join (
    select a.*, rank() over(partition by activityid order by activitycost) rn
    from activity a
) a on p.PackageActivityID = a.activityid
where a.rn = 1

По сути, это дает меньше деталей о менее дорогостоящей активности для каждого пакета — вот как я понимаю ваш вопрос. Здесь разрешены связи.

В MySQL <8.0, где оконные функции недоступны, альтернатива использует коррелированный подзапрос для фильтрации:

 select p.*, a.*
from package p
inner join activity a  on p.PackageActivityID = a.activityid
where a.activitycost = (
    select min(a1.activitycost)
    from activity a1
    where a1.activityid = a.activityid
)