Difference between revisions of "User:Taweiss"
Jump to navigation
Jump to search
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
Active when I have time. Should be at least weekly. | Active when I have time. Should be at least weekly. | ||
+ | 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 ; |
Revision as of 18:36, 28 March 2020
Active when I have time. Should be at least weekly. 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 ;