User:Taweiss
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Active when I have time. Should be at least weekly. 10th Anniversary!
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 ;