SELECT rooms.id, rooms.name AS roomname, rooms.checkbool, CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations) ON rooms.id = organizations_rooms.id_roomsORDER BY rooms.name;
SELECT rooms.id, rooms.name AS roomname, rooms.checkbool, CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations) ON rooms.id = organizations_rooms.id_roomsWHERE 1 = (SELECT COUNT(id_rooms) FROM organizations_rooms WHERE id_rooms = rooms.id) OR organizations_rooms.enddata IS NULL ORDER BY rooms.name;
SELECT id, roomname, checkbool, orgname FROM (SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations) ON rooms.id = organizations_rooms.id_roomsWHERE 1 = (SELECT COUNT(id_rooms) FROM organizations_rooms WHERE id_rooms = rooms.id) OR organizations_rooms.enddata IS NULL UNION SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgnameFROM roomsLEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id)WHERE organizations_rooms.id_rooms IS NULL) AS subselect ORDER BY roomname;
INSERT INTO rooms VALUES(12, 'вестибюль на первом этаже', ' ', ' ', ' ', FALSE),(13, 'склад на втором этаже', ' ', ' ', ' ', FALSE),(14, 'подвал', ' ', ' ', ' ', FALSE),(15, 'комната видео-наблюдения', ' ', ' ', ' ', FALSE),(16, 'туалет', ' ', ' ', ' ', FALSE),(17, 'секретная комната 1', ' ', ' ', ' ', FALSE), -- вот этой комнаты нет в organizations_rooms(18, 'очень секретная комната 2', ' ', ' ', ' ', FALSE); -- этой тоже INSERT INTO organizations_rooms VALUES(33, 14, 12, '2015-05-12', NULL),(34, 14, 14, '2015-05-12', NULL),(35, 15, 13, '2015-05-12', NULL),(36, 16, 16, '2015-05-12', '2015-05-12'),(37, 16, 15, '2015-05-12', '2015-05-12'),(38, 15, 16, '2015-05-12', NULL);
UniMainModule()->ClientDataSetOnlyRooms->First(); while(!UniMainModule()->ClientDataSetOnlyRooms->Eof) { //предварительный сброс флажков UniMainModule()->ClientDataSetOnlyRooms->Edit(); UniMainModule()->ClientDataSetOnlyRooms->FieldByName(L"checkbool")->AsBoolean = false;//ошибка UniMainModule()->ClientDataSetOnlyRooms->Post(); UniMainModule()->ClientDataSetOnlyRooms->Next(); }//***
WHERE 1 = (SELECT COUNT(id_rooms) FROM organizations_rooms WHERE id_rooms = rooms.id) OR organizations_rooms.enddata IS NULL
SELECT id, roomname, checkbool, orgname FROM (SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations) ON rooms.id = organizations_rooms.id_roomsWHERE -- первое условие - занятая комната 1 = (SELECT COUNT(id_rooms) FROM organizations_rooms WHERE id_rooms = rooms.id AND organizations_rooms.enddata IS NULL) OR -- если не сработало - проверяем второе условие, свободная комната 0 (SELECT COUNT(id_rooms) FROM organizations_rooms WHERE id_rooms = rooms.id AND organizations_rooms.enddata IS NOT NULL) UNION -- -- и добавляем те комнаты, которых вообще еще нет в этой таблице SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgnameFROM roomsLEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id)WHERE organizations_rooms.id_rooms IS NULL) AS subselect ORDER BY roomname;
SELECT id, roomname, checkbool, orgname FROM ( SELECT -- работаем с комнатами, присутствующими в organizations_rooms rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, -- хитрый шаг: в качестве orgname берем объединение всех -- organizations.name, то есть, если хоть у одной из комнат -- есть незакрытая дата - то уже будет ясно, кто ей владеет string_agg( CASE -- если поле enddata не заполнено - то есть владелец WHEN organizations_rooms.enddata IS NULL THEN organizations.name -- иначе (enddata заполнено) - пустая строка, комната свободна ELSE '' END, '') AS orgname -- и теперь просто связываем все id с названиями через inner join FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations ) ON rooms.id = organizations_rooms.id_rooms GROUP BY roomname, rooms.ID, checkbool UNION -- добавляем те комнаты, которых вообще еще нет в таблице organizations_rooms SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgname FROM rooms LEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id) WHERE organizations_rooms.id_rooms IS NULL ) AS subselectORDER BY roomname; -- и упорядочиваем по названию комнаты
INSERT INTO organizations_rooms VALUES(33, 14, 12, '2015-05-12', '2015-05-15'),(34, 14, 14, '2015-05-12', NULL),(35, 15, 13, '2015-05-12', NULL),(36, 16, 16, '2015-05-12', '2015-05-12'),(37, 16, 15, '2015-05-12', '2015-05-12'),(38, 15, 16, '2015-05-12', '2015-05-12'),(39, 14, 16, '2015-05-12', '2015-05-12'),(40, 14, 12, '2015-05-12', NULL);
string_agg( CASE -- если поле enddata не заполнено - то есть владелец WHEN organizations_rooms.enddata IS NULL THEN organizations.name -- иначе (enddata заполнено) - пустая строка, комната свободна ELSE '' END, '') AS orgname
CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname
SELECT id, roomname, checkbool, orgname FROM ( SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, CASE WHEN organizations_rooms.enddata IS NULL THEN organizations.name ELSE '' END AS orgname FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations ) ON rooms.id = organizations_rooms.id_rooms GROUP BY roomname, rooms.ID, checkbool, organizations_rooms.enddata, organizations.name UNION SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgname FROM rooms LEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id) WHERE organizations_rooms.id_rooms IS NULL ) AS subselectORDER BY roomname;
CREATE FUNCTION stringcheck(organizations_rooms_enddata text, organizations_name text) RETURNS text AS $$ BEGIN IF organizations_rooms_enddata IS NULL RETURN organizations_name; ELSE RETURN ''; END IF; END;$$ LANGUAGE plpgsql; CREATE AGGREGATE mytest(text, text)( SFUNC=stringcheck, STYPE=text); --Потом как-то так:SELECT id, roomname, checkbool, orgname FROM ( SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, mytest(organizations_rooms.enddata, organizations.name) AS orgname
CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' ); SELECT id, roomname, checkbool, orgname FROM ( SELECT -- работаем с комнатами, присутствующими в organizations_rooms rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, -- хитрый шаг: в качестве orgname берем объединение всех -- organizations.name, то есть, если хоть у одной из комнат -- есть незакрытая дата - то уже будет ясно, кто ей владеет textcat_all( CASE -- если поле enddata не заполнено - то есть владелец WHEN organizations_rooms.enddata IS NULL THEN organizations.name -- иначе (enddata заполнено) - пустая строка, комната свободна ELSE '' END || '') AS orgname -- и теперь просто связываем все id с названиями через inner join FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations ) ON rooms.id = organizations_rooms.id_rooms GROUP BY roomname, rooms.ID, checkbool UNION -- добавляем те комнаты, которых вообще еще нет в таблице organizations_rooms SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgname FROM rooms LEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id) WHERE organizations_rooms.id_rooms IS NULL ) AS subselect ORDER BY roomname;
//В модуле данных h: public: void SortColumn(const String FieldName, bool Dir); //cppvoid TUniMainModule::SortColumn(const String FieldName, bool Dir){ if(Dir) ClientDataSetOnlyRooms->IndexName = FieldName+"_index_asc"; else ClientDataSetOnlyRooms->IndexName = FieldName+"_index_des";} //--------------------------------------------------------------------- void __fastcall TUniMainModule::UniGUIMainModuleCreate(TObject *Sender){ int i; String IndexnameAsc; String IndexnameDes; for(i = 0; i ClientDataSetOnlyRooms->FieldCount; i++) { IndexnameAsc = ClientDataSetOnlyRooms->Fields->Fields[i]->FieldName + "_index_asc"; IndexnameDes = ClientDataSetOnlyRooms->Fields->Fields[i]->FieldName + "_index_des"; TIndexOptions opts, opts1; opts1 ixDescending; ClientDataSetOnlyRooms->IndexDefs->Add(IndexnameAsc, ClientDataSetOnlyRooms->Fields->Fields[i]->FieldName, opts); ClientDataSetOnlyRooms->IndexDefs->Add(IndexnameDes, ClientDataSetOnlyRooms->Fields->Fields[i]->FieldName, opts1); } } //***//Событие сортировки для грида void __fastcall TUniFormRoomsToOrg::UniDBGridOnlyRoomsColumnSort(TUniDBGridColumn *Column, bool Direction){ UniMainModule()->SortColumn(Column->FieldName, Direction); }
CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' ); SELECT id, roomname, checkbool, orgname FROM ( SELECT -- работаем с комнатами, присутствующими в organizations_rooms rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, -- хитрый шаг: в качестве orgname берем объединение всех -- organizations.name, то есть, если хоть у одной из комнат -- есть незакрытая дата - то уже будет ясно, кто ей владеет textcat_all( CASE -- если поле enddata не заполнено - то есть владелец WHEN organizations_rooms.enddata IS NULL THEN organizations.name -- иначе (enddata заполнено) - пустая строка, комната свободна ELSE '' END || '') AS orgname -- и теперь просто связываем все id с названиями через inner join FROM rooms INNER JOIN ( organizations INNER JOIN organizations_rooms ON organizations.id = organizations_rooms.id_organizations ) ON rooms.id = organizations_rooms.id_rooms GROUP BY roomname, rooms.ID, checkbool UNION -- добавляем те комнаты, которых вообще еще нет в таблице organizations_rooms SELECT rooms.id AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgname FROM rooms LEFT JOIN organizations_rooms ON (organizations_rooms.id_rooms = rooms.id) WHERE organizations_rooms.id_rooms IS NULL UNION -- добавляем те комнаты, у которых в organizations_rooms поле id_organizations is NULL SELECT organizations_rooms.id_rooms AS ID, rooms.name AS roomname, rooms.checkbool AS checkbool, '' AS orgname FROM rooms INNER JOIN organizations_rooms ON rooms.id = organizations_rooms.id_rooms GROUP BY organizations_rooms.id_rooms, rooms.name, rooms.checkbool, organizations_rooms.id_organizations, organizations_rooms.enddata HAVING (((organizations_rooms.id_organizations) IS NULL) AND ((organizations_rooms.enddata) IS NULL)) ) AS subselect ORDER BY roomname;