Выборка из нескольких таблиц на Sql Ce
01.10.2012, 08:38. Показов 787. Ответов 0
Здравствуйте. У меня есть N кол-во таблиц, и N кол-во строк в каждой, мне нужно выбрать из 18 таблиц по 23 строки(ID строки именуется X)
Условие выборки строк такое: X>=переменная_X и Х<=переменная_X + 22, где переменная_X это параметр с которого нужно начать читать строчки.
Составил алгоритм, который формировал бы строку запроса:
| VB.NET | 1
2
3
4
5
6
7
8
9
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
41
42
43
| Public Function WorldRead(ByVal x As Integer, ByVal y As Integer) As String
Dim SqlSelect As String = ""
Dim SqlFrom As String = ""
Dim SqlWhere As String = ""
For i = 0 To 17
Dim TName As String = ""
If CInt(y - i) > 0 Then 'префикс имени таблицы
TName = "yPlus" & Math.Abs(y - i)
Else
TName = "yMinus" & Math.Abs(y - i)
End If
SqlSelect &= TName & ".X, " & TName & ".TIP, " & TName & ".NAME, " & TName & ".STATYS, " & TName & ".OBJECKT, " 'столбцы
SqlFrom &= TName & " CROSS JOIN " 'таблицы
SqlWhere &= TName & ".X >=" & x & " AND " & TName & ".X<=" & x + 22 & " AND "
Next
Try
Dim sql As String = "SELECT " & Mid(SqlSelect, 1, SqlSelect.Length - 2) & " FROM " & Mid(SqlFrom, 1, SqlFrom.Length - 12) & " WHERE " & Mid(SqlWhere, 1, SqlWhere.Length - 5) & ";"
msg(sql, 1)
Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)
cmd.CommandType = CommandType.Text
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
If rs.HasRows Then
Dim _X As Integer = rs.GetOrdinal("X")
Dim _TIP As Integer = rs.GetOrdinal("TIP")
Dim _NAME As Integer = rs.GetOrdinal("NAME")
Dim _STATYS As Integer = rs.GetOrdinal("STATYS")
Dim _OBJECKT As Integer = rs.GetOrdinal("OBJECKT")
rs.ReadFirst()
Dim text As String = Mid(CStr(rs.GetInt32(_TIP)) + " ", 1, 16) & Mid(rs.GetString(_NAME) + " ", 1, 16) & Mid(CStr(rs.GetInt32(_STATYS)) + " ", 1, 16) & Mid(CStr(rs.GetInt32(_OBJECKT)) + " ", 1, 16)
Dim p As Integer = 0
Do While rs.Read
p += 1
' msg(p, 1)
text &= Mid(CStr(rs.GetInt32(_TIP)) + " ", 1, 16) & Mid(rs.GetString(_NAME) + " ", 1, 16) & Mid(CStr(rs.GetInt32(_STATYS)) + " ", 1, 16) & Mid(CStr(rs.GetInt32(_OBJECKT)) + " ", 1, 16)
Loop
Return text
End If
' Catch sqlexception As SqlCeException
' MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception
msg(ex.ToString, 1)
End Try
End Function |
|
Строка составляется, ошибок не выдает, но возвращает почему то до безумия много строк, оставлял ПК на ночь счетчик показал больше 500000 строк, после чего база стала превышать допустимый размер и отказала в доступе. Вот пример строки(на псевдонимы столбцов не обращайте внимания, их подставила студия, когда я перекидывал в нее строку), подскажите что делаю не так.
| SQL | 1
2
3
4
5
6
7
8
9
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
| SELECT yPlus7.TIP, yPlus7.NAME, yPlus7.STATYS, yPlus7.OBJECKT, yPlus6.TIP AS [6-1], yPlus6.NAME AS [6-2], yPlus6.STATYS AS [6-3], yPlus6.OBJECKT AS [6-4],
yPlus5.TIP AS Expr7, yPlus5.NAME AS Expr8, yPlus5.STATYS AS Expr9, yPlus5.OBJECKT AS Expr10, yPlus4.TIP AS Expr12, yPlus4.NAME AS Expr13,
yPlus4.STATYS AS Expr14, yPlus4.OBJECKT AS Expr15, yPlus3.TIP AS Expr17, yPlus3.NAME AS Expr18, yPlus3.STATYS AS Expr19, yPlus3.OBJECKT AS Expr20,
yPlus2.TIP AS Expr22, yPlus2.NAME AS Expr23, yPlus2.STATYS AS Expr24, yPlus2.OBJECKT AS Expr25, yPlus1.TIP AS Expr27, yPlus1.NAME AS Expr28,
yPlus1.STATYS AS Expr29, yPlus1.OBJECKT AS Expr30, yMinus0.TIP AS Expr32, yMinus0.NAME AS Expr33, yMinus0.STATYS AS Expr34,
yMinus0.OBJECKT AS Expr35, yMinus1.TIP AS Expr37, yMinus1.NAME AS Expr38, yMinus1.STATYS AS Expr39, yMinus1.OBJECKT AS Expr40,
yMinus2.TIP AS Expr42, yMinus2.NAME AS Expr43, yMinus2.STATYS AS Expr44, yMinus2.OBJECKT AS Expr45, yMinus3.TIP AS Expr47, yMinus3.NAME AS Expr48,
yMinus3.STATYS AS Expr49, yMinus3.OBJECKT AS Expr50, yMinus4.TIP AS Expr52, yMinus4.NAME AS Expr53, yMinus4.STATYS AS Expr54,
yMinus4.OBJECKT AS Expr55, yMinus5.TIP AS Expr57, yMinus5.NAME AS Expr58, yMinus5.STATYS AS Expr59, yMinus5.OBJECKT AS Expr60,
yMinus6.TIP AS Expr62, yMinus6.NAME AS Expr63, yMinus6.STATYS AS Expr64, yMinus6.OBJECKT AS Expr65, yMinus7.TIP AS Expr67, yMinus7.NAME AS Expr68,
yMinus7.STATYS AS Expr69, yMinus7.OBJECKT AS Expr70, yMinus8.TIP AS Expr72, yMinus8.NAME AS Expr73, yMinus8.STATYS AS Expr74,
yMinus8.OBJECKT AS Expr75, yMinus9.TIP AS Expr77, yMinus9.NAME AS Expr78, yMinus9.STATYS AS Expr79, yMinus9.OBJECKT AS Expr80,
yMinus10.TIP AS Expr82, yMinus10.NAME AS Expr83, yMinus10.STATYS AS Expr84, yMinus10.OBJECKT AS Expr85
FROM yPlus2 CROSS JOIN
yPlus5 CROSS JOIN
yPlus4 CROSS JOIN
yPlus3 CROSS JOIN
yPlus7 CROSS JOIN
yPlus6 CROSS JOIN
yPlus1 CROSS JOIN
yMinus0 CROSS JOIN
yMinus1 CROSS JOIN
yMinus2 CROSS JOIN
yMinus3 CROSS JOIN
yMinus4 CROSS JOIN
yMinus5 CROSS JOIN
yMinus6 CROSS JOIN
yMinus7 CROSS JOIN
yMinus8 CROSS JOIN
yMinus9 CROSS JOIN
yMinus10
WHERE (yPlus7.X >= - 12) AND (yPlus7.X <= 10) AND (yPlus6.X >= - 12) AND (yPlus6.X <= 10) AND (yPlus5.X >= - 12) AND (yPlus5.X <= 10) AND (yPlus4.X >= - 12) AND
(yPlus4.X <= 10) AND (yPlus3.X >= - 12) AND (yPlus3.X <= 10) AND (yPlus2.X >= - 12) AND (yPlus2.X <= 10) AND (yPlus1.X >= - 12) AND (yPlus1.X <= 10) AND
(yMinus0.X >= - 12) AND (yMinus0.X <= 10) AND (yMinus1.X >= - 12) AND (yMinus1.X <= 10) AND (yMinus2.X >= - 12) AND (yMinus2.X <= 10) AND (yMinus3.X >= - 12)
AND (yMinus3.X <= 10) AND (yMinus4.X >= - 12) AND (yMinus4.X <= 10) AND (yMinus5.X >= - 12) AND (yMinus5.X <= 10) AND (yMinus6.X >= - 12) AND
(yMinus6.X <= 10) AND (yMinus7.X >= - 12) AND (yMinus7.X <= 10) AND (yMinus8.X >= - 12) AND (yMinus8.X <= 10) AND (yMinus9.X >= - 12) AND (yMinus9.X <= 10)
AND (yMinus10.X >= - 12) AND (yMinus10.X <= 10) |
|
0
|
(
