Задача по SQL
От: msqrt84 Россия  
Дата: 19.03.06 16:22
Оценка:
Привет, всем!

Есть одна задачка. Ознакомился с ней 7 месяцев назад (на собеседовании). Решить не смог. Забыл. Сейчас вновь решил ей заняться. Ну, ни как не получается.

ДАНО:

таблица 1 с исходными данными. Есть поля, где храниться продукт (product), дата прихода продукта на склад (date), количество продукта (quantity).

Таблица 1. Исходные данные.

---------------------------------------------
product        date        quantity
---------------------------------------------
sugar        01.02.2005    15
sugar        25.04.2005    7.5
sugar        14.01.2005    11.5
salt         10.06.2004    20
flour        07.11.2003    17
---------------------------------------------



ПОЛУЧИТЬ:

написать SQL-запрос в соответствии со стандартом ANSI SQL'92, используя один оператор SELECT без вложенных подзапросов, и получить таблицу 2 с результатом выборки, где на пересечении строк и столбцов находится сумма количеств определённого продукта за конкретный год.

Таблица 1. Результат выборки.

----------------------------------------
product        2003    2004    2005
----------------------------------------
sugar                           34
salt                    20
flour           17
----------------------------------------



Всем, спасибо.
Re: Задача по SQL
От: Merle Австрия http://rsdn.ru
Дата: 19.03.06 16:40
Оценка:
Здравствуйте, msqrt84, Вы писали:

M>ПОЛУЧИТЬ:
M>написать SQL-запрос в соответствии со стандартом ANSI SQL'92, используя один оператор SELECT без вложенных подзапросов, и получить таблицу 2 с результатом выборки, где на пересечении строк и столбцов находится сумма количеств определённого продукта за конкретный год.

Cross-Tab Reports
... [RSDN@Home 1.2.0 alpha rev. 619]
Мы уже победили, просто это еще не так заметно...
Re: Задача по SQL
От: Jack128  
Дата: 19.03.06 18:30
Оценка:
Здравствуйте, msqrt84, Вы писали:

M>Привет, всем!

M>ПОЛУЧИТЬ:

M>написать SQL-запрос в соответствии со стандартом ANSI SQL'92, используя один оператор SELECT без вложенных подзапросов, и получить таблицу 2 с результатом выборки, где на пересечении строк и столбцов находится сумма количеств определённого продукта за конкретный год.

M>Таблица 1. Результат выборки.

M>
M>----------------------------------------
M>product        2003    2004    2005
M>----------------------------------------
M>sugar                           34
M>salt                    20
M>flour           17
M>----------------------------------------
M>



Это невозможно. Даже с вложенными запросами.
Re[2]: Задача по SQL
От: Пацак Россия  
Дата: 19.03.06 19:14
Оценка:
Здравствуйте, Jack128, Вы писали:

J>Это невозможно. Даже с вложенными запросами.

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

Что-то типа такого:


select 
    t.f_name as f_name,
    (select count(*) from tbl t1 where t1.f_name = t.f_name and t1.f_date between '01.01.2003' and '01.01.2004') as count_2003,
    (select count(*) from tbl t2 where t2.f_name = t.f_name and t2.f_date between '01.01.2004' and '01.01.2005') as count_2004,
    (select count(*) from tbl t3 where t3.f_name = t.f_name and t3.f_date between '01.01.2005' and '01.01.2006') as count_2005
from tbl t


Или такого:

select 
    t.f_name as f_name,
    count (case extract(year from t.f_date) when 2003 then 1 else 0) as count_2003,
    count (case extract(year from t.f_date) when 2004 then 1 else 0) as count_2004,
    count (case extract(year from t.f_date) when 2005 then 1 else 0) as count_2005
from tbl t
group by t.f_name
Ку...
Re[3]: Задача по SQL
От: Jester Канада  
Дата: 21.03.06 08:15
Оценка:
Здравствуйте, Пацак, Вы писали:

П>Здравствуйте, Jack128, Вы писали:

J>>Это невозможно. Даже с вложенными запросами.

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

П>Что-то типа такого:


П>
П>select 
П>    t.f_name as f_name,
П>    (select count(*) from tbl t1 where t1.f_name = t.f_name and t1.f_date between '01.01.2003' and '01.01.2004') as count_2003,
П>    (select count(*) from tbl t2 where t2.f_name = t.f_name and t2.f_date between '01.01.2004' and '01.01.2005') as count_2004,
П>    (select count(*) from tbl t3 where t3.f_name = t.f_name and t3.f_date between '01.01.2005' and '01.01.2006') as count_2005
П>from tbl t
П>


П>Или такого:

П>
П>select 
П>    t.f_name as f_name,
П>    count (case extract(year from t.f_date) when 2003 then 1 else 0) as count_2003,
П>    count (case extract(year from t.f_date) when 2004 then 1 else 0) as count_2004,
П>    count (case extract(year from t.f_date) when 2005 then 1 else 0) as count_2005
П>from tbl t
П>group by t.f_name
П>



Написано ж было — без подзапросов. А насчет второго варианта — я и сам к нему склоняюсь. Только sum вместо count и quantity вместо 1. В принципе, запрос получается несложный, способом copy-paste размножить его лет на двадцать вперед и всё
Re: Задача по SQL
От: Аноним  
Дата: 14.04.06 01:10
Оценка: :)
Здравствуйте, msqrt84, Вы писали:

M>Привет, всем!

M>Есть одна задачка. Ознакомился с ней 7 месяцев назад (на собеседовании). Решить не смог. Забыл. Сейчас вновь решил ей заняться. Ну, ни как не получается.

M>ДАНО:

M>таблица 1 с исходными данными. Есть поля, где храниться продукт (product), дата прихода продукта на склад (date), количество продукта (quantity).

M>Таблица 1. Исходные данные.

M>
M>---------------------------------------------
M>product        date        quantity
M>---------------------------------------------
M>sugar        01.02.2005    15
M>sugar        25.04.2005    7.5
M>sugar        14.01.2005    11.5
M>salt         10.06.2004    20
M>flour        07.11.2003    17
M>---------------------------------------------
M>



M>ПОЛУЧИТЬ:

M>написать SQL-запрос в соответствии со стандартом ANSI SQL'92, используя один оператор SELECT без вложенных подзапросов, и получить таблицу 2 с результатом выборки, где на пересечении строк и столбцов находится сумма количеств определённого продукта за конкретный год.

M>Таблица 1. Результат выборки.

M>
M>----------------------------------------
M>product        2003    2004    2005
M>----------------------------------------
M>sugar                           34
M>salt                    20
M>flour           17
M>----------------------------------------
M>



M>Всем, спасибо.

Потратил пол дня и кажется нашел решение. Зацените:

begin tran
set dateformat dmy
declare @supply table (product varchar(10), date smalldatetime, quantity numeric(10,2))
insert into @supply values ('sugar', '01.02.2005',15)
insert into @supply values ('sugar', '25.04.2005',7.5)
insert into @supply values ('sugar', '14.01.2005',11.5)
insert into @supply values ('salt', '10.06.2004',20)
insert into @supply values ('flour', '07.11.2003',17)

select * from @supply

select s.product as product,
case
when count(s3.product) > 1 then sum(s3.quantity)/sqrt(count(s3.product))
else sum(s3.quantity)
end as y2003,
case
when count(s4.product) > 1 then sum(s4.quantity)/sqrt(count(s4.product))
else sum(s4.quantity)
end as y2004,
case
when count(s5.product) > 1 then sum(s5.quantity)/sqrt(count(s5.product))
else sum(s5.quantity)
end as y2005
from @supply s
left join @supply s3
on s3.product = s.product and s3.date between '01.01.03' and '31.12.03'
left join @supply s4
on s4.product = s.product and s4.date between '01.01.04' and '31.12.04'
left join @supply s5
on s5.product = s.product and s5.date between '01.01.05' and '31.12.05'
group by s.product


rollback tran
Re: Задача по SQL
От: greenpci  
Дата: 14.04.06 01:11
Оценка:
Здравствуйте, msqrt84, Вы писали:

M>Привет, всем!

M>Есть одна задачка. Ознакомился с ней 7 месяцев назад (на собеседовании). Решить не смог. Забыл. Сейчас вновь решил ей заняться. Ну, ни как не получается.

M>ДАНО:

M>таблица 1 с исходными данными. Есть поля, где храниться продукт (product), дата прихода продукта на склад (date), количество продукта (quantity).

M>Таблица 1. Исходные данные.

M>
M>---------------------------------------------
M>product        date        quantity
M>---------------------------------------------
M>sugar        01.02.2005    15
M>sugar        25.04.2005    7.5
M>sugar        14.01.2005    11.5
M>salt         10.06.2004    20
M>flour        07.11.2003    17
M>---------------------------------------------
M>



M>ПОЛУЧИТЬ:

M>написать SQL-запрос в соответствии со стандартом ANSI SQL'92, используя один оператор SELECT без вложенных подзапросов, и получить таблицу 2 с результатом выборки, где на пересечении строк и столбцов находится сумма количеств определённого продукта за конкретный год.

M>Таблица 1. Результат выборки.

M>
M>----------------------------------------
M>product        2003    2004    2005
M>----------------------------------------
M>sugar                           34
M>salt                    20
M>flour           17
M>----------------------------------------
M>



M>Всем, спасибо.

Кажется нашел решение. Зацените:

begin tran
set dateformat dmy
declare @supply table (product varchar(10), date smalldatetime, quantity numeric(10,2))
insert into @supply values ('sugar', '01.02.2005',15)
insert into @supply values ('sugar', '25.04.2005',7.5)
insert into @supply values ('sugar', '14.01.2005',11.5)
insert into @supply values ('salt', '10.06.2004',20)
insert into @supply values ('flour', '07.11.2003',17)

select * from @supply

select s.product as product,
case
when count(s3.product) > 1 then sum(s3.quantity)/sqrt(count(s3.product))
else sum(s3.quantity)
end as y2003,
case
when count(s4.product) > 1 then sum(s4.quantity)/sqrt(count(s4.product))
else sum(s4.quantity)
end as y2004,
case
when count(s5.product) > 1 then sum(s5.quantity)/sqrt(count(s5.product))
else sum(s5.quantity)
end as y2005
from @supply s
left join @supply s3
on s3.product = s.product and s3.date between '01.01.03' and '31.12.03'
left join @supply s4
on s4.product = s.product and s4.date between '01.01.04' and '31.12.04'
left join @supply s5
on s5.product = s.product and s5.date between '01.01.05' and '31.12.05'
group by s.product


rollback tran
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.