User:Taweiss
Jump to navigation
Jump to search
Active when I have time. Should be at least weekly. 10th Anniversary! Trying different SQL queries.
Dump my PVs
SELECT
(SELECT GROUP_CONCAT(CONCAT(a.author_lastname, ', ', TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))) SEPARATOR '|') FROM authors a, pub_authors pa WHERE pubs.pub_id = pa.pub_id AND a.author_id = pa.author_id) author_details, pubs.pub_title title, pubs.pub_isbn isbn, pb.publisher_name publisher, CASE WHEN pubs.pub_year = '0000-00-00' THEN NULL WHEN EXTRACT(MONTH FROM pubs.pub_year) = 0 THEN SUBSTRING(pubs.pub_year, 1, 4) WHEN EXTRACT(DAY FROM pubs.pub_year) = 0 THEN SUBSTRING(pubs.pub_year, 1, 7) ELSE pubs.pub_year END AS 'date_published', CASE WHEN ps.pub_series_name IS NOT NULL AND pubs.pub_series_num IS NOT NULL THEN CONCAT(ps.pub_series_name, ' (', pubs.pub_series_num, ')') ELSE ps.pub_series_name END AS 'series_details', pubs.pub_pages pages, CASE WHEN pubs.pub_ctype = 'ANTHOLOGY' THEN '1' ELSE '0' END AS 'anthology', pubs.pub_ptype 'format', ts.series_title, t.title_seriesnum
FROM
primary_verifications v INNER JOIN pubs ON pubs.pub_id = v.pub_id INNER JOIN pub_content pc ON pubs.pub_id = pc.pub_id INNER JOIN titles t ON t.title_id = pc.title_id LEFT OUTER JOIN publishers pb ON pb.publisher_id = pubs.publisher_id LEFT OUTER JOIN pub_series ps ON ps.pub_series_id = pubs.pub_series_id LEFT OUTER JOIN series ts ON ts.series_id = t.series_id
WHERE
pubs.pub_ctype IN ('COLLECTION' , 'ANTHOLOGY', 'MAGAZINE', 'NOVEL', 'CHAPBOOK', 'OMNIBUS', 'NONFICTION') AND ((t.title_ttype = pubs.pub_ctype) OR (t.title_ttype = 'EDITOR' AND pubs.pub_ctype = 'MAGAZINE')) AND v.user_id = 56100
ORDER BY pubs.pub_title INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\book_list.csv' CHARACTER SET UTF8MB4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';
Seeing if I can find the most reprinted short story.
SELECT
(SELECT GROUP_CONCAT(CONCAT(a.author_lastname, ', ', TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))) SEPARATOR '|') FROM authors a, canonical_author pa WHERE t.title_id = pa.title_id AND a.author_id = pa.author_id) author_details, t.title_title, pubs.pub_title, pubs.pub_isbn, COUNT(t.title_title) AS dupe_cnt
FROM
titles t INNER JOIN pub_content pc ON t.title_id = pc.title_id INNER JOIN pubs ON pubs.pub_id = pc.pub_id
WHERE
t.title_title BETWEEN 'A' AND 'z' AND t.title_ttype = 'SHORTFICTION'
GROUP BY author_details , t.title_title HAVING COUNT(t.title_title) > 50 ;