Получить идентификаторы из группы по ⇐ C#

[Anonymous]

У меня есть следующая таблица в MySQL:

Код: Выделить всё

    CREATE TABLE `bookings` (

`BookingID` int NOT NULL AUTO_INCREMENT,

`Adults` int DEFAULT '-1',

`AdultsSubTotal` double DEFAULT '-1',

`Children` int DEFAULT '-1',

`ChildrenSubTotal` double DEFAULT '-1',

`Babies` int DEFAULT '-1',

`BabiesSubTotal` double DEFAULT '-1',

`Seniors` int DEFAULT '-1',

`SeniorsSubTotal` double DEFAULT '-1',

`Students` int DEFAULT '-1',

`StudentsSubTotal` double DEFAULT '-1',

`TotalPersons` int DEFAULT '-1',

`TotalToPay` double DEFAULT '-1',

`Checkin` datetime DEFAULT NULL,

`Checkout` datetime DEFAULT NULL,

`Insurance` double DEFAULT '-1',

`Deposited` double DEFAULT '-1',

`RemainingToPay` double DEFAULT '-1',

`Language` varchar(45) DEFAULT NULL,

`Checked` tinyint DEFAULT '0',

`Note` longtext,

`TourID` int DEFAULT '-1',

`TourGroupID` int DEFAULT '-1',

`OrderID` int DEFAULT '-1'

PRIMARY KEY (`BookingID`)

) ENGINE=InnoDB AUTO_INCREMENT=1127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Я создал представление, сгруппированное по TourGroupID (оно работает), но не знаю, как получить все OrderID и TourID? Я не могу использовать MAX, MIN в столбце идентификаторов, мне нужно получить все идентификаторы для каждой группы.

Код: Выделить всё

CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `newbookings_vw` AS
SELECT
`v`.`TourGroupID` AS `TourGroupID`,
`v`.`TourGroup` AS `TourGroup`,
`v`.`Checkin` AS `Checkin`,
`v`.`PendingAdults` AS `PendingAdults`,
`v`.`BookedAdults` AS `BookedAdults`,
`v`.`TotalAdults` AS `TotalAdults`,
`v`.`PendingChildren` AS `PendingChildren`,
`v`.`BookedChildren` AS `BookedChildren`,
`v`.`TotalChildren` AS `TotalChildren`,
`v`.`PendingBabies` AS `PendingBabies`,
`v`.`BookedBabies` AS `BookedBabies`,
`v`.`TotalBabies` AS `TotalBabies`,
`v`.`PendingSeniors` AS `PendingSeniors`,
`v`.`BookedSeniors` AS `BookedSeniors`,
`v`.`TotalSeniors` AS `TotalSeniors`,
`v`.`PendingStudents` AS `PendingStudents`,
`v`.`BookedStudents` AS `BookedStudents`,
`v`.`TotalStudents` AS `TotalStudents`,
`v`.`PendingPersons` AS `PendingPersons`,
`v`.`BookedPersons` AS `BookedPersons`,
`v`.`TotalPersons` AS `TotalPersons`,
`v`.`PendingTotal` AS `PendingTotal`,
`v`.`VerifiedTotal` AS `VerifiedTotal`,
`v`.`Total` AS `Total`,
SUBSTR(`t`.`Language`, 1, 2) AS `Language`/*,BIN, BIY*/
FROM
((SELECT
`n`.`TourGroupID` AS `TourGroupID`,
`n`.`TourGroup` AS `TourGroup`,
DATE_FORMAT(`n`.`Checkin`, '%Y-%m-%d %H:%i') AS `Checkin`,
SUM(IFNULL(`n`.`PendingAdults`, 0)) AS `PendingAdults`,
SUM(IFNULL(`y`.`BookedAdults`, 0)) AS `BookedAdults`,
SUM((IFNULL(`y`.`BookedAdults`, 0) + IFNULL(`n`.`PendingAdults`, 0))) AS `TotalAdults`,
SUM(IFNULL(`n`.`PendingChildren`, 0)) AS `PendingChildren`,
SUM(IFNULL(`y`.`BookedChildren`, 0)) AS `BookedChildren`,
SUM((IFNULL(`y`.`BookedChildren`, 0) + IFNULL(`n`.`PendingChildren`, 0))) AS `TotalChildren`,
SUM(IFNULL(`n`.`PendingBabies`, 0)) AS `PendingBabies`,
SUM(IFNULL(`y`.`BookedBabies`, 0)) AS `BookedBabies`,
SUM((IFNULL(`y`.`BookedBabies`, 0) + IFNULL(`n`.`PendingBabies`, 0))) AS `TotalBabies`,
SUM(IFNULL(`n`.`PendingSeniors`, 0)) AS `PendingSeniors`,
SUM(IFNULL(`y`.`BookedSeniors`, 0)) AS `BookedSeniors`,
SUM((IFNULL(`y`.`BookedSeniors`, 0) + IFNULL(`n`.`PendingSeniors`, 0))) AS `TotalSeniors`,
SUM(IFNULL(`n`.`PendingStudents`, 0)) AS `PendingStudents`,
SUM(IFNULL(`y`.`BookedStudents`, 0)) AS `BookedStudents`,
SUM((IFNULL(`y`.`BookedStudents`, 0) + IFNULL(`n`.`PendingStudents`, 0))) AS `TotalStudents`,
SUM(IFNULL(`n`.`PendingPersons`, 0)) AS `PendingPersons`,
SUM(IFNULL(`y`.`BookedPersons`, 0)) AS `BookedPersons`,
SUM((IFNULL(`y`.`BookedPersons`, 0) + IFNULL(`n`.`PendingPersons`, 0))) AS `TotalPersons`,
CONCAT(ROUND(SUM(IFNULL(`n`.`PendingTotal`, 0)), 2),  ' €') AS PendingTotal,
CONCAT(ROUND(SUM(IFNULL(`y`.VerifiedTotal, 0)), 2), ' €') AS VerifiedTotal,
CONCAT(ROUND(SUM((IFNULL(y.VerifiedTotal, 0) + IFNULL(n.PendingTotal, 0))), 2), ' €') AS Total /*,n.BIDsN as BIN, y.BIDsY  as BIY*/
FROM
((SELECT
g.Name AS TourGroup,
DATE_FORMAT(b.Checkin, '%Y-%m-%d %H:%i') AS Checkin,
b.TourGroupID AS TourGroupID,
SUM(b.Adults) AS PendingAdults,
SUM(b.Children) AS PendingChildren,
SUM(b.Babies) AS PendingBabies,
SUM(b.Seniors) AS PendingSeniors,
SUM(b.Students) AS PendingStudents,
SUM(b.TotalPersons) AS PendingPersons,
CONCAT(ROUND(SUM(b.TotalToPay), 2), ' €') AS PendingTotal,
g.Name AS Name /*,group_concat(BookingID separator ',') as BIDsN*/
FROM
(bookings b
JOIN tourgroups `g` ON ((b.TourGroupID = g.TourGroupID)))
WHERE
(b.Checked = 0)
GROUP BY DATE_FORMAT(b.Checkin, '%Y-%m-%d %H:%i') , b.TourGroupID , g.Name) n
LEFT JOIN (SELECT
b.TourGroupID AS TourGroupID,
DATE_FORMAT(b.Checkin, '%Y-%m-%d %H:%i') AS `Checkin`,
SUM(b.Adults) AS BookedAdults,
SUM(b.Children) AS BookedChildren,
SUM(b.Babies) AS BookedBabies,
SUM(b.Seniors) AS BookedSeniors,
SUM(b.Students) AS BookedStudents,
SUM(b.TotalPersons) AS BookedPersons,
CONCAT(ROUND(SUM(b.TotalToPay), 2), ' €') AS VerifiedTotal,
g.`Name` AS Name /*,group_concat(BookingID separator ',') as BIDsY*/
FROM
(bookings b
JOIN tourgroups `g` ON ((b.TourGroupID = g.TourGroupID)))
WHERE
(b.Checked = 1)
GROUP BY DATE_FORMAT(b.Checkin, '%Y-%m-%d %H:%i') , b.TourGroupID , g.Name) y ON (((y.TourGroupID = n.TourGroupID)
AND (y.Name = n.Name)
AND (DATE_FORMAT(y.Checkin, '%Y-%m-%d %H:%i') = DATE_FORMAT(n.Checkin, '%Y-%m-%d %H:%i')))))
GROUP BY n.Name , n.TourGroupID , DATE_FORMAT(n.Checkin, '%Y-%m-%d %H:%i')
ORDER BY DATE_FORMAT(n.Checkin, '%Y-%m-%d %H:%i')) v
JOIN tourgroups `t` ON ((v.TourGroupID = t.TourGroupID)))
ORDER BY v.Checkin

Я читал, что Group_CONCAT может объединять идентификаторы:

Код: Выделить всё

group_concat(OrderID separator ',')

но это дает мне:

Код: Выделить всё

Error Code: 1055. Expression #25 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'n.BIDsN' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Примечание. Этот запрос будет использоваться на веб-сайте на общем хосте (для начала), поэтому я не знаю, позволяет ли хост мне отключить only_full_group_by.
Как изменить приведенный выше SQL-запрос, чтобы он также включал идентификаторы TourID и OrderID для каждой группы?
Спасибо,
Стив>

Подробнее здесь: https://stackoverflow.com/questions/783 ... m-group-by