Tango.info track duration sql: Difference between revisions
Jump to navigation
Jump to search
(2 intermediate revisions by the same user not shown) | |||
Line 94: | Line 94: | ||
ORDER by | ORDER by | ||
t.tinp | t.tinp | ||
SELECT t.tinp, p.product_name | |||
FROM ti_track t, ti_product p | |||
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' | |||
AND t.tinp =p.tinp | |||
ORDER by | |||
t.tinp | |||
SELECT t.tinp, p.product_name, p.product_artist, p.product_collection | |||
FROM ti_track t, ti_product p | |||
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' | |||
AND t.tinp = p.tinp | |||
ORDER BY t.tinp | |||
LIMIT 0 , 200 |
Latest revision as of 2013-06-13T02:31:31
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
SELECT t.tinp, p.product_name FROM ti_track t, ti_product p 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' AND t.tinp =p.tinp ORDER by t.tinp
SELECT t.tinp, p.product_name, p.product_artist, p.product_collection FROM ti_track t, ti_product p 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' AND t.tinp = p.tinp ORDER BY t.tinp LIMIT 0 , 200