Блог

Главная - Вопросы по программированию - Написание SQL-запроса для MySQL и SQL Server

Написание SQL-запроса для MySQL и SQL Server

#mysql #sql #sql-server-2005

#mysql #sql #sql-server-2005

Вопрос:

у меня есть таблица следующей структуры

 id name field1 field2 field3
1  aaa   20     30     40
2  aaa   40     50     60
3  bbb   40     50     60
4  aaa   75     55     60
5  bbb   40     50     60
6  bbb   40     50     60

Результирующий набор, который я хочу, это

 1 aaa (sum of field 1) (product of field 2) (average of field3)
2 bbb (sum of field 1) (product of field 2) (average of field3)

Я не могу узнать, как это сделать. Я попытался 

 select SUM(field1),PROD(field2),AVG(field3) from table GROUP BY name

но он не работает. Также мне нужно выяснить, как это сделать и в sql server 2005.

Комментарии:

1. На Prod вы хотите умножить все значения в группе? Все ли они положительные числа?

Ответ №1:

Для SQL Server вы могли бы использовать

 SELECT SUM(field1),
       CASE
         WHEN COUNT(CASE
                      WHEN field2 = 0 THEN 1
                    END) > 0 THEN 0
         ELSE 1
       END * CASE COUNT(CASE WHEN SIGN(field2) = -1 THEN 1 END )%2
               WHEN 0 THEN 1
               ELSE -1
             END * EXP(SUM(LOG(ABS(NULLIF(field2, 0))))),
       AVG(field3)
FROM   T
GROUP  BY name

Комментарии:

1. @Smith я проверяю это, и теперь все работает нормально, не могли бы вы описать, что вы сделали для продукта field2

2. @rahularyansharma: Это простая математика: log(A*B*...Z)=logA loB ... logZ , поэтому: A*B*...*Z=exp(logA loB ... logZ) (поиск идентификаторов логарифмов для получения дополнительных объяснений)

3. @rahularyansharma Он основан на here , но расширен для обработки нулевых и отрицательных чисел.

Ответ №2:

попробуйте:

 select name, SUM(field1),PROD(field2),AVG(field3) from table GROUP BY name

Комментарии:

1. В SQL Server нет PROD()

2. Если вы не написали вызываемый UDF PROD , PROD() функция недоступна.

3. Я предполагаю, что это то, что он имел в виду PROD() , если нет, вы можете создать для него свою собственную функцию.

Ответ №3:

 create table product(
id int identity(1,1),
_name varchar(max),
field1 int,
field2 int,
field3 int
)
create table #_name(
id int identity(1,1),
_name varchar(max),
field1 int,
field2 int,
field3 int
)
insert into product values ('aaa',20,30,40)
insert into product values ('aaa',40,50,60)
insert into product values ('bbb',40,50,60)
insert into product values ('aaa',75,55,60)
insert into product values ('bbb',20,30,40)
insert into product values ('bbb',40,50,60)
insert into product values ('ddd',40,50,60)
insert into product values ('ddd',40,50,60)
insert into product values ('ddd',40,50,60)

declare @name varchar(max);
declare @sum int,@prod int, @avg int;
declare @field2 int;
set @prod=1;

select * from product
declare pro_cursor cursor for 
select p._name from (select distinct(_name) from product)p
open pro_cursor
fetch next from pro_cursor into @name
while(@@fetch_status=0)
begin 
    declare field_cursor cursor for
    select field2 from product where _name=@name
    open field_cursor
    fetch next from field_cursor into @field2
    while(@@fetch_status=0)
    begin
        set @prod=@prod*@field2;        
    fetch next from field_cursor into @field2
    end
    close field_cursor
    deallocate field_cursor
    set @sum=(select sum(field1) from product where _name=@name);
    set @avg=(select avg(field3) from product where _name=@name);
insert into #_name values (@name,@sum,@prod,@avg)
set @prod=1;
fetch next from pro_cursor into @name
end
select * from #_name
close pro_cursor
deallocate pro_cursor
truncate table #_name