- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
MainDM->ADOQ_pr_kontr_vert->SQL->Add(
"SELECT Smena, Prostenok, Popravka, Val_zad_temp, t.Zam1, t.Zam2, t.Zam3, t.Zam4, "
"@sr1:=((t.Zam1 + t.Zam2 + t.Zam3 + t.Zam4) / 4) as sredn, "
"@p:= @sr1 - (avgtempr.avgZam1 + avgtempr.avgZam2 + avgtempr.avgZam3 + avgtempr.avgZam4) / 4 as popravka from "
"(SELECT avg(Zam1) as avgZam1, avg(Zam2) as avgZam2, avg(Zam3) as avgZam3, avg(Zam4) as avgZam4 from "
"(SELECT "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 1 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam1, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 2 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam2, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 3 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam3, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 4 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam4 "
"FROM kontroln_vertik WHERE kontroln_vertik.Date = \'2011-03-02\' "
"and kontroln_vertik.Storona = 1 "
"group by kontroln_vertik.Prostenok) zameri) avgtempr, "
"(SELECT kontroln_vertik.Smena, kontroln_vertik.Prostenok, kontroln_vertik.Popravka, "
"kontroln_vertik.Val_zad_temp, kontroln_vertik.Period_koks, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 1 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam1, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 2 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam2, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 3 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam3, "
"SUM(CASE "
"WHEN kontroln_vertik.Zamer = 4 THEN kontroln_vertik.Value ELSE 0 "
"END) as Zam4 "
"FROM kontroln_vertik "
"WHERE kontroln_vertik.Date = \'2011-03-02\' and "
"kontroln_vertik.Storona = 1 "
"group by kontroln_vertik.Prostenok) t"
);
absolut 04.03.2011 00:52 # +5
koodeer 04.03.2011 07:24 # +5
Я вот придумал: LINQ - это SQL#