non so se puo' andare bene, ma in SQL Server io userei un po' di common table expression per pre-confezionare alcune info...
SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE dbo.Rooms (
Id int NOT NULL PRIMARY KEY,
Room varchar(10) NOT NULL
);
CREATE TABLE dbo.Utenti (
Id int NOT NULL PRIMARY KEY,
Nome varchar(10) NOT NULL,
Cognome varchar(10) NOT NULL
);
CREATE TABLE dbo.Post (
Id_Utente int NOT NULL,
Id_Post int NOT NULL IDENTITY PRIMARY KEY,
Titolo varchar(10) NOT NULL,
Testo varchar(10) NOT NULL,
Id_Room int NOT NULL,
Data datetime NOT NULL
);
CREATE TABLE dbo.Risposte (
Id_Utente int NOT NULL,
Id_Risposta int NOT NULL IDENTITY PRIMARY KEY,
Id_Post int NOT NULL ,
Titolo varchar(10) NOT NULL,
Testo varchar(10) NOT NULL,
Id_Room int NOT NULL,
Data datetime NOT NULL
);
GO
INSERT INTO dbo.Rooms
VALUES ( 1, 'SQL' ), ( 2, 'Asp' ), ( 3, 'Sys' ), ( 4, 'Test' );
INSERT INTO dbo.Utenti
VALUES ( 1, 'na', 'Rare' ), ( 2, 'Andrea', 'Montanari' ), ( 3, 'Daniele', 'Bochicchio' );
INSERT INTO dbo.Post
VALUES ( 1, 'Titolo1', 'Testo1', 1, '20111210');
INSERT INTO dbo.Risposte
VALUES ( 2, 1, 'Titolo1.1', 'Testo1.1', 1, '20111210 00:01');
INSERT INTO dbo.Post
VALUES ( 2, 'Titolo2', 'Testo2', 2, '20111210 00:01');
INSERT INTO dbo.Risposte
VALUES ( 1, 2, 'Titolo2.1', 'Testo2.1', 2, '20111210 00:01:05'),
( 3, 2, 'Titolo2.2', 'Testo2.2', 2, '20111210 00:01:10');
INSERT INTO dbo.Post
VALUES ( 3, 'Titolo3', 'Testo3', 3, '20111210 00:01');
GO
WITH cteRPost AS (
SELECT p.Id_Room, COUNT(*) AS [Count]
, MAX(p.Data) AS [Data]
FROM dbo.Post p
GROUP BY p.Id_Room
),
cteRisp AS (
SELECT p.Id_Room, COUNT(*) AS [Count]
, MAX(p.Data) AS [Data]
FROM dbo.Risposte p
GROUP BY p.Id_Room
)
SELECT r.Room
, cp.[Count] AS [NDiscussioni]
, cr.[Count] AS [NRisposte]
, CASE WHEN cr.Data IS NULL THEN cp.Data ELSE cr.Data END AS [LastActivity]
, CASE WHEN cr.Data IS NULL THEN
(SELECT u.Cognome + ' ' + u.Nome AS [Utente]
FROM dbo.Post post
JOIN dbo.Utenti u ON u.Id = post.Id_Utente
WHERE post.Data = cp.Data
AND post.Id_Room = cp.Id_Room)
ELSE
(SELECT u.Cognome + ' ' + u.Nome AS [Utente]
FROM dbo.Risposte post
JOIN dbo.Utenti u ON u.Id = post.Id_Utente
WHERE post.Data = cr.Data
AND post.Id_Room = cr.Id_Room)
END AS [LastUser]
FROM dbo.Rooms r
LEFT JOIN cteRPost cp ON cp.Id_Room = r.Id
LEFT JOIN cteRisp cr ON cr.Id_Room = r.Id
LEFT JOIN dbo.Post p ON p.Data = cp.Data AND p.Id_Room = r.Id
LEFT JOIN dbo.Risposte ri ON ri.Data = cr.Data AND ri.Id_Room = r.Id;
GO
DROP TABLE dbo.Post, dbo.Risposte, dbo.Rooms, dbo.Utenti;
--<---------------------
Room NDiscussioni NRisposte LastActivity LastUser
---------- ------------ ----------- ----------------------- ---------------------
SQL 1 1 2011-12-10 00:01:00.000 Montanari Andrea
Asp 1 2 2011-12-10 00:01:10.000 Bochicchio Daniele
Sys 1 NULL 2011-12-10 00:01:00.000 Bochicchio Daniele
Test NULL NULL NULL NULL
