1. Этот сайт использует файлы cookie. Продолжая пользоваться данным сайтом, Вы соглашаетесь на использование нами Ваших файлов cookie. Узнать больше.

Знатоки Sql

Тема в разделе "Программирование", создана пользователем DirectX, 27.06.04.

  1. DirectX

    DirectX Активный участник

    1.873
    0
    Выбросив лишние подробности имеется следующая база данный на Access'e:

    1. Таблица Authors:
    поля ID, Name (ID-ключевое)

    2. Таблица Books:
    поля ID, Caption (ID-ключевое)

    и, наконец, промежуточная таблица, обеспечивающая связь "многие-ко-многим"

    3. Таблица BooksAuthors:
    поля Book_ID, Author_ID

    в последней таблице первичным ключом является уникальная комбинация двух полей.

    Теперь, допустим, в базу занесены следующие данные:

    Authors:

    1 Ильф И.
    2 Петров Е.
    3 Момзен Т.
    ...

    Books:

    1 12 стульев
    2 Золотой телёнок
    3 История Рима
    ...

    BooksAuthors:
    1 1
    1 2
    2 1
    2 2
    3 3

    А вот теперь вопрос: как создать запрос, выдающий:

    12 Стульев Ильф И., Петров Е.
    Золотой телёнок Ильф И., Петров Е.
    История Рима Момзен Т.

    ?

    Создать запрос, выдающий

    12 Стульев Ильф И.
    12 Стульев Петров Е.
    Золотой телёнок Ильф И.
    Золотой телёнок Петров Е.
    История Рима Момзен Т.

    легко.

    Если бы, к примеру, число авторов было бы ограничено, например, таблица Books имела бы вид: ID, Caption, Author1, Author2, то вопрос решался бы просто:

    SELECT Caption, [Author1] & ", " & [Author2] FROM Books

    Здесь подразумевается непосредственное хранение фамилий в таблице, что не есть гуд, но суть понятна: операция объединения строк склеивает строки из разных полей. Так как можно добиться подобного эффекта с использованием промежуточных таблиц для неограниченного числа полей. Желательно с использованием SQL без Visual Basic'a?
     
  2. pegas

    pegas Участник

    311
    0
    то что тебе нужно называется Cross-Tab query (перекрестный запрос), в SQL нет встроенных возможностей для ее получения, но именно в Access можно сделать так

    создаем обьединяющий запрос query1-SELECT books.caption, authors.name, autors.id
    FROM books INNER JOIN (authors
    INNER JOIN booksauthors ON authors.id = booksauthors.id_authors) ON books.id = booksauthors.id_books
    и тогда перекрестный запрос будет например

    TRANSFORM First([authors].[name]) AS Выражение1
    SELECT books.caption
    FROM query1
    GROUP BY books.caption
    PIVOT id

    в данном случае названиями колонок будут id автора.
    P.S.
    а, тебе кажется имена авторов нужны в одной колонке?. тогда только через хранимымые процедуры, но если база в аксессе то не прокатит . или временную таблицу, но VB придется использовать.
     
  3. Vaulter

    Vaulter Активный участник

    1.622
    0
    попробуй
    Код:
    SELECT DISTINCT Book_ID,Books.Caption,Author.Name FROM BooksAuthors 
    LEFT JOIN Books ON Books.ID=Books_ID
    LEFT JOIN Authors ON Authors.ID=Author_ID
    
     
  4. jek

    jek Активный участник

    5.733
    0
    Нужно использовать курсор, но в аксессе его нет.
    Vaulter Ты бы почитал что человеку нужно для начала.
     
  5. DirectX

    DirectX Активный участник

    1.873
    0
    В общем, проблему решил следующим образом:

    1. Создал запрос вроде этого:

    SELECT Books.Caption AS Caption, Authors.Name AS Name FROM Authors, Books, BooksAuthors INNER JOIN (Books INNER JOIN BooksAuthors ON Author.ID = BooksAuthors.Author_ID) WHERE BooksAuthors.Book_ID = Books.ID ORDER BY Books.Caption

    и обозвал его "BooksByAuthors". В нём названия книг продублированы по количеству авторов на каждую.

    2. После этого написал на VB макрос:

    Private Sub btnCreateQuery_Click()

    Dim strQueryName As String
    Dim strTableName As String
    strQueryName = "BooksByAuthors" ' Запрос с продублированными названиями книг
    strTableName = "BooksByAuthors_Autogenerated"

    Dim db As DAO.Database
    Dim qd As QueryDef
    Dim qdLoop As QueryDef
    Dim td As TableDef
    Dim tdLoop As TableDef
    Dim src_rs As Recordset
    Dim dst_rs As Recordset

    Set db = CurrentDb()

    For Each tdLoop In db.TableDefs
    If tdLoop.Name = strTableName Then
    db.TableDefs.Delete strTableName
    End If
    Next tdLoop

    Set qd = db.QueryDefs(strQueryName)

    Set td = db.CreateTableDef(strTableName)
    With td
    .Fields.Append .CreateField("Caption", dbText, 255)
    .Fields.Append .CreateField("Authors", dbMemo)
    End With
    db.TableDefs.Append td

    Set src_rs = qd.OpenRecordset
    Set dst_rs = td.OpenRecordset

    Dim i As Integer
    Dim CurrSectionID As Integer
    Dim strCurrSection As String
    Dim CurrReportID As Integer
    Dim strCurrCaption As String
    Dim strAuthors As String

    src_rs.MoveFirst

    strCurrCaption = src_rs("Caption")
    strAuthors = src_rs("Name")

    For i = 1 To src_rs.RecordCount - 1
    src_rs.MoveNext

    If src_rs("Caption") <> strCurrCaption Then ' Проверка уникальности имени книги
    dst_rs.AddNew
    dst_rs("Caption") = strCurrCaption
    dst_rs("Authors") = strAuthors
    dst_rs.Update

    strCurrCaption = src_rs("Caption")
    strAuthors = src_rs("Name")
    Else
    strAuthors = strAuthors & ", " & src_rs("Name") ' Объединение имён авторов
    End If
    Next i

    End Sub
     
  6. luka

    luka Активный участник

    704
    0
    DirectX, а distinct поставить не получится?
     
  7. Гость

    Гость Гость

    DISTINCT nelzja - poprobuj GROUP BY