Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les élèves
qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les élèves
qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les élèves
qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
Bonjour,
olivier a écrit :
Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
"Michel__D" a écrit dans le message de
news: unl%Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
"Michel__D" <Michel.NOSPAM@orange-ft.com.invalid> a écrit dans le message de
news: unl%23aWuLKHA.220@TK2MSFTNGP02.phx.gbl...
Bonjour,
olivier a écrit :
Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
"Michel__D" a écrit dans le message de
news: unl%Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
olivier a écrit :merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
Votre requête est fausse !
Elle donne :
nom
----------------------------------------------------------------------------------------------------
TUTU
en effet prenez le jeu d'essais et vous verrez immédiatement votre erreur
:
SELECT * FROM cours ORDER BY eleve_id, date_cours
id eleve_id date_cours code_absence
------ ---------- ----------- ------------
1 1 2009-01-01 0
2 1 2009-01-07 0
3 1 2009-01-09 0
4 1 2009-01-10 0
5 2 2009-01-01 0
6 2 2009-01-07 -1
7 2 2009-01-09 0
8 2 2009-01-10 0
9 3 2009-01-01 -1
10 3 2009-01-07 -1 --> 2e absence consécutive
11 3 2009-01-09 -1
12 3 2009-01-10 0
13 4 2009-01-01 0
14 4 2009-01-07 -1
15 4 2009-01-09 -1 --> 2e absence consécutive
16 4 2009-01-10 -1
Le résultat devrait donc être 3 et 4, soit TITI et T
Voici une réponse à votre requête avec tous les détails :
WITH
T AS (SELECT C.id as cours_id, E.id as eleve_id, date_cours,
code_absence, nom,
ROW_NUMBER() OVER(PARTITION BY E.id
ORDER BY date_cours) AS N1,
ROW_NUMBER() OVER(PARTITION BY E.id, code_absence
ORDER BY date_cours) AS N2
FROM cours AS C
INNER JOIN eleves AS E
ON C.eleve_id = E.id)
SELECT cours_id, eleve_id,nom, date_cours
FROM T
WHERE N2 >= 2 and N1 >= 2 AND code_absence = -1
ORDER BY eleve_id, date_cours
qui donne :
cours_id eleve_id nom date_cours
----------- ----------- ------------- -----------------------
10 3 TITI 2009-01-07 00:00:00.000
11 3 TITI 2009-01-09 00:00:00.000
15 4 TUTU 2009-01-09 00:00:00.000
16 4 TUTU 2009-01-10 00:00:00.000
De manière générale, tous les problèmes d'ordonnancement nécessitent
l'utilisation de fonction d'ordonnancement tel que RANK ou ROW_NUMBER
Si vous ne voulez que les noms, simplifier le SELECT final avec :
DISTINCT nom.
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************
"Michel__D" a écrit dans le message
de news: unl%Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
olivier a écrit :
merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
Votre requête est fausse !
Elle donne :
nom
----------------------------------------------------------------------------------------------------
TUTU
en effet prenez le jeu d'essais et vous verrez immédiatement votre erreur
:
SELECT * FROM cours ORDER BY eleve_id, date_cours
id eleve_id date_cours code_absence
------ ---------- ----------- ------------
1 1 2009-01-01 0
2 1 2009-01-07 0
3 1 2009-01-09 0
4 1 2009-01-10 0
5 2 2009-01-01 0
6 2 2009-01-07 -1
7 2 2009-01-09 0
8 2 2009-01-10 0
9 3 2009-01-01 -1
10 3 2009-01-07 -1 --> 2e absence consécutive
11 3 2009-01-09 -1
12 3 2009-01-10 0
13 4 2009-01-01 0
14 4 2009-01-07 -1
15 4 2009-01-09 -1 --> 2e absence consécutive
16 4 2009-01-10 -1
Le résultat devrait donc être 3 et 4, soit TITI et T
Voici une réponse à votre requête avec tous les détails :
WITH
T AS (SELECT C.id as cours_id, E.id as eleve_id, date_cours,
code_absence, nom,
ROW_NUMBER() OVER(PARTITION BY E.id
ORDER BY date_cours) AS N1,
ROW_NUMBER() OVER(PARTITION BY E.id, code_absence
ORDER BY date_cours) AS N2
FROM cours AS C
INNER JOIN eleves AS E
ON C.eleve_id = E.id)
SELECT cours_id, eleve_id,nom, date_cours
FROM T
WHERE N2 >= 2 and N1 >= 2 AND code_absence = -1
ORDER BY eleve_id, date_cours
qui donne :
cours_id eleve_id nom date_cours
----------- ----------- ------------- -----------------------
10 3 TITI 2009-01-07 00:00:00.000
11 3 TITI 2009-01-09 00:00:00.000
15 4 TUTU 2009-01-09 00:00:00.000
16 4 TUTU 2009-01-10 00:00:00.000
De manière générale, tous les problèmes d'ordonnancement nécessitent
l'utilisation de fonction d'ordonnancement tel que RANK ou ROW_NUMBER
Si vous ne voulez que les noms, simplifier le SELECT final avec :
DISTINCT nom.
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************
"Michel__D" <Michel.NOSPAM@orange-ft.com.invalid> a écrit dans le message
de news: unl%23aWuLKHA.220@TK2MSFTNGP02.phx.gbl...
Bonjour,
olivier a écrit :
Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;
olivier a écrit :merci
la voici pour SQL server
SELECT T2.nom
FROM cours AS T1
INNER JOIN eleves AS T2 ON T1.eleve_id = T2.id
WHERE T1.code_absence = -1 AND T1.date_cours IN
(SELECT TOP 2 date_cours FROM cours WHERE cours.eleve_id = t2.id ORDER BY
date_cours DESC )
GROUP BY T2.nom
HAVING Count(T1.eleve_id) >= 2
Votre requête est fausse !
Elle donne :
nom
----------------------------------------------------------------------------------------------------
TUTU
en effet prenez le jeu d'essais et vous verrez immédiatement votre erreur
:
SELECT * FROM cours ORDER BY eleve_id, date_cours
id eleve_id date_cours code_absence
------ ---------- ----------- ------------
1 1 2009-01-01 0
2 1 2009-01-07 0
3 1 2009-01-09 0
4 1 2009-01-10 0
5 2 2009-01-01 0
6 2 2009-01-07 -1
7 2 2009-01-09 0
8 2 2009-01-10 0
9 3 2009-01-01 -1
10 3 2009-01-07 -1 --> 2e absence consécutive
11 3 2009-01-09 -1
12 3 2009-01-10 0
13 4 2009-01-01 0
14 4 2009-01-07 -1
15 4 2009-01-09 -1 --> 2e absence consécutive
16 4 2009-01-10 -1
Le résultat devrait donc être 3 et 4, soit TITI et T
Voici une réponse à votre requête avec tous les détails :
WITH
T AS (SELECT C.id as cours_id, E.id as eleve_id, date_cours,
code_absence, nom,
ROW_NUMBER() OVER(PARTITION BY E.id
ORDER BY date_cours) AS N1,
ROW_NUMBER() OVER(PARTITION BY E.id, code_absence
ORDER BY date_cours) AS N2
FROM cours AS C
INNER JOIN eleves AS E
ON C.eleve_id = E.id)
SELECT cours_id, eleve_id,nom, date_cours
FROM T
WHERE N2 >= 2 and N1 >= 2 AND code_absence = -1
ORDER BY eleve_id, date_cours
qui donne :
cours_id eleve_id nom date_cours
----------- ----------- ------------- -----------------------
10 3 TITI 2009-01-07 00:00:00.000
11 3 TITI 2009-01-09 00:00:00.000
15 4 TUTU 2009-01-09 00:00:00.000
16 4 TUTU 2009-01-10 00:00:00.000
De manière générale, tous les problèmes d'ordonnancement nécessitent
l'utilisation de fonction d'ordonnancement tel que RANK ou ROW_NUMBER
Si vous ne voulez que les noms, simplifier le SELECT final avec :
DISTINCT nom.
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************
"Michel__D" a écrit dans le message
de news: unl%Bonjour,
olivier a écrit :Bonjour,
Voici un script (ci-dessous) de création d'une base de test.
Dans la table [COURS] le champ [code_absence] vaut 0 quand l'élève est
présent et vaut '-1' quand l'élève est absent.
Ma question est :
Quel sont les élèves qui ont été absent lors des deux derniers cours ?
(je précise bien les deux derniers cours consécutivements et non les
élèves qui ont eu deux absence ! c'est pas pareil...)
Dans mon exemple, c'est uniquement l'élève n°4 : TUTU
En espérant être assez clair dans mes explications et ma question ;-)
Merci
Olivier
-- <SCRIPT>
USE [master]
GO
CREATE DATABASE [testbase] ON PRIMARY
( NAME = N'testbase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAtestbase_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
USE [testbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[id] [int] IDENTITY(1,1) NOT NULL,
[eleve_id] [int] NOT NULL,
[date_cours] [datetime] NOT NULL,
[code_absence] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO eleves (nom) VALUES ('TOTO')
INSERT INTO eleves (nom) VALUES ('TATA')
INSERT INTO eleves (nom) VALUES ('TITI')
INSERT INTO eleves (nom) VALUES ('TUTU')
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'07/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (1,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'09/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (2,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'01/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (3,
'10/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'01/01/2009',0)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'07/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'09/01/2009',-1)
INSERT INTO cours (eleve_id, date_cours, code_absence) VALUES (4,
'10/01/2009',-1)
-- </SCRIPT>
Testé avec sqlite (à adapter) :
SELECT T2.nom
FROM cours AS T1 INNER JOIN eleves AS T2 ON T1.id_eleve = T2.id_eleve
WHERE T1.code_absence = -1 AND T1.date_cours IN (
SELECT DISTINCT date_cours FROM cours ORDER BY date_cours DESC LIMIT 2)
GROUP BY T2.nom
HAVING Count(T2.id_eleve) = 2;