не могу записать запрос

@freeax · Регистрация: 19.09.2012

Студворк — интернет-сервис помощи студентам

Уважаемые гуру баз данных!

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

er-модель:

Кликните здесь для просмотра всего текста

@freeax · 20.01.2013, 17:13 **[ТС]**

ddl-скрипт

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
CREATE TABLE Firm 
    ( 
     firmId NUMBER (2)  NOT NULL , 
     firmName VARCHAR2 (30) 
    ) 
;
 
 
 
ALTER TABLE Firm 
    ADD CONSTRAINT "Firm PK" PRIMARY KEY ( firmId ) ;
 
 
 
CREATE TABLE Product 
    ( 
     productId NUMBER (2)  NOT NULL , 
     productName VARCHAR2 (30) 
    ) 
;
 
 
 
ALTER TABLE Product 
    ADD CONSTRAINT "Product PK" PRIMARY KEY ( productId ) ;
 
 
 
CREATE TABLE Production 
    ( 
     productionId NUMBER (2)  NOT NULL , 
     firmId NUMBER (2) , 
     productId NUMBER (2) , 
     productId1 NUMBER (2) , 
     firmId1 NUMBER (2) 
    ) 
;
 
 
 
ALTER TABLE Production 
    ADD CONSTRAINT "Production PK" PRIMARY KEY ( productionId ) ;
 
 
 
 
ALTER TABLE Production 
    ADD CONSTRAINT firmToProduction FOREIGN KEY 
    ( 
     firmId1
    ) 
    REFERENCES Firm 
    ( 
     firmId
    ) 
;
 
 
ALTER TABLE Production 
    ADD CONSTRAINT productToProduction FOREIGN KEY 
    ( 
     productId1
    ) 
    REFERENCES Product 
    ( 
     productId
    ) 
;

KuKu · 20.01.2013, 21:48

SQL
1
2
3
4
5
6
SELECT 
  * 
FROM 
  firm 
WHERE 
  firmId IN (SELECT firmId FROM production GROUP BY productId HAVING COUNT(1)>1)

Как-то так можно. Если не так покажите пример тестовых данных и результатов.

@freeax · 20.01.2013, 22:15 **[ТС]**

Сообщение от KuKu

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
SELECT 
  * 
FROM 
  firm 
WHERE 
  firmId IN (SELECT firmId FROM production GROUP BY productId HAVING COUNT(1)>1)

Как-то так можно. Если не так покажите пример тестовых данных и результатов.

вываливается с ошибкой

SQL
1
2
3
4
5
ORA-00979: NOT a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 COLUMN: 44

тестовые данные:

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE SEQUENCE firmIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Firm VALUES (firmIdSeq.nextVal,'Фирма 1');
INSERT INTO Firm VALUES (firmIdSeq.nextVal,'Фирма 2');
INSERT INTO Firm VALUES (firmIdSeq.nextVal,'Фирма 3');
INSERT INTO Firm VALUES (firmIdSeq.nextVal,'Фирма 4');
 
CREATE SEQUENCE productIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Product VALUES (productIdSeq.nextVal,'Товар 1');
INSERT INTO Product VALUES (productIdSeq.nextVal,'Товар 2');
INSERT INTO Product VALUES (productIdSeq.nextVal,'Товар 3');
INSERT INTO Product VALUES (productIdSeq.nextVal,'Товар 4');
 
CREATE SEQUENCE productionIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Production VALUES (productionIdSeq.nextVal,1,1);
INSERT INTO Production VALUES (productionIdSeq.nextVal,1,2);
INSERT INTO Production VALUES (productionIdSeq.nextVal,2,1);
INSERT INTO Production VALUES (productionIdSeq.nextVal,2,2);
INSERT INTO Production VALUES (productionIdSeq.nextVal,3,3);
INSERT INTO Production VALUES (productionIdSeq.nextVal,4,2);
INSERT INTO Production VALUES (productionIdSeq.nextVal,4,1);
INSERT INTO Production VALUES (productionIdSeq.nextVal,3,4);

на выходе должно быть :

SQL
1
2
Фирма 1
Фирма 2

т.к. у них одинаковая продукция (Товар 1 и Товар 2)

@cygapb-007 · 20.01.2013, 22:24

в mssql у меня получилось вроде бы, но как-то извращенно... однако работает...

Кликните здесь для просмотра всего текста

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
create table firm (idf int,firm varchar(20)) 
create table prod (idp int,prod varchar(20))
create table make (lf int, lp int)
GO
insert into firm values (1,'firm1'),(2,'firm2'),(3,'firm3'),(4,'firm4'),(5,'firm5'),(6,'firm6')
insert into prod values (1,'prod1'),(2,'prod2'),(3,'prod3'),(4,'prod4'),(5,'prod5'),(6,'prod6')
insert into make values (1,2),(1,3),(2,2),(3,3),(3,2),(4,2),(5,2),(6,3),(4,1),(6,2)
GO
 
create function prod_fn(@firm int, @group int) returns nvarchar(4000) 
as begin 
    declare @ret nvarchar(4000)='', @id int
    declare qry cursor fast_forward
    for select m.lp
        from make m
        where m.lf=@firm
        order by m.lp
    open qry
    fetch next from qry into @id
    while @@FETCH_STATUS=0 begin
        select @ret = @ret + ' ' + cast(@id as nvarchar(20))
        fetch next from qry into @id
    end
    close qry
    deallocate qry
    --ISNULL(nullif(@group,0),@firm), 
    return @ret
end
GO
 
select f.*, p.* 
    from make m 
    join firm f on f.idf=m.lf
    join prod p on p.idp=m.lp
    order by m.lf,m.lp
 
GO
select 
    DENSE_RANK() over(order by dbo.prod_fn(f.idf,0)) grp,
    dbo.prod_fn(f.idf,0)grp_det, 
    f.*
from firm f
order by grp,f.idf
 
drop function prod_fn
drop table make
drop table prod
drop table firm

на данных

Code
1
2
3
4
5
6
7
8
9
10
11
12
idf         firm                 idp         prod
----------- -------------------- ----------- --------------------
1           firm1                2           prod2
1           firm1                3           prod3
2           firm2                2           prod2
3           firm3                2           prod2
3           firm3                3           prod3
4           firm4                1           prod1
4           firm4                2           prod2
5           firm5                2           prod2
6           firm6                2           prod2
6           firm6                3           prod3

выдает

Code
1
2
3
4
5
6
7
8
grp                  grp_det    idf         firm
-------------------- ---------- ----------- --------------------
1                     1 2       4           firm4
2                     2         2           firm2
2                     2         5           firm5
3                     2 3       1           firm1
3                     2 3       3           firm3
3                     2 3       6           firm6

@freeax · 20.01.2013, 22:29 **[ТС]**

Сообщение от cygapb-007

в mssql у меня получилось вроде бы, но как-то извращенно... однако работает...

эх, почему в оракле не робит...

@cygapb-007 · 20.01.2013, 22:38

сорь, с ораклом не знаком)

@freeax · 21.01.2013, 07:09 **[ТС]**

вот такой вопрос, а как вывести фирмы и продукцию, которую они производят?

Например:

Фирма 1 Товар 1
Фирма 1 Товар 2
Фирма 2 Товар 1
Фирма 2 Товар 2
Фирма 3 Товар 3
Фирма 3 Товар 4
Фирма 4 Товар 1
Фирма 4 Товар 2

@freeax · 21.01.2013, 07:17 **[ТС]**

поправил модель:

Кликните здесь для просмотра всего текста

ddl-скрипт:

Кликните здесь для просмотра всего текста

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
CREATE TABLE Firm 
    ( 
     firmId NUMBER (2)  NOT NULL , 
     firmName VARCHAR2 (30) 
    ) 
;
 
 
 
ALTER TABLE Firm 
    ADD CONSTRAINT "Firm PK" PRIMARY KEY ( firmId ) ;
 
 
 
CREATE TABLE Product 
    ( 
     productId NUMBER (2)  NOT NULL , 
     productName VARCHAR2 (30) 
    ) 
;
 
 
 
ALTER TABLE Product 
    ADD CONSTRAINT "Product PK" PRIMARY KEY ( productId ) ;
 
 
 
CREATE TABLE Production 
    ( 
     productionId NUMBER (2)  NOT NULL , 
     firmId NUMBER (2) , 
     productId NUMBER (2) , 
     Volume NUMBER (4) 
    ) 
;
 
 
 
ALTER TABLE Production 
    ADD CONSTRAINT "Production PK" PRIMARY KEY ( productionId ) ;
 
 
 
 
ALTER TABLE Production 
    ADD CONSTRAINT firmToProduction FOREIGN KEY 
    ( 
     firmId
    ) 
    REFERENCES Firm 
    ( 
     firmId
    ) 
;
 
 
ALTER TABLE Production 
    ADD CONSTRAINT productToProduction FOREIGN KEY 
    ( 
     productId
    ) 
    REFERENCES Product 
    ( 
     productId
    ) 
;

тестовые данные:

Кликните здесь для просмотра всего текста

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE SEQUENCE firmIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 1');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 2');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 3');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 4');
 
CREATE SEQUENCE productIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 1');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 2');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 3');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 4');
 
CREATE SEQUENCE productionIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,1,1,25);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,1,2,12);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,2,1,34);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,2,2,65);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,3,3,34);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,4,2,2);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,4,1,78);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,3,4,94);

@freeax · 21.01.2013, 07:18 **[ТС]**

Помогите плиз!!!!!!

@turbanoff · 21.01.2013, 07:29

Самый обычный join, без всяких выдумок:

SQL
1
2
3
4
SELECT *
FROM Firm, Product, Production
WHERE Firm.firmId = Production.firmId
  AND Production.productId = Product.productId

@freeax · 21.01.2013, 07:42 **[ТС]**

Сообщение от turbanoff

Самый обычный join, без всяких выдумок:

Спасибо большое!!!
Подскажите, как вывести фирмы у которых одинаковая продукция?

Пример:

Фирма 1
Фирма 2
Фирма 4

@turbanoff · 21.01.2013, 08:07

Нужен более точный критерий. Одинаковая продукция - слишком растяжимое понятие.
Нужны фирмы, у которых есть хотя бы один общий продукт?
Или, фирмы, у которых продукция в точности совпадает?

@freeax · 21.01.2013, 08:09 **[ТС]**

Сообщение от turbanoff

Нужен более точный критерий. Одинаковая продукция - слишком растяжимое понятие.
Нужны фирмы, у которых есть хотя бы один общий продукт?
Или, фирмы, у которых продукция в точности совпадает?

В точности совпадает

@Kateren · 21.01.2013, 11:05

по последнему вопросу можно попробовать вот так:

select max(c.firmname), max(b.productname)
from production a, product b, firm c
where a.productid = b.productid and a.firmid = c.firmid
group by a.firmid, a.productid
order by a.firmid, a.productid

Добавлено через 11 минут
оп. . .
немного протупил. . .

KuKu · 21.01.2013, 11:48

freeax, покажите пример входных данных и какой результат хотите увидеть. Непонятно как выводить - для конкретной фирмы, для всех фирм или как? А так смотрите full join.

@freeax · 21.01.2013, 15:02 **[ТС]**

Сообщение от KuKu

freeax, покажите пример входных данных и какой результат хотите увидеть. Непонятно как выводить - для конкретной фирмы, для всех фирм или как? А так смотрите full join.

данные:

Кликните здесь для просмотра всего текста

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE SEQUENCE firmIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 1');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 2');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 3');
INSERT INTO Firm VALUES (firmIdSeq.NEXTVAL,'Фирма 4');
 
CREATE SEQUENCE productIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 1');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 2');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 3');
INSERT INTO Product VALUES (productIdSeq.NEXTVAL,'Товар 4');
 
CREATE SEQUENCE productionIdSeq START WITH 1 INCREMENT BY 1;
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,1,1,25);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,1,2,12);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,2,1,34);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,2,2,65);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,3,3,34);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,4,2,2);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,4,1,78);
INSERT INTO Production VALUES (productionIdSeq.NEXTVAL,3,4,94);

На выходе примерно следующее:
Фирма 1 Товар 1
Фирма 1 Товар 2
Фирма 2 Товар 1
Фирма 2 Товар 2
Фирма 4 Товар 1
Фирма 4 Товар 2

Смысл такой, что выводятся фирмы, у которых товары в точности совпадают

KuKu · 21.01.2013, 17:28

Можно через попу:

SQL
1
2
3
4
5
WITH buf AS
(
  SELECT t.firmid, listagg(t.productid, ',') WITHIN GROUP (ORDER BY t.productid) hash FROM Production t GROUP BY t.firmid
)
SELECT t1.firmid, p.productid FROM buf t1, buf t2, production p WHERE t1.firmid != t2.firmid AND t1.hash = t2.hash AND p.firmid=t1.firmid

Ща оракла под рукой нет, чтоб человеческое писать. Если скорость не очень актуальна, то как-то так сойдет.

@freeax · 22.01.2013, 22:27 **[ТС]**

Сообщение от KuKu

Можно через попу:

СПАСИБО ОГРОМНОЕ!!!!
главное, что работает!!!

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

нужно вывести товары, которые производятся всеми фирмами

кручусь вокруг кода и не могу с мертвой точки сдвинутся:

Oracle 11 SQL
1
2
3
SELECT COUNT(1)
FROM production
WHERE (SELECT COUNT(*) FROM firm) = ANY(SELECT COUNT(*) FROM production p GROUP BY p.productid);

@cygapb-007 · 22.01.2013, 23:45

может сработает)? в mssql влёт..)

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare @Firm table (firm_id int,firm varchar(20)) 
declare @Product table (prod_id int,prod varchar(20))
declare @Production table (firm_id int, prod_id int)
 
insert into @Firm values (1,'firm1'),(2,'firm2'),(3,'firm3'),(4,'firm4')
insert into @Product values (1,'prod1'),(2,'prod2'),(3,'prod3'),(4,'prod4')
insert into @Production values (1,1),(1,2),(2,1),(2,2),(3,2),(4,2),(4,1),(3,4)
 
select p.*
from @Product p
cross apply (
    select min(isnull(m.firm_id,-1)) firm_id
        from @Firm f
        left join @Production m on m.firm_id=f.firm_id and m.prod_id = p.prod_id
    ) a
where a.firm_id>0

Code
1
2
3
prod_id     prod
----------- --------------------
2           prod2

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Desktop (MinGW): Создаём пустое окно с нуля для 2D-графики на SDL3, Си и C++ 8Observer8 10.03.2026 Содержание блога Финальные проекты на Си и на C++: hello-sdl3-c. zip hello-sdl3-cpp. zip Результат:	Установка CMake и MinGW 13.1 для сборки С и C++ приложений из консоли и из Qt Creator в EXE 8Observer8 10.03.2026 Содержание блога MinGW - это коллекция инструментов для сборки приложений в EXE. CMake - это система сборки приложений. Здесь описаны базовые шаги для старта программирования с помощью CMake и. . .	Как дизайн сайта влияет на конверсию: 7 решений, которые реально повышают заявки Neotwalker 08.03.2026 Многие до сих пор воспринимают дизайн сайта как “красивую оболочку”. На практике всё иначе: дизайн напрямую влияет на то, оставит человек заявку или уйдёт через несколько секунд. Даже если у вас. . .	Модульная разработка через nuget packages DevAlt 07.03.2026 Сложившийся в . Net-среде способ разработки чаще всего предполагает монорепозиторий в котором находятся все исходники. При создании нового решения, мы просто добавляем нужные проекты и имеем. . .
Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .	SDL3 для Web (WebAssembly): Идентификация объектов на Box2D v3 - использование userData и событий коллизий 8Observer8 02.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-collision-events-sdl3-c. zip Сканируйте QR-код на мобильном и вы увидите, что появится джойстик для управления главным героем. . . .

@freeax 3 / 3 / 0 Регистрация: 19.09.2012 Сообщений: 151

	не могу записать запрос 20.01.2013, 13:15. Показов 1972. Ответов 20 Метки нет (Все метки) Уважаемые гуру баз данных! Необходимо создать запрос: вывести фирмы, у которых одна и та же производимая продукция. Уже все мозги сломал, помогите плиз!!!! er-модель: Кликните здесь для просмотра всего текста 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.01.2013, 22:38
	сорь, с ораклом не знаком) 0

@freeax 3 / 3 / 0 Регистрация: 19.09.2012 Сообщений: 151
	21.01.2013, 07:09 [ТС]
	вот такой вопрос, а как вывести фирмы и продукцию, которую они производят? Например: Фирма 1 Товар 1 Фирма 1 Товар 2 Фирма 2 Товар 1 Фирма 2 Товар 2 Фирма 3 Товар 3 Фирма 3 Товар 4 Фирма 4 Товар 1 Фирма 4 Товар 2 0

@freeax 3 / 3 / 0 Регистрация: 19.09.2012 Сообщений: 151
	21.01.2013, 07:18 [ТС]
	Помогите плиз!!!!!! 0

@turbanoff 4094 / 3828 / 745 Регистрация: 18.05.2010 Сообщений: 9,331 Записей в блоге: 12
	21.01.2013, 08:07
	Нужен более точный критерий. Одинаковая продукция - слишком растяжимое понятие. Нужны фирмы, у которых есть хотя бы один общий продукт? Или, фирмы, у которых продукция в точности совпадает? 0

@Kateren 14 / 14 / 0 Регистрация: 25.10.2012 Сообщений: 44
	21.01.2013, 11:05
	по последнему вопросу можно попробовать вот так: select max(c.firmname), max(b.productname) from production a, product b, firm c where a.productid = b.productid and a.firmid = c.firmid group by a.firmid, a.productid order by a.firmid, a.productid Добавлено через 11 минут оп. . . немного протупил. . . 1

KuKu 1563 / 1041 / 94 Регистрация: 17.04.2009 Сообщений: 2,995
	21.01.2013, 11:48
	freeax, покажите пример входных данных и какой результат хотите увидеть. Непонятно как выводить - для конкретной фирмы, для всех фирм или как? А так смотрите full join. 0