남은블러 뽑기

select a.pvrid
   SELE(select pvrid from view_nas1234 LIMIT 3000) as a
  LEFT OUTER JOIN blur_process as b
  ON a.pvrid = b.pvrid WHERE b.touch IS NULL

select a.pvrid
  FROM 
  (SELECT pvrid FROM pvr 
where hidden = 0) as a
  LEFT OUTER JOIN blur_process as b
  ON a.pvrid = b.pvrid WHERE b.touch IS NULL

select a.pvrid
  FROM 
  (SELECT pvrid FROM pvr 
where hidden = 0) as a
  LEFT OUTER JOIN blur_process as b
  ON a.pvrid = b.pvrid WHERE b.touch IS NULL

INSERT INTO blur_residue (pvrid)
VALUES ( 1201101018815) ,
(1201101014137),
(1201101001047),
(1201101026895),
...

[SQL] INSERT 데이터 삽입

PLAYER 테이블에 위와 같은 값들을 데이터로 입력한다. 해당 컬럼의 데이터 유형이 CHAR 이거나 VARCHAR2 등 문자 유형일 경우 ' ' 로 입력할 값을 입력한다. 숫자일 경우 숫자만 입력한다. 첫번째 방식은 입력할 데이터의 컬럼명과 입력되어야 하는 값을 1:1로 매핑하여 입력하면 된다. 테이블 명 뒤에 () 안에 입력할 테이블의 컬럼을 정의하며, 컬럼의 순서는 테이블의 실제 컬럼 순서와 매치할 필요는 없다.

https://hyeonstorage.tistory.com/294

다뽑아서 where 만들었음

 

select a.pvrid
  FROM 
  (SELECT pvrid FROM blur_residue) as a
  LEFT OUTER JOIN blur_process as b
  ON a.pvrid = b.pvrid WHERE b.touch IS NULL order by rand()

최종

(select a.pvrid
  FROM 
  (SELECT pvrid FROM blur_residue) as a
  LEFT OUTER JOIN blur_process as b
  ON a.pvrid = b.pvrid WHERE b.touch IS NULL order by RAND())
    union
  (SELECT pvr.pvrid FROM pvr RIGHT join
  (SELECT pvrid FROM
  (SELECT *, COUNT(*), AVG(touch) AS t from blur_process AS bp
  group by pvrid having COUNT(*) > 0 ORDER BY RAND())
  AS a WHERE t = 30) AS bp
  ON bp.pvrid = pvr.pvrid
  WHERE pvr.hidden = 0)

(SELECT job.* FROM job right join
(SELECT pvrid from
(SELECT *, COUNT(*), AVG(touch) as t from
(SELECT bp.*, g.content from blur_process AS bp left join
(SELECT pvrid, content from
(SELECT a.pvrid, a.content from report AS a
JOIN job as b ON a.pvrid = b.pvrid
WHERE a.job = 300 and a.state = 20)
AS a
) AS g
ON bp.pvrid = g.pvrid
WHERE g.pvrid IS not NULL
ORDER BY bp.pvrid) AS final
group BY pvrid HAVING COUNT(*) = 1) AS fi
WHERE t = 20) AS object
ON job.pvrid=object.pvrid
WHERE object.pvrid IS not NULL
AND state = 40)