SQL запрос

[uniqueman]

мой запрос выдаст по одной записи для всех записей из T1 INNER JOIN T2 ON T1.id=T2.id

Я хотел сказать, по одной записи для каждого id из T1 INNER JOIN T2 ON T1.id=T2.id

На результат эти нюансы формулировки не влияют: запрос делает то, что надо. Если я ошибаюсь, пожалуйста, контрпример данных на к-рых запрос вернет не то, что надо.

Вы абсолютно правы. Я просто подумал что символ T в предикате означает Table 1

[hb]

На результат эти нюансы формулировки не влияют: запрос делает то, что надо. Если я ошибаюсь, пожалуйста, контрпример данных на к-рых запрос вернет не то, что надо.

Вы абсолютно правы. Я просто подумал что символ T в предикате означает Table 1

Символ T означал "обзовем таблицу T2 в подзапросе как-нибудь, скажем, T, чтобы не путать с T2 из основного запроса". Я думал, что это делает запрос нагляднее. Но если наоборот, то давайте T просто на фиг уберем :

Code: Select all

SELECT T2.id, T2.Sum
FROM T1, T2
WHERE T1.id=T2.id AND
      T2.Time=(SELECT MAX(Time) FROM T2 WHERE T2.id=T1.id)

Результат тот же самый.

[tengiz]

vc, it's different. You're referring to a subquery as a table expression, which is perfectly valid. What I meant is the following:

select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1

Provided that the subquery returns one column rowset with no more that one row, this is valid for SQL Server; however, is not standard complaint. Here is the equivalent ANSI SQL query:

select tab1.pk, tab1.a, tab1.b, tab2.c from tab1 left join tab2 on tab1.pk = tab2.pk

[vc]

tengiz,

vc, it's different. You're referring to a subquery as a table expression, which is perfectly valid. What I meant is the following:

select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1

Provided that the subquery returns one column rowset with no more that one row, this is valid for SQL Server; however, is not standard complaint. .........

I beg to differ (not being obnoxious, just willing to clarify ;) ):

From the '92 standard:

"Syntax Rules

1) The degree of a <scalar subquery> shall be 1
..................
"
and

"General Rules

1) If the cardinality of a <scalar subquery> or a <row subquery> is
greater than 1, then an exception condition is raised: cardinal-
ity violation.
"
and elsewhere in the standard:

"X3H2-92-154/DBL CBR-002
6.11 <value expression>


General Rules

.............................
2) If a <value expression primary> is a <scalar subquery> and the
result of the <subquery> is empty, then the result of the <value
expression primary> is the null value.

.........................
Leveling Rules

1) The following restrictions apply for Intermediate SQL:

None.

2) The following restrictions apply for Entry SQL in addition to
any Intermediate SQL restrictions:

........................................
e) A <value expression primary> shall not be a <scalar subquery>
except when the <value expression primary> is simply con-
tained in a <value expression> that is simply contained in
the second <row value constructor> of a <comparison predi-
cate>.
"
To sum up, a scalar subquery can be used anywhere a <value expression> is used, in particular in the select list.

Therefore, if the subquery satisfies the above rules, your query (select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1 )
is ANSI compatible ('92 intermediate) because (select c from tab2 where tab2.pk = tab1.pk) is a <value expression>.

Rgds.

[tengiz]

vc, I've just looked into the SQL99 final doc - you're right. They've relaxed the restriction I was referring to. Anyway, I should have been more careful - it was the older stadard (a namely - SQL89) that didn't allow correlated subqueries in a select list. Thanks a lot!

[vc]

tengiz,

vc, I've just looked into the SQL99 final doc - you're right. They've relaxed the restriction I was referring to. Anyway, I should have been more careful - it was the older stadard (a namely - SQL89) that didn't allow correlated subqueries in a select list. Thanks a lot!

Thank you -- it's always a pleasure ...

Rgds.

[Dedal]

Вот пара вариантов:

1) Со связанным подзапросом. Такой, в общем-то, уже был:

Code: Select all

select N.ID, N.NAME, V2.SUM, V2.TIME from TABLE1 N, TABLE2 V2
where 
  N.ID=V2.ID
  and
  V2.TIME = (SELECT MAX(V1.TIME) from TABLE2 V1 where V1.ID = V2.ID);

2) С группировкой и агрегатной функцией:

Code: Select all

select N.ID, N.NAME, V2.SUM, MAX(V2.TIME) from TABLE1 N, TABLE2 V2, TABLE2 V1
where N.ID = V2.ID AND V2.ID = V1.ID
group by N.ID, N.NAME, V2.SUM
having MAX(V2.TIME) = MAX(V1.TIME);