Tango.info track duration sql: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
Line 15: | Line 15: | ||
t.tinp,t.side_num,t.track_num | t.tinp,t.side_num,t.track_num | ||
Count tracks having a duration | ==Count== | ||
===Count tracks having a duration=== | |||
SELECT count(*) | SELECT count(*) | ||
FROM ti_track t | FROM ti_track t | ||
Line 22: | Line 23: | ||
t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}' | t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}' | ||
Count tracks having duration and TIWC | ===Count tracks having duration and TIWC=== | ||
SELECT count(*) | SELECT count(*) | ||
FROM ti_track t | FROM ti_track t | ||
Line 31: | Line 32: | ||
AND | AND | ||
t.work_id REGEXP '[TZ]{1}[0-9]{10}' | t.work_id REGEXP '[TZ]{1}[0-9]{10}' | ||
Count tracks having duration, TIWC and date | ===Count tracks having duration, TIWC and date=== | ||
SELECT count(*) | SELECT count(*) | ||
FROM ti_track t | FROM ti_track t | ||
Line 53: | Line 54: | ||
t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' | t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' | ||
Count track that are not 33 or 78 and have no duration | ===Count track that are not 33 or 78 and have no duration=== | ||
SELECT count(*) | SELECT count(*) | ||
FROM ti_track t | FROM ti_track t | ||
Line 62: | Line 63: | ||
AND | AND | ||
t.track_format NOT REGEXP '(33|78)' | t.track_format NOT REGEXP '(33|78)' | ||
Select tinp of products that have track 1 without duration | |||
==Select TINP== | |||
===Select tinp of products that have track 1 without duration=== | |||
SELECT t.tinp | SELECT t.tinp | ||
FROM ti_track t | FROM ti_track t | ||
Line 75: | Line 78: | ||
ORDER by | ORDER by | ||
t.tinp | t.tinp | ||
Select tinp of products that have track 1 without duration but where the track format is not 33 or 78 and the collection is not [[Coleccion Almacen]] | ===Select tinp of products that have track 1 without duration but where the track format is not 33 or 78 and the collection is not [[Coleccion Almacen]]=== | ||
SELECT t.tinp | SELECT t.tinp | ||
FROM ti_track t | FROM ti_track t |
Revision as of 2013-03-11T20:44:54
Combine with track duration table (for data import)
SELECT t.tinp,t.side_num,t.track_num,d.duration FROM ti_track t LEFT JOIN ti_track_duration d ON t.tinp=d.tinp AND t.side_num=d.side AND t.track_num=d.tracknumber WHERE t.tinp RLIKE '^[0-9]{14}$' ORDER BY t.tinp,t.side_num,t.track_num
Count
Count tracks having a duration
SELECT count(*) FROM ti_track t WHERE t.tinp RLIKE '^[0-9]{14}$' AND t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}'
Count tracks having duration and TIWC
SELECT count(*) FROM ti_track t WHERE t.tinp RLIKE '^[0-9]{14}$' AND t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}' AND t.work_id REGEXP '[TZ]{1}[0-9]{10}'
Count tracks having duration, TIWC and date
SELECT count(*) FROM ti_track t WHERE t.tinp REGEXP '^[0-9]{14}$' AND t.track_dura REGEXP '^[0-9]{2}:[0-9]{2}' AND t.work_id REGEXP '[TZ]{1}[0-9]{10}' AND t.track_date REGEXP '(19|20)[0-9]{2}-[0-9]{2}-[0-9]{2}'
Select tracks that have no duration but a discID
SELECT t.tinp, t.side_num, t.track_num , d.cddb_discid FROM ti_track t RIGHT JOIN ti_discid d ON t.tinp = d.tinp AND t.side_num = d.side_num WHERE t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}'
Count track that are not 33 or 78 and have no duration
SELECT count(*) FROM ti_track t WHERE t.tinp REGEXP '^[0-9]{14}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)'
Select TINP
Select tinp of products that have track 1 without duration
SELECT t.tinp FROM ti_track t WHERE t.tinp REGEXP '^[0]{1}[0-9]{13}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)' AND t.track_num =1 ORDER by t.tinp
Select tinp of products that have track 1 without duration but where the track format is not 33 or 78 and the collection is not Coleccion Almacen
SELECT t.tinp FROM ti_track t WHERE t.tinp REGEXP '^[0]{1}[0-9]{13}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)' AND t.track_num =1 AND t.tinp NOT REGEXP '^0248000203' ORDER by t.tinp