[ad_1]
Bonjour les amis, je voulais poser une question sur une instruction SELECT dans SQLite3. J’ai 4 tableaux structurés comme indiqué dans le schéma,
|-------------------------| | TabAnaOra1 | |-------------------------| | Var1 | Var2 | Name | |-------------------------| | 0.23 | 25.00| francesco | | 1.25 | 11.02| jhon | | 10 | 0.002| julia | | ...... |-------------------------| | TabAnaOra2 | |-------------------------| | Var1 | Var2 | Name | |-------------------------| | 0.77 | 35.00| francesco | | 0.25 | 41.02| jhon | | 70 | 8.002| julia | | ...... | TabAnaOra3 | |-------------------------| | Var1 | Var2 | Name | |-------------------------| | 0.03 | 28.00| rudolf | | 9.25 | 15.02| jhon | | 100 | 0.102| julia | | ...... |-------------------------| | TabAnaOra4 | |-------------------------| | Var1 | Var2 | Name | |-------------------------| | 0.13 | 2.00 | francesco | | 125 | 1.02| jhon | | 10.22| 1.002| maria | | ......
et je voudrais remplir un cinquième tableau avec les valeurs les plus basses et les plus élevées, comme décrit dans le diagramme.
|----------------------------------------------------------| | DataMix | |----------------------------------------------------------| | Name | Var1Min | Var1Max | Tab1 | Tab2 | |----------------------------------------------------------| | francesco | 0.13 | 0.77 | TabAnaOra3 | TabAnaOra2 | | jhon | 0.25 | 125 | TabAnaOra2 | TabAnaOra4 | | julia | 10 | 100 | TabAnaOra1 | TabAnaOra3 | | ......
J’ai essayé de créer un script, mais il ne fonctionne que pour les deux premières tables et uniquement pour les 12 premiers enregistrements. Tout le monde peut vérifier si mon appel de sélection est correct ?
Ce que j’ai essayé :
Frapper
#!/bin/bash DB_PATH="ConfigDB.db" TABLE1="TabAnaOra1" TABLE2="TabAnaOra2" TABLE3="TabAnaOra3" TABLE4="TabAnaOra4" MIX_TABLE="DataMix" INSERT_MIX_TABLE="INSERT INTO $MIX_TABLE SELECT NULL, t1.Name, MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) AS Var11, MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) AS Var12, CASE WHEN t1. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE1' WHEN t2. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE2' WHEN t3. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE3' WHEN t4. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE4' END AS Tab1, CASE WHEN t1. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE1' WHEN t2. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE2' WHEN t3. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE3' WHEN t4. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE4' END AS Tab2 FROM $TABLE1 AS t1 JOIN $TABLE2 AS t2 ON t1.Name = t2.Name JOIN $TABLE3 AS t3 ON t1.Name = t3.Name JOIN $TABLE4 AS t4 ON t1.Name = t4.Name GROUP BY t1.Name;" # Esecuzione della query SQL sqlite3 "$DB_PATH" "$INSERT_MIX_TABLE"
Solution 1
Vous pouvez utiliser l’approche ci-dessous pour obtenir le résultat souhaité.
SQL
DECLARE @TabAnaOra1 TABLE ( Var1 decimal(6, 3), Var2 decimal(6, 3), Name varchar(20) ); DECLARE @TabAnaOra2 TABLE ( Var1 decimal(6, 3), Var2 decimal(6, 3), Name varchar(20) ); DECLARE @TabAnaOra3 TABLE ( Var1 decimal(6, 3), Var2 decimal(6, 3), Name varchar(20) ); DECLARE @TabAnaOra4 TABLE ( Var1 decimal(6, 3), Var2 decimal(6, 3), Name varchar(20) ); DECLARE @TmpTable TABLE ( Var1 decimal(6, 3), Var2 decimal(6, 3), Name varchar(20), TableName varchar(50) ); INSERT INTO @TabAnaOra1 SELECT 0.23, 25.00, 'francesco' UNION ALL SELECT 1.23, 11.02, 'jhon' UNION ALL SELECT 10, 0.002, 'julia'; INSERT INTO @TabAnaOra2 SELECT 0.770, 35.00, 'francesco' UNION ALL SELECT 0.25, 41.02, 'jhon' UNION ALL SELECT 70, 8.002, 'julia'; INSERT INTO @TabAnaOra3 SELECT 0.03, 28.00, 'rudolf' UNION ALL SELECT 9.25, 15.02, 'jhon' UNION ALL SELECT 100, 0.102, 'julia'; INSERT INTO @TabAnaOra4 SELECT 0.13, 2.00, 'francesco' UNION ALL SELECT 125, 1.02, 'jhon' UNION ALL SELECT 10.22, 1.002, 'maria'; INSERT INTO @TmpTable (Var1, Var2, Name, TableName) select Var1, Var2, Name, 'TabAnaOra1' As TableName FROM @TabAnaOra1 T1 UNION ALL select Var1, Var2, Name, 'TabAnaOra2' As TableName FROM @TabAnaOra2 T2 UNION ALL select Var1, Var2, Name, 'TabAnaOra3' As TableName FROM @TabAnaOra3 T3 UNION ALL select Var1, Var2, Name, 'TabAnaOra4' As TableName FROM @TabAnaOra4 T4 SELECT T.*, T1.TableName, T2.TableName FROM ( SELECT Name, min(var1) as MinVar1, max(var1) as MaxVar1 FROM @TmpTable T GROUP BY Name ) T LEFT OUTER JOIN @TmpTable T1 ON T.Name = T1.Name AND T.MinVar1 = T1.Var1 LEFT OUTER JOIN @TmpTable T2 ON T.Name = T2.Name AND T.MaxVar1 = T2.Var1
Bien que ce soit un peu délicat et nécessite des modifications dans votre script bash, je suis convaincu que vous pouvez le faire.
[ad_2]
コメント