Как написать запрос

[Viktor Zull]

Есть таблица с данными о работниках Pers со структурой: tabn (табельный номер работника),
fio(фамилия работника):
--------------
| tabn | fio |
---------------
| 1 |Шилов|
| 2 |Гусев|
| 3 |Зуев |
---------------

Каждый работник делет ежедневный отчет за каждый день. Каждый работник за день может сделать до 2-х отчетов или не сделать отчетов вообще. Данные об отчетах хранятся в таблице reps со
структурой: nrep(номер отчета), tabn(табельный номер сотрудника сделавшего отчет),
date(дата):
--------------------------
| nrep | tabn | date |
--------------------------
| 1 | 1 |01.01.2005|
| 2 | 2 |01.01.2005|
| 3 | 3 |01.01.2005|
| 4 | 3 |01.01.2005|
| 5 | 1 |02.01.2005|
| 6 | 2 |02.01.2005|
| 7 | 1 |03.01.2005|
| 8 | 2 |03.01.2005|
--------------------------

Необходимо создать запрос в результате которого будет получено сколько отчетов за каджую дату сделал каждый работник, т.е. исходя из услови должна получится таблица типа:

--------------------------
| fio | date | kolvo |
---------------------------
|Шилов |01.01.2005| 1 |
|Шилов |02.01.2005| 1 |
|Шилов |03.01.2005| 1 |
|Гусев |01.01.2005| 1 |
|Гусев |02.01.2005| 1 |
|Гусев |03.01.2005| 1 |
|Зуев |01.01.2005| 2 |
|Зуев |02.01.2005| 0 |
|Зуев |03.01.2005| 0 |
---------------------------

[Oscar]

pers

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

p_id    name

  1      Шилов
  2      Гусев
  3      Зуев

days

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

d_id   day

  1     01.01.2005
  2     02.01.2005
  3     03.01.2005

reps

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

r_id    d_id    p_id

 1        1        1
 2        1        2
 3        1        3
 4        1        3
 5        2        1
 6        2        2
 7        3        1
 8        3        2

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

SELECT p.name, d.day, count(r.p_id) AS count
FROM reps r
RIGHT JOIN pers p, days d
ON p.p_id = r.p_id
AND d.d_id =r.d_id
GROUP BY p.name, d.day

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

name          day       count

Гусев      01.01.2005     1
Гусев      02.01.2005     1
Гусев      03.01.2005     1
Зуев       01.01.2005     2
Зуев       02.01.2005     0
Зуев       03.01.2005     0
Шилов      01.01.2005     1
Шилов      02.01.2005     1
Шилов      03.01.2005     1

Если БД поддерживает вложенные запросы, или же не хочется создавать таблицу day статично, можно создать её динамично:

SELECT DISTINCT r.date FROM repsco r



P.S. Вы не пытались сортировать по дате, представленной в таком формате? Очень даже занятно получается ....
MySQL: Date and Time Types

[Viktor Zull]

Oscar, благодарю за то что ответили на мой вопрос. Насколько я понял, мне нужно создать еще таблицу с датами, где каждой дате должен соответствовать код. Дело в том, что на самом деле база с которой я работаю очень большая и содержит данные с 2000 по 2005 год. Я не могу создать отдельную таблицу. Подскажите, как мне решить эту проблему. Как создать таблицу динамично?

[Oscar]

Viktor Zull,

1. Какая БД?
2. Какой язык программирования вызывает запросы к бызе?

[Oscar]

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

CREATE TEMPORARY TABLE days (d_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (d_id)) SELECT DISTINCT r.date AS day FROM repsco r;
CREATE TEMPORARY TABLE reps SELECT * FROM repsco;
UPDATE reps r, days d SET r.date = d.d_id WHERE r.date=d.day;

SELECT p.fio, d.day, count(r.tabn) AS count
FROM reps r
RIGHT JOIN pers p, days d
ON p.tabn = r.tabn
AND d.d_id =r.date
GROUP BY p.fio, d.day;

Это для общего случая (если БД не поддерживает вложенные запросы, например MySQL меньше 5.0, или НЕ Oracle)

Всю эту дрянь нужно выполнять одним запросом :lol:

Единственное что, я понятия не имею, когда точно удаляются "временные таблицы".
Но по практическим тестам похоже, что они удаляются сразу же после окончания этого большого запроса.

(если дописать в конце DROP TABLE reps - оно вернёт: изменено 0 рядов, или тип того)


------

Damn, I'm good :roll:

[Viktor Zull]

Я работаю с базой Microsoft SQL Server 2000. Там есть возможность создавать вложенные запросы. Как можно достич нужного результата используя вложенные запросы без создания таблицы days? Я боюсь что на создание таблицы уйдет много времени и запрос будет выполняться долго.

[AiK]

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

create view
report_dates
as
select distinct report_date from reports
go


SELECT 
	e.fio,
	r2.report_date, 
	count(r.report_id) as quan
FROM 
	reports r, 
	employee e, 
	report_dates  r2
WHERE 
	r.employee_id =* e.employee_id  
    AND r.report_date =* r2.report_date
GROUP BY 
	e.fio, 
  r2.report_date 
order by 1

[Oscar]

Не знаю, как долго создаётся View, но если AiK это советует, значит не зря

С другой стороны, если я правильно понимаю, этот запрос нужен для создания отчётов, причём за определённый временной промежуток.

Таким образом, запрос, по которому создаётся временная таблица:

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

select distinct report_date from reports 

можно ограничить: WHERE report_date > ОПРЕДЕЛЁННАЯ_ДАТА

Но как я уже говорил, с датой в таком представлении очень неудобно работать.

Например:
31.12.2004
будет БОЛЬШЕ, чем
01.01.2005

AiK, я ведь правильно понимаю,
что "=*" в Microsort SQL
это то же самое, что и "=+" в Оракле
и является короткой записью "RIGHT JOIN" ?

[Viktor Zull]

Здорово, все работает. Благодарю за гениальную идею. Подскажите мне еще как сделать
итоговую таблицу удобной для просмотра пользователем, например так:

----------------------------------------
| fio |01.01.2005|02.01.2005|03.01.2005|
----------------------------------------
|Гусев| 1 | 1 | 1 |
----------------------------------------
|Зуев | 2 | 0 | 0 |
----------------------------------------
|Шилов| 1 | 1 | 1 |
----------------------------------------

В конце я добавляю условие для группы: r2.datetime between '2005-01-01' and '2005-01-03', чтобы была возможность просматривать
данные за указанный период. Вся сложность в том что я не знаю как добавить нужное число столбцов.

[AiK]

Не знаю, как долго создаётся View

миллисекунды. По своей сути вьюха ближе к хранимой процедуре без параметров, чем к таблице.
Соответственно

можно ограничить: WHERE report_date > ОПРЕДЕЛЁННАЯ_ДАТА

неудачный совет. Эдак на каждый запрос придётся городить отдельную вьюху. Это ограничение лучше делать уже непосредственно в самом запросе, а не во вьюхе. Причём как для вьюхи, так и для таблицы, коль скоро там outer join.
А вообще вьюха + outer join это завсегда не быстро. Для увеличения производительности лучше пересмотреть структуру данных.
Один раз сконвертить, и новые данные заполнять уже так, как нужно.

| fio |01.01.2005|02.01.2005|03.01.2005|

Можно это конечно и на сервере сделать, но это большой изврат. Не ИМХО.