Share with you Sarah Brightman Eden

Sarah Brightman - Eden

MS SQL 列出Table所有欄位

--列出Table所有欄位
SELECT     *
FROM         INFORMATION_SCHEMA.COLUMNS
WHERE     (TABLE_NAME = '[TableName]')

比對並Update的SQL - MS SQL version

系統上線一陣子後因為資料日漸增多，我們會把資料切成online, offline的部分，有時會想將online更新到offline，這時我們把兩個Table的資料做比對，找出相同的資料後，更新offline。

使用下面的SQL可以更新offline資料。使用cursor。

DECLARE c Cursor FOR
  select pd.tankid, pd.bodid, pd.sampletstamp
  from newbod.dbo.tblQCThickMeasurements bod, ProcessData.dbo.tblQCThickMeasurements pd
  where bod.tankid = pd.tankid
  and bod.bodid = pd.bodid
  and bod.sampletstamp = pd.sampletstamp
  and pd.qualcopied = 0;
Open c;
IF (@@CURSOR_ROWS<>0) ---判斷cursor開啟狀況
 BEGIN  
  DECLARE @tankId VARCHAR(50), @BodId VARCHAR(50), @sampleTS Datetime;  
  Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;

  While (@@FETCH_STATUS <> -1)--當陳述式失敗，或資料列超出結果集時停止迴圈
   BEGIN
    update ProcessData.dbo.tblQCThickMeasurements set
      qualcopied=1 
    where tankid = @tankId
    and bodid = @BodId
    and sampletstamp = @sampleTS;
    Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;
   END;
 END;
CLOSE c;
DEALLOCATE c;
GO

比對並Update的SQL - Oracle version

系統上線一陣子後因為資料日漸增多，我們會把資料切成online, offline的部分，有時會想將online更新到offline，這時我們把兩個Table的資料做比對，找出相同的資料後，更新offline。

使用下面的SQL可以更新offline資料。

update 
    (select ol.tank_id, ol.bod_id, ol.sampletstamp, ol.qualcopied
    from hisadm.qc_cord_summ su, hisadm.qc_cord_summ_ol ol
    where su.tank_id = ol.tank_id
    and su.bod_id = ol.bod_id
    and su.sampletstamp = ol.sampletstamp
    and ol.qualcopied = 'F'
    and ol.sampletstamp <> to_date('2009/7/26 11:32:00','yyyy/MM/dd HH24:MI:SS')
    and ol.sampletstamp <> to_date('2009/12/8 19:17:00','yyyy/MM/dd HH24:MI:SS')) a set 
a.qualcopied = 'T';

當條件上出現重覆資料時，Oracle就不允許update了，需要自已將重覆測試的資料過濾，自行update不重覆的資料，會使用到cursor如同下方SQL

declare cursor c is select ol.tank_id, ol.bod_id, ol.sampletstamp, ol.qualcopied
                    from hisadm.qc_cord_summ su, hisadm.qc_cord_summ_ol ol
                    where su.tank_id = ol.tank_id
                    and su.bod_id = ol.bod_id
                    and su.sampletstamp = ol.sampletstamp
                    and ol.qualcopied = 'F'
                    and ol.sampletstamp <> to_date('2009/7/26 11:32:00','yyyy/MM/dd HH24:MI:SS')
                    and ol.sampletstamp <> to_date('2009/12/8 19:17:00','yyyy/MM/dd HH24:MI:SS');
begin
  for r_c in c loop  
      update hisadm.qc_cord_summ_ol a set
      a.qualcopied = 'T'
      where a.tank_id = r_c.tank_id
      and a.bod_id = r_c.bod_id
      and a.sampletstamp = r_c.sampletstamp;
  end loop;
end;

MS SQL 2000以下版本如何做出row_num的效果?

MS SQL 2005含以上的版本有個 row_number() 可以讓使用者容易的做查詢出的資料加上序號，可是MS SQL 2000就不支援了。
由於這樣的需求簡單寫了下方的code，可以做出序號效果，前題是id欄位是唯一。

select (select count(1) from table1 c
  where c.id <= table1.id) as row_number, 
  table1.*     
from table1

Oracle 找出loss的數字序列

今日遇到個問題。
在table: pt_dltgls裡有location 1 ~ 500(符合條件的max location)，其中有些location在輸入時漏掉了。所以要找出這裡漏掉的location，讓使用者來補回。

SQL做法:
1.Oracle語法列出1 ~ max location。
2.再minus現有的location，剩下的就是漏掉的location啦!~


Oracle SQL

Select level location
from dual 
connect by level <= (select nvl(max(location), 0) from pt_dltgls where dp_lot_id='C9J04031' and gls_status='S')
minus 
select distinct location from pt_dltgls t where dp_lot_id='C9J04031' and gls_status='S'

ps:感謝同仁Aming大力幫忙，沒有Aming還真寫不出來呢~哈~

Wonder girls sing nobody in NY