SQL Rätsel zum WochenEnde - Kombinationen ermitteln

[cbe]

kann man sicher eleganter machen, aber wie wärs mit sowas:

with tmp (auf, fert, teil) as ( values 
 ('AN160007548', '4711', 'TEIL1202480550'),
 ('AN160010602', '4712', 'TEIL1202480550'),
 ('AN160011460', '4713', 'TEIL1202480550'),
 ('AN160011863', '4714', 'TEIL1202480550'),
 ('AN160012865', '4715', 'TEIL1202480550'),
 ('AN160001605', '4716', 'TEIL1202480557'),
 ('AN160008730', '4717', 'TEIL1202480557'),
 ('AN160009457', '4718', 'TEIL1202480557'),
 ('AN160009383', '4719', 'TEIL1202560550'),
 ('AN160009468', '4720', 'TEIL1202560550'),
 ('AN160010881', '4721', 'TEIL1202560550'),
 ('AN160011506', '4722', 'TEIL1202560550'),
 ('AA160000195', '4723', 'TEIL1202460129'),
 ('AA160000195', '4724', 'TEIL1202470129'),
 ('AA160000310', '4725', 'TEIL1202460129'),
 ('AA160000310', '4726', 'TEIL1202470129'),
 ('AN160008509', '4727', 'TEIL1202490550'),
 ('AN160011380', '4728', 'TEIL1202490550'),
 ('AN160011731', '4729', 'TEIL1202490550')  )
, data  as (select distinct auf, teil from tmp order by 1, 2)
, t1 (auf, teil, teile ) as  (select auf, teil,  '                                                                                                                                                                ' from data
 union all select t2.auf, t2.teil, t2.teil concat ',' concat t1.teile  from t1, data as t2  
    where  t1.auf=t2.auf and t2.teil>substr(t1.teile, 1, 14))
, help as (select auf, max(teile) teile from t1 group by auf order by auf)
select teile, count(*), min(auf), max(auf) from help group by teile order by 2 desc

(habe die Daten hier etwas gekürzt)

Der lange leere String steht für einen längeren String, ich weiß gerade nicht auswendig, wie man den per cast o.ä. in SQL definiert

Gruß, Christian

PS. nette kleine Knobelaufgabe zum Wochenende. Gibt's das jetzt jede Woche?

[KM]

wie wäre es mit folgendem kleinen SQL-Statement:

        with tmp (Auftrag, Fertigungsauftrag, Teilenummer) as ( values
        ('AN160007548', '4711', 'TEIL1202480550'),
        ('AN160010602', '4712', 'TEIL1202480550'),
        ('AN160011460', '4713', 'TEIL1202480550'),
        ('AN160011863', '4714', 'TEIL1202480550'),
        ('AN160012865', '4715', 'TEIL1202480550'),
        ('AN160001605', '4716', 'TEIL1202480557'),
        ('AN160008730', '4717', 'TEIL1202480557'),
        ('AN160009457', '4718', 'TEIL1202480557'),
        ('AN160009383', '4719', 'TEIL1202560550'),
        ('AN160009468', '4720', 'TEIL1202560550'),
        ('AN160010881', '4721', 'TEIL1202560550'),
        ('AN160011506', '4722', 'TEIL1202560550'),
        ('AA160000195', '4723', 'TEIL1202460129'),
        ('AA160000195', '4724', 'TEIL1202470129'),
        ('AA160000310', '4725', 'TEIL1202460129'),
        ('AA160000310', '4726', 'TEIL1202470129'),
        ('AN160008509', '4727', 'TEIL1202490550'),
        ('AN160011380', '4728', 'TEIL1202490550'),
        ('AN160011731', '4729', 'TEIL1202490550'),
        ('AN150044553', '4730', 'TEIL1202490557'),
        ('AN160008758', '4731', 'TEIL1202490557'),
        ('AN160012958', '4732', 'TEIL1202490557'),
        ('AN160000404', '4733', 'TEIL1202470129'),
        ('AN160000404', '4734', 'TEIL1202580129'),
        ('AN160008753', '4735', 'TEIL1202450550'),
        ('AN160008753', '4736', 'TEIL1202480550'),
        ('AN160009822', '4737', 'TEIL1202450550'),
        ('AN160009822', '4738', 'TEIL1202480550'),
        ('AN160010442', '4739', 'TEIL1202450550'),
        ('AN160010442', '4740', 'TEIL1202480550'),
        ('AN160010989', '4741', 'TEIL1202450550'),
        ('AN160010989', '4742', 'TEIL1202480550'),
        ('AN160011490', '4743', 'TEIL1202450550'),
        ('AN160011490', '4744', 'TEIL1202480550'),
        ('AN160011758', '4745', 'TEIL1202450550'),
        ('AN160011758', '4746', 'TEIL1202480550'),
        ('AN160010900', '4747', 'TEIL1202450557'),
        ('AN160010900', '4748', 'TEIL1202560557'),
        ('AN160010021', '4749', 'TEIL1202480550'),
        ('AN160010021', '4750', 'TEIL1202570550'),
        ('AN160012837', '4751', 'TEIL1202480550'),
        ('AN160012837', '4752', 'TEIL1202570550'),
        ('AN160003196', '4753', 'TEIL1202460550'),
        ('AN160003196', '4754', 'TEIL1202480550'),
        ('AN160008991', '4755', 'TEIL1202460550'),
        ('AN160008991', '4756', 'TEIL1202480550'),
        ('AN160010253', '4757', 'TEIL1202460550'),
        ('AN160010253', '4758', 'TEIL1202480550'),
        ('AN160011833', '4759', 'TEIL1202460550'),
        ('AN160011833', '4760', 'TEIL1202480550'),
        ('AN160012758', '4761', 'TEIL1202460550'),
        ('AN160012758', '4762', 'TEIL1202480550'),
        ('AN160013057', '4763', 'TEIL1202480557'),
        ('AN160013057', '4764', 'TEIL1202490557'),
        ('AN160012213', '4765', 'TEIL1202560432'),
        ('AN160009388', '4766', 'TEIL1202450550'),
        ('AN160009388', '4767', 'TEIL1202560550'),
        ('AN160008115', '4768', 'TEIL1202450321'),
        ('AN160008115', '4769', 'TEIL1202470321'),
        ('AN160013060', '4770', 'TEIL1202450321'),
        ('AN160013060', '4771', 'TEIL1202470321'),
        ('AN160006322', '4772', 'TEIL1202450129'),
        ('AN160006322', '4773', 'TEIL1202470129'),
        ('AN160009516', '4774', 'TEIL1202470129'),
        ('AN160009516', '4775', 'TEIL1202570129'),
        ('AN160008501', '4776', 'TEIL1202450550'),
        ('AN160008501', '4777', 'TEIL1202490550'),
        ('AN160008627', '4778', 'TEIL1202450550'),
        ('AN160008627', '4779', 'TEIL1202490550'),
        ('AN160008988', '4780', 'TEIL1202450550'),
        ('AN160008988', '4781', 'TEIL1202490550'),
        ('AN160008131', '4782', 'TEIL1202460557'),
        ('AN160008131', '4783', 'TEIL1202480557'),
        ('AN160009607', '4784', 'TEIL1202460557'),
        ('AN160009607', '4785', 'TEIL1202480557'),
        ('AN160010210', '4786', 'TEIL1202460557'),
        ('AN160010210', '4787', 'TEIL1202480557'),
        ('AN160010901', '4788', 'TEIL1202480550'),
        ('AN160010901', '4789', 'TEIL1202580550'),
        ('AN160013096', '4790', 'TEIL1202480550'),
        ('AN160013096', '4791', 'TEIL1202580550'),
        ('AN160010062', '4792', 'TEIL1202490432'),
        ('AN160006030', '4793', 'TEIL1202460550'),
        ('AN160006030', '4794', 'TEIL1202490550'),
        ('AN160007787', '4795', 'TEIL1202460550'),
        ('AN160007787', '4796', 'TEIL1202490550'),
        ('AN160008384', '4797', 'TEIL1202460550'),
        ('AN160008384', '4798', 'TEIL1202490550'),
        ('AN160010677', '4799', 'TEIL1202460550'),
        ('AN160010677', '4800', 'TEIL1202490550'),
        ('AN160011800', '4801', 'TEIL1202450557'),
        ('AN160011800', '4802', 'TEIL1202490557'),
        ('AN160009597', '4803', 'TEIL1202490432'),
        ('AN160009597', '4804', 'TEIL1202580432'),
        ('AA150002206', '4805', 'TEIL1202450550'),
        ('AA150002206', '4806', 'TEIL1202470550'),
        ('AN150037024', '4807', 'TEIL1202450550'),
        ('AN150037024', '4808', 'TEIL1202470550'),
        ('AN160007280', '4809', 'TEIL1202450550'),
        ('AN160007280', '4810', 'TEIL1202470550'),
        ('AN160007545', '4811', 'TEIL1202450432'),
        ('AN160007545', '4812', 'TEIL1202470432'),
        ('AN160007919', '4813', 'TEIL1202450432'),
        ('AN160007919', '4814', 'TEIL1202470432'),
        ('AN160008171', '4815', 'TEIL1202450550'),
        ('AN160008171', '4816', 'TEIL1202470550'),
        ('AN160008630', '4817', 'TEIL1202450550'),
        ('AN160008630', '4818', 'TEIL1202470550'),
        ('AN160008631', '4819', 'TEIL1202450550'),
        ('AN160008631', '4820', 'TEIL1202470550'),
        ('AN160008655', '4821', 'TEIL1202450550'),
        ('AN160008655', '4822', 'TEIL1202470550'),
        ('AN160008690', '4823', 'TEIL1202450550'),
        ('AN160008690', '4824', 'TEIL1202470550'),
        ('AN160009727', '4825', 'TEIL1202450550'),
        ('AN160009727', '4826', 'TEIL1202470550'),
        ('AN160009973', '4827', 'TEIL1202450550'),
        ('AN160009973', '4828', 'TEIL1202470550'),
        ('AN160010259', '4829', 'TEIL1202450550'),
        ('AN160010259', '4830', 'TEIL1202470550'),
        ('AN160010524', '4831', 'TEIL1202450432'),
        ('AN160010524', '4832', 'TEIL1202470432'),
        ('AN160010860', '4833', 'TEIL1202450550'),
        ('AN160010860', '4834', 'TEIL1202470550'),
        ('AN160011152', '4835', 'TEIL1202450550'),
        ('AN160011152', '4836', 'TEIL1202470550'),
        ('AN160011663', '4837', 'TEIL1202450550'),
        ('AN160011663', '4838', 'TEIL1202470550'),
        ('AN160012021', '4839', 'TEIL1202450550'),
        ('AN160012021', '4840', 'TEIL1202470550'),
        ('AN160012567', '4841', 'TEIL1202450550'),
        ('AN160012567', '4842', 'TEIL1202470550'),
        ('AN160013069', '4843', 'TEIL1202450550'),
        ('AN160013069', '4844', 'TEIL1202470550'),
        ('AN160007361', '4845', 'TEIL1202470550'),
        ('AN160007361', '4846', 'TEIL1202570550'),
        ('AN160010756', '4847', 'TEIL1202470550'),
        ('AN160010756', '4848', 'TEIL1202570550'),
        ('AN160013006', '4849', 'TEIL1202470550'),
        ('AN160013006', '4850', 'TEIL1202570550'),
        ('AN160009826', '4851', 'TEIL1202550550'),
        ('AN160009826', '4852', 'TEIL1202570550'),
        ('AA160000308', '4853', 'TEIL1202460550'),
        ('AA160000308', '4854', 'TEIL1202470550'),
        ('AN160008965', '4855', 'TEIL1202460550'),
        ('AN160008965', '4856', 'TEIL1202550550'),
        ('AN160008615', '4857', 'TEIL1202450557'),
        ('AN160008615', '4858', 'TEIL1202470557'),
        ('AN160009253', '4859', 'TEIL1202450557'),
        ('AN160009253', '4860', 'TEIL1202470557'),
        ('AN160010339', '4861', 'TEIL1202450557'),
        ('AN160010339', '4862', 'TEIL1202470557'),
        ('AN160010367', '4863', 'TEIL1202450557'),
        ('AN160010367', '4864', 'TEIL1202470557'),
        ('AN160012498', '4865', 'TEIL1202450557'),
        ('AN160012498', '4866', 'TEIL1202470557'),
        ('AN160007450', '4867', 'TEIL1202480432'),
        ('AN160007450', '4868', 'TEIL1202570432'),
        ('AN160011330', '4869', 'TEIL1202470557'),
        ('AN160011330', '4870', 'TEIL1202580557'),
        ('AN160011489', '4871', 'TEIL1202480129'),
        ('AN160011489', '4872', 'TEIL1202570129'),
        ('AN160010817', '4873', 'TEIL1202460557'),
        ('AN160010817', '4874', 'TEIL1202470557'),
        ('AN160011518', '4875', 'TEIL1202460557'),
        ('AN160011518', '4876', 'TEIL1202470557'),
        ('AN160010019', '4877', 'TEIL1202480557'),
        ('AN160010019', '4878', 'TEIL1202550557'),
        ('AN160010019', '4879', 'TEIL1202580557'),
        ('AN160011417', '4880', 'TEIL1202470550'),
        ('AN160011417', '4881', 'TEIL1202580550'),
        ('AN160010189', '4882', 'TEIL1202450129'),
        ('AN160010189', '4883', 'TEIL1202560129'),
        ('AN160010024', '4884', 'TEIL1202450432'),
        ('AN160010024', '4885', 'TEIL1202490432'),
        ('AN160011913', '4886', 'TEIL1202450432'),
        ('AN160011913', '4887', 'TEIL1202490432')
        ),
        tmp2 (t2_Auftrag, t2_Teilenummer, curr, prev) as (
        SELECT auftrag, teilenummer,
        ROW_NUMBER() OVER (PARTITION BY auftrag ORDER BY teilenummer) AS curr,
        ROW_NUMBER() OVER (PARTITION BY auftrag ORDER BY teilenummer) -1 AS prev
        FROM tmp),
        tmp3 (t3_Auftrag, t3_Teilenummer) as (
        SELECT t2_auftrag, MAX(TRIM(TRIM(LEADING ',' FROM CAST(SYS_CONNECT_BY_PATH(TRIM(t2_teilenummer), ', ') AS VARCHAR(1024)))))
        FROM tmp2
        START WITH curr = 1
        CONNECT BY t2_auftrag = PRIOR t2_auftrag AND prev = PRIOR curr
        GROUP BY t2_auftrag)
        select t3_teilenummer as "Kombination", count(t3_auftrag) as "Anzahl Aufträge"
        from tmp3
        group by t3_teilenummer
        ORDER BY t3_teilenummer

Gruß,
KM

[cbe]

Hallo KM,
Dein Ergebnis sieht recht ähnlich aus, ist ja ein gutes Zeichen
Und da ist einiges für mich Neues drin - die Syntax mit OVER und START kenne ich noch gar nicht. Muss ich mir mal anschauen...
Gruß, Christian

[B.Hauser]

Wie wär's den damit:

with tmp (Auftrag, Fertigungsauftrag, Teilenummer) as ( values .... ),
 y as (Select Dense_Rank() Over (Partition by Auftrag Order By TeileNummer) 
                lfd, tmp.*
         From tmp) 
Select Auftrag, max(Case When lfd = 1 Then TeileNummer End),
                max(Case When lfd = 2 Then TeileNummer End),
                max(Case When lfd = 3 Then TeileNummer End) 
   from y
   Group By Auftrag
   Having Max(lfd) = 3
   Order By Auftrag;

Birgitta