SQL для 3-х таблиц ?

← →
worldmen © (2005-09-19 12:49) [0]

Дано, таблицы (упрощенно):
Т1- таблица клиентов. Поля: idkl, Fam.

Т2, Т3- таблицы данных. Поля: id, data1, data2
Эти таблицы (Т2, Т3) одинаковые.

Нужно составить SQL таким образом: Т1 использует данные из Т2, но если есть по этому клиенту строка в Т3 - использовать данные из Т3.

← →
Os (2005-09-19 12:55) [1]

ispolsuj join i analiziruj v programme

← →
msguns © (2005-09-19 12:56) [2]

Скорее where

← →
ANB © (2005-09-19 12:56) [3]

1. ТЗ не полное. (не понятно, по какому принципу Т1 использует данные из Т2 и что вообще нужно получить в результате)
2. Читать про JOIN и EXISTS
3. Лучше так не делать (будет тормозить)

← →
ANB © (2005-09-19 12:57) [4]

К 2. И CASE

← →
Plague © (2005-09-19 12:59) [5]

напиши подробнее... какие данные лежат в t1,2 и "Т1 использует данные из Т2" это типа если idkl=t2.id? и "если есть по этому клиенту строка в Т3 " это idkl=t3.id??

← →
worldmen © (2005-09-20 11:02) [6]

Передо мной стоит задача:
есть таблица преподавателей - Т1,
в Т2 заносятся данные по начитке часов на год (заносятся в начале учебн. года) - эти часы я потом делю на месяцы (10 месяцев) и знаю сколько преподаватель вычитал часов;
в Т3 - ведомость по часам- подается каждый месяц. Т.е. в Т3 есть данные за этот месяц - надо брать их.

Подробнее :
Т1 - таблица общих данных по клиенту:
| idkl | Fam | Imy | и т.д.
1 Иванов Иван ...
2 Петров ... ...
--------------------------
Т2 -
| idkl | hour1 | hour2| hour3| hour4 |
1 150 200 160 170
2 120 160 140 200
------------------------------
T3
| idkl | hour1 | hour2| hour3| hour4 |
1 20 15 30 25

для Иванова - используем часы: 20ч. 15ч. 30ч. 25ч.
а для Петрова : 120/10 160/10 140/10 200/10

связка типа - Where T1.idkl = T2.idkl - ничего недает.
А вот про это я забыл:
> 2. Читать про JOIN и EXISTS

Надо попробовать.

← →
pasha_golub © (2005-09-20 11:04) [7]

А нельзя ли T3 сделать View"хой и не мучатцо?

← →
Sergey13 © (2005-09-20 11:08) [8]

Я бы подумал о совмещении инфы из таблиц. Типа давать и план и факт. Это вроде и показательнее даже. А так... Ну ХП напиши. Одним запросом вряд ли.
ИМХО.

← →
ANB © (2005-09-20 11:23) [9]

Уточни еще :
- где в T3 номер месяца ?
- В каком виде хочешь получить запрос ?
- В середине года в T2 так и лежат часы за прошлый год или там копится сумма ?
- сколько записей в T2 и Т3 на одного преподавателя ?
Имхо - в оракле сделать одним запросом можно, потом под FB отладишь, особых наворотов тут не надо.

Еще вопросик - это, часом не домашнее задание ?

← →
Os © (2005-09-20 11:36) [10]

Процедурой в базе

Select t1.Name, t2.hour1, t3.hour1 from Table1 t1, Table2 t2
left join Table3 t3 on t3.id=t2.id
where t1.id=t2.id into :id, :t2hour, :t3houre;
if (t3hour is null) then t3houre= :t2houre/10
......

Возврат :id, :t3houre

Sergey13 очень прав пересмотри структуру

← →
Sergey13 © (2005-09-20 11:43) [11]

2[10] Os © (20.09.05 11:36)
Я имел в виду не изменение структуры (хотя и она возможна при таком раскладе), а просто совместный вывод из них и плана и факта.

← →
worldmen © (2005-09-20 12:08) [12]

> Я бы подумал о совмещении инфы из таблиц.

План на год подается раз в год (Т2), а в Т3 - ежемесячно.

Т2 -
| idkl | hour1 | hour2| hour3| hour4 | UchYear |
1 150 200 160 170 2005
2 120 160 140 200 2005
UchYear - учебный год, т.е. начало учебного года.

T3
| idkl | hour1 | hour2| hour3| hour4 | data |
1 20 15 30 25 200509

дата в формате YYYYMM.

> Еще вопросик - это, часом не домашнее задание ?

Не, на полставки в училище культуры работаю.

> Select t1.Name, t2.hour1, t3.hour1 from Table1 t1, Table2
> t2
> left join Table3 t3 on t3.id=t2.id
> where t1.id=t2.id into :id, :t2hour, :t3houre;
> if (t3hour is null) then t3houre= :t2houre/10

- А я и не знал, что так можно: if (t3hour is null) then t3houre= :t2houre/10

А это я не понял:

> Я имел в виду не изменение структуры (хотя и она возможна
> при таком раскладе), а просто совместный вывод из них и
> плана и факта.

← →
Sergey13 © (2005-09-20 12:13) [13]

2[12] worldmen © (20.09.05 12:08)
> А это я не понял:
Плановые часы ты можешь выводить отдельно. Фактические тоже. Что мешает совместить это в одном запросе. Просто фактические иногда будут пустые, если данных нет. И все сразу видно. А при том как ты хочешь сделать непонятно что это - то что должно быть или то что есть.
Это просто совет по реализации, а не ответ на твой конкретный вопрос.

← →
ANB © (2005-09-20 12:20) [14]

> worldmen © (20.09.05 12:08) [12]
- теперь ближе к делу. Запости, примерный результат того, что ты хочешь увидеть и я запостю запрос.

← →
ANB © (2005-09-20 12:23) [15]

Во, сообразил. union all тебя спасет :

select idkl, hour1, hour2, hour3, hour4 from T1, T3
where T3.idkl = T1.idkl
union all
select idkl, hour1, hour2, hour3, hour4 from T1, T2
where T2.idkl = T1.idkl and not exists (select 1 from T3 where T3.idkl = T1.idkl)

Примерно так. Дальше можешь агрегировать и по всякому изгалится с условиями.

← →
Os © (2005-09-20 12:50) [16]

> - А я и не знал, что так можно: if (t3hour is null) then t3houre= :t2houre/10

Проверено электроникой
SET TERM ^ ;

CREATE PROCEDURE TEST
RETURNS (
INAME VARCHAR(20),
IT2HOUR1 INTEGER,
IT3HOUR1 INTEGER)
AS
begin
for
Select t1.Name, t2.Hour1, t3.hour1 from Table1 t1, Table2 t2
left join table3 t3 on t3.id=t2.id
where t1.Id=t2.id into :iName, :it2Hour1, :it3Hour1
do
begin
if (it3Hour1 is null) then it2Hour1= :it2Hour1/10;
suspend;
end
end
^

← →
worldmen © (2005-09-20 17:41) [17]

> ANB ©
> - теперь ближе к делу. Запости, примерный результат того,
> что ты хочешь увидеть и я запостю запрос.
Я хочу сразу выбрать одним запросом тех кто идет по ведомости, а потом тех кого в ведомости нет, т.е. данные для нихбудут браться из часов дающихся на год (плановых).

> Sergey13 ©
> А при том как ты хочешь сделать непонятно что это - то что
> должно быть или то что есть.

Это я так хотел бы. Может я неправильно мыслю.
Я попробую все ваши советы и сообщу потом.

← →
ANB © (2005-09-20 19:23) [18]

> worldmen © (20.09.05 17:41) [17]

выбрать людей или их часы ?

← →
worldmen © (2005-09-21 11:45) [19]

Я вас, видимо, ввел в заблуждение. Мне надо было не обязательно одним Select-ом выбрать.
У меня получилось. Спасибо > ANB © .
Вот что у меня вышло:

сдесь я выбираю тех, кто не вошел в ведомость на этот месяц, т.е. часы вычисляются по плану:
select idkl, hour1, hour2, hour3, hour4
from T1
where not exists (select 1 from T3 where T1.idkl=T3.idkl and {с учетом месяца})

А потом делаю обыкновенный select из T3 (ведомости). И все. Всем спасибо. Я много узнал интересного.

← →
ANB © (2005-09-21 14:49) [20]

> select idkl, hour1, hour2, hour3, hour4
> from T1
- странно, вроде часов в T1 у тебя не было . . .

← →
worldmen © (2005-09-21 16:56) [21]

> ANB © (21.09.05 14:49) [20]
>
> > select idkl, hour1, hour2, hour3, hour4
> > from T1
> - странно, вроде часов в T1 у тебя не было

Действительно небыло. Я в реальном запросе круче сделал. А точнее:

select idkl,
(select hour1 from T2 where ....) hour1 ,
(select hour2 from T2 where ....) hour2 ,
(select hour3 from T2 where ....) hour3 ,
(select hour4 from T2 where ....) hour4 ,
from T1
where not exists (select 1 from T3 where T1.idkl=T3.idkl and {с учетом месяца})
У меня там еще преподаватель может иметь две должности. Поэтому так сложно.

← →
ANB © (2005-09-21 17:19) [22]

> worldmen © (21.09.05 16:56) [21]
- нехорошо сделал. DATA_NOT_FOUND в FB не бывает ? Вообще то для скалярных запросов лучше агрегатные функции для надежности использовать. Плюс, твоя конструкция будет жутко тормозить.
Так кто лежит в T1 - преподаватели или должности ?

← →
worldmen © (2005-09-21 19:04) [23]

SQL работает, пока.... Я в базу мало людей еще ввел.
Завтра принесу структуру БД.

← →
worldmen © (2005-09-22 10:48) [24]

Табл. подразделений. Например:
1. АУП - административно управл. персонал
2. Преподаватели - (каждый преподаватель имеет две должности - Преподаватель и Концертмейстер.)
Не путать подразделения с должностями.
3. Совместители
4. ...
CREATE TABLE Podrazd (
ID_pdr INTEGER NOT NULL, - id подразделения
Name_Pdr VARCHAR(20), - назв. подр
Grafik INTEGER, - график работы
PRIMARY KEY (ID_pdr)
);

Табл. - КЛИЕНТОВ (Т1) (учителей)
CREATE TABLE Klient (
ID_KL INTEGER NOT NULL,
ID_pdr INTEGER NOT NULL,- в какое подраз входит
Tab_nom VARCHAR(20), - табельный
Fam VARCHAR(20), - фамилия
Imy VARCHAR(20),
Otch VARCHAR(20),
PRIMARY KEY (ID_KL),
FOREIGN KEY (ID_pdr)
REFERENCES Podrazd
);

Таблица - у какого клиента какая должность
Поле Prof_1 - для id преподавателя,
а Prof_2 - для id концертмейстера
Dop1..4 - дополнительная нагрузка (4 вида) выражается в % от оклада.
CREATE TABLE Kl_Prof (
ID_KL INTEGER NOT NULL,
Prof_1 INTEGER,
Prof_2 INTEGER,
Dop1 INTEGER,
Dop2 INTEGER,
Dop3 INTEGER,
Dop4 INTEGER,
FOREIGN KEY (ID_KL)
REFERENCES Klient
);

ПЕДНАГРУЗКА - Т2
CREATE TABLE PedNagr ( - Пед. нагрузка в часах на год (плановое)
ID_KL INTEGER NOT NULL,
Dn_Konc INTEGER, - дневная
Dn_Ped INTEGER,
Zao_Ped INTEGER, - заочная
Zao_Konc INTEGER,
Year INTEGER,
FOREIGN KEY (ID_KL)
REFERENCES Klient
);

Каждый преподаватель имеет 2-е должности - Преподаватель и Концертмейстер.
ВЕДОМОСТЬ - Т3 - подается ежемесячно.
CREATE TABLE Vedomost (
ID_KL INTEGER NOT NULL,
PedH INTEGER, - часы дневного отделения педагогические
KoncH INTEGER, - ч. д. о. концертмейстерские
ZPedH INTEGER, - заочное отд. ...
ZKoncH INTEGER, - ...
FOREIGN KEY (ID_KL)
REFERENCES Klient
);

У должностей - Преподаватель и Концертмейстер есть разряды.
например:
разряд | преп. | конц. | оклад
| грн/ч | грн/ч |
----------------------------------
14 3.50 3.15 280
15 4.20 3.90 310
Но табл. я оформил по другому:
разряд | грн/ч | оклад
--------------------------
14 3.50 280
14 3.15 280
15 4.20 310
15 3.90 310
Так как Учитель может иметь разряд 14 Преподавателя и 15 Концертмейстера.
CREATE TABLE Kategor ( - табл. категорий
ID_KT INTEGER NOT NULL,
ID_Pdr INTEGER NOT NULL,
Razr VARCHAR(20),
Kat_Name VARCHAR(20),
Hour FLOAT,
Oklad INTEGER,
PRIMARY KEY (ID_KT),
FOREIGN KEY (ID_Pdr)
REFERENCES Podrazd
);
Не сильно загрузил? :)

← →
Os © (2005-09-22 12:27) [25]

Структура :(((

Завтра кто-то возмет еще нагрузку и перелопачивай всю базу.
Я бы создал tabl. Должностей и туда писал бы
Id
IdPrepoda
IdRaboti - Основная, Дополнительная, Препод и т.д.
Status - дневная, заочная ....
Oklad
.....

и аналогично табель.
Все легко извлекается и никакого гемороя

← →
worldmen © (2005-09-22 18:41) [26]

> Id
> IdPrepoda
> IdRaboti - Основная, Дополнительная, Препод и т.д.
> Status - дневная, заочная ....
> Oklad

А как тогда выбрать по id какая из професий Основная, Дополнительная, Препод ?

> Os © (22.09.05 12:27) [25]
- транслитом поля обзывать некошерно.

> worldmen © (22.09.05 18:41) [26]

Преподаватель - должность - это обычная линковка (многие к многим). Тип связи (тип должности) указываешь в отдельном поле и можешь учитывать как константой, так и сделать ссылку на справочник (это более кузяво). Если нужно выбрать только основные (препод) - в запрос втыкаешь фильтр по типу.
Совет - грамотно спланируй индексы, не забудь сделать их составными иначе, когда база вырастет, все начнет тормозить.

> А как тогда выбрать по id какая из професий Основная, Дополнительная,
> Препод

Select ... from .... where id=:IdPrepoda and IdRaboti="Основная"
В данном случае IdRaboti varchar(20)

Вариантов много, хочешь список всех его професий хочешь отдельно. Все зависит от реализации класса. Данные из таблицы можно рассматривать как свойства класса, а методы в зависимости какой смысл вложить в класс. По крайней мере локализация данных и ошибок обеспечена. Плата за это дополнительный код и возможно снижение быстродействия.

> В данном случае IdRaboti varchar(20)

- совсем некошерно.

> - совсем некошерно.

Я и не говорил что это самый лучший вариант, Int веселее но не наглядно.

> Os © (23.09.05 11:53) [30]
- зато int даст возможность завести локаповое поле и выбирать значение в гриде из комбобоксика. Это так прикольно.
А если серьезно, то текст, во первых - несколько длиннее, во вторых больше граблей с его изменением или неправильным написанием.

Категорически согласен :)

> ANB © (23.09.05 11:16) [27]
> Преподаватель - должность - это обычная линковка
- это понятно, но я не смог представить, в деталях, какможно забить туда реальные данные. Например такие:
Иванов И.И.
-преподаватель 14-го разряда, (1-я основ. проф.) - 3.55 руб/ч. оклад 250 руб.
-концертмейстер 16-го разряда (2-я основ. проф.)- 4.85 руб/ч. оклад 311 руб
-классное руководство - 10% (допол. должн.)
-руководитель ПЦК - 12% (допол. должн.)
-проверка тетрадей -15% (допол. должн.)
-методист 14% (допол. проф.)

Для дополн. должности считается процент от максимального оклада преподавателя. В данном случае 311 руб.

2[33] worldmen © (27.09.05 12:03)
Чем больше читаю, тем сильнее мне кажется, что ты плохо спроетировал БД (или точнее недоработал в этом направлении). Тремя таблицами тут не обойтись, даже заная SQL в cсовершенстве.
Все исключительно ИМХО.

> worldmen © (27.09.05 12:03) [33]

Примерно так :
Имеем :
1. Список людей пусть Person (ID, Fam, Name . . .)
2. Список должностей Post (ID, Name ...)
3. Справочник типов должности Work_Type (ID, Name ...)
Вот сюда и забиваешь : "1-я основ. проф. ", "2-я основ. проф. " . . .
4. Штат (кто, где) Staff (
Person_ID,
Post_ID,
Work_Type_ID,
Оклад, норма часов и прочая бредятина
)
ЗЫ. SQL тут и правда не причем, нужно сесть и с карандашем и бумагой все продумать. Если практики маловато (я таких книжек не читал, все само дошло), почитай умные книжки по теории БД.

> Sergey13 © (27.09.05 12:10) [34]

> Тремя таблицами тут не обойтись, даже заная SQL в cсовершенстве
У меня их 25, и будет больше. Структура согласен - не супер, но все работает. И Ведомость и План. Боюсь, что будет тормозить.
И ошибок пока небыло с базой.

> ANB © (27.09.05 13:02) [35]
Псижу подумаю

> ANB © (27.09.05 13:02) [35]
> почитай умные книжки по теории БД.
Читал - О нормальных формах.

>ANB © (27.09.05 13:02) [35]
>4. Штат (кто, где) Staff (
>Person_ID,
>Post_ID,
>Work_Type_ID,
>Оклад, норма часов и прочая бредятина
>)

Структура таблицы Staff нарушает 1-ю нормальную форму.
1-я нормальная форма(1NF): Сущность находится в 1NF только тогда, когда все атрибуты содержат атомарные значения. Среди атрибутов не должно встречаться повторяющихся групп.
Например для Person_ID - Work_Type_ID имеет два значения.
Так, что Work_Type_ID, Норму часов и прочую бредятину надо в другую таблицу.
Для Норма_часов надо:
1. ID_H
2. Dnevn - на дневном отделении
3. Zaoch - на заочном отделении.

Из-за этого надо переделывать >Work_Type (ID, Name ...)
Эти троеточия как раз самые главные. И надо подумать, что туда подставить.
Предлагаю так:
Work_Type ( ID, - Person_ID, - ссылка на препода Name_WT, - наимен типа (преподаватель, концертмейстер) Norma_ID_H - на норму часов ) Хотя и здесь нарушается 1NF - Name_WT
Разбить конечно можно, но надо не забывать, что есть еще другие должности:
- совместители, административно управленческий персонал (АУП), дежурные и т.д. И их надо када-то определять.

SQL для 3-х таблиц ? Найти похожие ветки