SQL-Abfrage ausschliesslich auf das Ergebnis einer SQL-Abfrage

[Gerald]

Hallo,

ich habe eine Datenbank mit 2 Tabellen:
1) Objekte (die Namen können öfter vorkommen)
2) Leistungen (beziehen sich auf Objekte, stehen unter Vertrag oder nicht)

Ich hätte nun gerne eine Übersicht, zu welchen Objektnamen es wieviele Objekte gibt, die mindestens 1 Leistung unter Vertrag haben.


Hier einmal eine auf das Wesentliche reduzierte Beispiel-DB:

USE [master]
GO
CREATE DATABASE [Test2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Test2', FILENAME = N'/var/opt/mssql/data/Test2.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Test2_log', FILENAME = N'/var/opt/mssql/data/Test2_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

USE [Test2]
GO
CREATE TABLE [dbo].[Objekte](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Objekte] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Leistungen](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IdObjekt] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Vertrag] [bit] NOT NULL,
CONSTRAINT [PK_Leistungen] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Leistungen] ON
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (2, 1, N'L1_1', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (3, 2, N'L1_2', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (4, 2, N'L2_2', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (5, 2, N'L3_2', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (6, 3, N'L1_3', 0)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (7, 4, N'L1_4', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (8, 4, N'L2_4', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (9, 4, N'L3_4', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (10, 4, N'L4_4', 1)
GO
INSERT [dbo].[Leistungen] ([Id], [IdObjekt], [Name], [Vertrag]) VALUES (11, 5, N'L1_5', 0)
GO
SET IDENTITY_INSERT [dbo].[Leistungen] OFF
GO

SET IDENTITY_INSERT [dbo].[Objekte] ON
GO
INSERT [dbo].[Objekte] ([Id], [Name]) VALUES (1, N'O1')
GO
INSERT [dbo].[Objekte] ([Id], [Name]) VALUES (2, N'O2')
GO
INSERT [dbo].[Objekte] ([Id], [Name]) VALUES (3, N'O3')
GO
INSERT [dbo].[Objekte] ([Id], [Name]) VALUES (4, N'O2')
GO
INSERT [dbo].[Objekte] ([Id], [Name]) VALUES (5, N'O1')
GO
SET IDENTITY_INSERT [dbo].[Objekte] OFF
GO

ALTER TABLE [dbo].[Leistungen] WITH CHECK ADD CONSTRAINT [FK_Leistungen_Objekte] FOREIGN KEY([IdObjekt])
REFERENCES [dbo].[Objekte] ([Id])
GO
ALTER TABLE [dbo].[Leistungen] CHECK CONSTRAINT [FK_Leistungen_Objekte]
GO

USE [master]
GO
ALTER DATABASE [Test2] SET READ_WRITE
GO


Mit der Abfrage
select o.Name, o.Id from Objekte o
join Leistungen l on o.Id = l.IdObjekt
where l.Vertrag = 1
group by o.Name, o.Id
bekomme ich das richtige Ergebnis
O1 1
O2 2
O2 4
was interpretiert werden kann als
für den Objektnamen O1 gibt es das Objekt mit der Id=1, das Leistungen unter Vertrag hat
für den Objektnamen O2 gibt es das Objekt mit der Id=2, das Leistungen unter Vertrag hat
für den Objektnamen O2 gibt es das Objekt mit der Id=4, das Leistungen unter Vertrag hat


Ich hätte aber gerne das Ergbnis
O2 2
O1 1
was interpretiert werden kann als
für den Objektnamen O2 gibt es zwei Objekte, die Leistungen unter Vertrag haben
für den Objektnamen O1 gibt es ein Objekt, das Leistungen unter Vertrag hat


Wie kann ich um meine SELECT-Abfrage eine weitere Abfrage herumbauen, die zum richtigen Ergebnis führt?
Wenn ich eine VIEW anlege, die wie meine SELECT-Abfrage aufgebaut ist kann ich einfach per
select Name, count(*)
from View_1
group by (Name)
das gewünschte Ergebnis erzielen, aber wie mache ich das ohne VIEW?


Oder kann ich meine SELECT-Abfrage sogar direkt ändern, um das gewünschte Ergebnis zu bekommen?


Danke für jede Unterstützung!!!

 

[dabadepdu]

Oder kann ich meine SELECT-Abfrage sogar direkt ändern, um das gewünschte Ergebnis zu bekommen?

Wenn ich das richtig sehe, führt bereits Deine erste Abfrage durch das Group By ohne Aggregatfunktion zu einem "DISTINCT" Ergebnis. Es werden so nur die eindeutigen Vorkommen aufgelistet, nicht gezählt.
Ergänzt du als Aggregatfunktion z.B. "count(*)" als weitere Spalte, dann werden alle identisch gruppierbaren Einträge gezählt und diese Anzahl mit ausgegeben.

 

[Gerald]

Nein, leider nicht, ergänze ich die SELECT-Abfrage um count(*) zu
select o.Name, o.Id, count(*) from Objekte o

so erhalte ich nur zusätzlich (letzte Spalte) die jeweilige Anzahl der Leistungen, die für das jeweilige Objekt unter Vertrag sind
O1 1 1
O2 2 3
O2 4 4

 

[dabadepdu]

so erhalte ich nur zusätzlich die jeweilige Anzahl der Leistungen

Ah, ich hab nicht gründlich genug gelesen.
Deine Objekte haben eine eindeutige ID, aber der Name ist nicht eindeutig. Das ist etwas ungewöhnlich, zumindest bei der Benamung. Wäre Objekte.Name eher sowas wie Objekte.Klasse, Objekte.Typ oder Objekte.Vertragsart, wäre es offensichtlicher.
Du willst auf Objekt-Ebene zählen, aber abhängig von bestimmten Bedingungen in Leistungen.

Versuch mal das:

select o.Name, count(*) from Objekte o
 where exists(
       Select 1 from Leistungen l 
        where o.Id = l.IdObjekt
          and l.Vertrag = 1)
 group by o.Name

 

[Gerald]

Super, das ist es, vielen Dank!

 

[ukulele]

Ohne es jetzt selbst alles gelesen zu haben ;-) nochmal zur eigentlichen Frage:

Wie kann ich um meine SELECT-Abfrage eine weitere Abfrage herumbauen, die zum richtigen Ergebnis führt?

a) Wie du schon gut erkannt hast eine View anlegen und ein Select darauf machen.
b)

SELECT alias.* FROM ( SELECT * FROM tabelle ) alias

c)

WITH alias AS ( SELECT * FROM tabelle ) SELECT * FROM alias

Alles hat so seine Vor- und Nachteile. WITH kann noch mehr wie Rekrusion, wird aber nicht von jedem DBMS in gleicher Weise unterstützt.

 

[Gerald]

Habe b) getestet, klappt auch bei mir , kann ich bestimmt für andere Abfragen auch verwenden, klasse, vielen Dank!