This repository has been archived by the owner on Jan 7, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 9
Nützliche SQL Statements
marians edited this page Apr 9, 2013
·
6 revisions
ACHTUNG: Diese Seite beschreibt Funktionen, wie sie vor Version 2 in Gebrauch waren. In Version 2 kommt keine SQL-Datenbank mehr zum Einsatz.
Dies ist eine Sammlung von SQL-Statements, die man immer wieder mal gebrauchen könnte.
Ermittelt die Anzahl der PDF-Seiten aus den Daten in attachments und attachment_thumbnails.
SELECT SUM(t1.numpages) FROM (
SELECT (MAX(page)+1) AS numpages
FROM attachments a
LEFT JOIN attachment_thumbnails t ON a.attachment_id=t.attachment_id
WHERE attachment_mimetype="application/pdf" and height=300
GROUP BY a.attachment_id
) t1
Gibt alle Vorlagentexte mit den Gremien, aus denen die Vorlagen stammen, aus. Kann z.B. genutzt werden, um Bayes-Klassifizierung zu trainieren.
SELECT
attachment_content, committees.committee_id, committee_title
FROM attachments
LEFT JOIN submissions2attachments ON submissions2attachments.attachment_id=attachments.attachment_id
LEFT JOIN submissions ON submissions2attachments.submission_id=submissions.submission_id
LEFT JOIN agendaitems2submissions ON agendaitems2submissions.submission_id=submissions.submission_id
LEFT JOIN agendaitems ON agendaitems2submissions.agendaitem_id=agendaitems.agendaitem_id
LEFT JOIN sessions ON agendaitems.session_id=sessions.session_id
LEFT JOIN committees ON sessions.committee_id=committees.committee_id
WHERE attachment_content IS NOT NULL AND attachment_content != ''
AND committee_title LIKE "Bezirksvertretung%"
ORDER BY committees.committee_id
SELECT
attachment_content, committees.committee_id, committee_title
FROM attachments
LEFT JOIN requests2attachments ON requests2attachments.attachment_id=attachments.attachment_id
LEFT JOIN requests ON requests2attachments.request_id=requests.request_id
LEFT JOIN agendaitems2requests ON agendaitems2requests.request_id=requests.request_id
LEFT JOIN agendaitems ON agendaitems2requests.agendaitem_id=agendaitems.agendaitem_id
LEFT JOIN sessions ON agendaitems.session_id=sessions.session_id
LEFT JOIN committees ON sessions.committee_id=committees.committee_id
WHERE attachment_content IS NOT NULL AND attachment_content != ''
AND committee_title LIKE "Bezirksvertretung%"
ORDER BY committees.committee_id
LIMIT 10000
SELECT
request_id, submission_id, attachment_exclusions.attachment_id, attachment_mimetype,
attachment_filename, excluded_since, reason_code, reason_text
FROM attachment_exclusions
LEFT JOIN attachments ON attachment_exclusions.attachment_id=attachments.attachment_id
LEFT JOIN requests2attachments ON requests2attachments.attachment_id=attachment_exclusions.attachment_id
LEFT JOIN submissions2attachments ON submissions2attachments.attachment_id=attachment_exclusions.attachment_id
ORDER BY excluded_since