SELECT * FROM T1 WHERE T1.id NOT IN (SELECT...) AND T1.id NOT IN (SELECT...)
ID PAYS TYPE MAX INDICE 0 | 2 | Type1 | 5 | 8 0 | 2 | Type1 | 2 | 5
ID PAYS TYPE MAX INDICE 0 | 2 | Type1 | 5 | 8
ID PAYS TYPE Multi INDICE
ID - Clé Primaire PAYS - Clé Étrangère TYPE - NVARCHAR(50) MAX - INT INDICE - INT
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSELECT ..., INDICE, 1 AS Multi, (INDICE * 1) AS Total From MaTable WHERE MAX > 1 UNION SELECT ..., INDICE, 2 AS Multi, (INDICE * 2) AS Total From MaTable WHERE MAX > 2 ... UNION SELECT ..., INDICE, N AS Multi, (INDICE * N) AS Total From MaTable WHERE MAX > N
ID PAYS TYPE MAX INDICE MULTI TOTAL 0 | 2 | Type1 | 5 | 8 | 1 | 8 0 | 2 | Type1 | 2 | 5 | 1 | 5 0 | 2 | Type1 | 5 | 8 | 2 | 16 0 | 2 | Type1 | 2 | 5 | 2 | 10 0 | 2 | Type1 | 5 | 8 | 3 | 24 0 | 2 | Type1 | 5 | 8 | 4 | 32 0 | 2 | Type1 | 5 | 8 | 5 | 40
ID PAYS TYPE MAX INDICE 0 | 2 | Type1 | 5 | 2 1 | 2 | Type2 | 2 | 4
ID PAYS TYPE Multi INDICE 0 | 2 | Type1 | 1 | 2 0 | 2 | Type1 | 2 | 4 0 | 2 | Type1 | 3 | 6 0 | 2 | Type1 | 4 | 8 0 | 2 | Type1 | 5 | 10 1 | 2 | Type2 | 1 | 4 1 | 2 | Type2 | 2 | 8
01/03/2013 20€ 05/03/2013 40€ 22/03/2013 50€
WITH T0 AS (SELECT CAST(@DateDebut AS DATE) AS JOUR), T1 AS (SELECT JOUR FROM T0 AS T0_1 UNION ALL SELECT DATEADD(DAY, 1, JOUR) AS Expr1 FROM T1 AS T1_2 WHERE (DATEADD(DAY, 1, JOUR) <= CAST(@DateFin AS DATE)))
C'est en gros ce que je cherche à faire. Créer une table virtuelle, contenant des enregistrements inventés de toutes pièces mais basés sur une table réelle.
CREATE TABLE [Nombres] ( [Nombre] INT NOT NULL PRIMARY KEY );
INSERT INTO [Nombres]([Nombre]) SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10;
CREATE VIEW AS [TB_Multi] WITH SCHEMABINDING AS SELECT [TB].[ID], [TB].[PAYS], [TB].[TYPE], [Nombres].[Nombre] AS [Multi], [Nombres].[Nombre] * [TB].[INDICE] AS [INDICE] FROM [TB] INNER JOIN [Nombres] ON [Nombres].[Nombre] <= [TB].[MAX]
CREATE VIEW AS [TB_Multi] WITH SCHEMABINDING AS WITH [RECURSION] ( SELECT [TB].[ID], [TB].[PAYS], [TB].[TYPE], [TB].[INDICE], [TB].[MAX] AS [Multi] FROM [TB] UNION ALL SELECT [RECURSION].[ID], [RECURSION].[PAYS], [RECURSION].[TYPE], [RECURSION].[INDICE], [RECURSION].[Multi] - 1 AS [Multi] FROM [RECURSION] WHERE [RECURSION].[Multi] > 1 ) SELECT [RECURSION].[ID], [RECURSION].[PAYS], [RECURSION].[TYPE], [RECURSION].[Multi], [RECURSION].[INDICE] * [RECURSION].[Multi] AS [INDICE] FROM [RECURSION]
SELECT TOP(10) * FROM [TB_Multi] ORDER BY ABS([INDICE]-10)