Blueimp 論壇首頁
  首頁  | 討論區  | 最新話題  | 搜尋  | XML  |  登入

Blueimp »  列出所有討論區 »  討論區: SQL
此話題中所有文章數: 4 [ 話題狀態: 一般 / 話題型態: Sticky 話題 ]
上一話題 此文章已經觀看 21994 次 而且有 3 篇回應 下一話題
會員大頭照
 男寶寶 jieh 《騎士團團長》
文章: 6884
v3.8.8

[精華] SQL 語法動態組合 Script

今天實在對本公司為了讓 User 直接強姦資料庫而用 Excel 白痴法開的 Table
造成本人懶得寫程式 而接手過來貼得拉拉長的 SQL 很不爽
直接上討論區只能用 "欄位" 兩個字當關鍵字 (不得以的 其他太直接的關鍵字找不到文章)
爬文爬了一個小時眼睛有點扭傷 終於找到答案

先提供一個簡單的範例讓大家吸收 - 動態 Table 名稱
 
declare @vSQL nvarchar(100), @TableName varchar(100)

set @TableName = 'ICMS_DELAY_APPLY'

set @vSQL = 'select top 10 * from ' + @TableName

execute sp_executesql @vSQL

(轉貼時請加註來源)
引用自:藍色小惡魔《用 SQL 語法動態組合 SQL》
http://www.imp.idv.tw/play/forum/viewthread?thread=743

重點說明
sp_executesql：這是 MS SQL 內部平常用來跑你寫死的 SQL 語法 SP，把它偷出來用就是主角啦!!!

要丟給 sp_executesql 的變數一定要是 nchar / nvarchar 的型態喔!!!
除了以上兩個重點 其他就隨便你高興亂改成你想要的即可

以此類推動態欄位就搞定啦!!! 看懂這個語法後 就可以接下去看我最終的寫法了...

good luck

2016/7/27:6817

xuite, pixnet

2016/7/27:240
2016/7/27:123

----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣，越多人知道越有價值；知識最好的備份方法，散播！
藍色小惡魔(林永傑)： 臉書
----------------------------------------
[編輯文章 17 次, 最後修改： jieh 於 2016/12/1 下午 05:48:02]
[2007/3/20 下午 05:49:24]   [返迴此篇文章頂端 ]  回到頂端 
會員大頭照
 男寶寶 jieh 《騎士團團長》
文章: 6884
v3.8.8

動態欄位名稱更新 SQL

 
declare @vSQL nvarchar(500), @ColumnName varchar(50), @RunDate datetime, @DateStr char(8)

set @RunDate = '2007-12-25'

while @RunDate > '2006-02-01' begin

set @DateStr = convert(char(8), @RunDate, 112)
print @DateStr + ' 處理中......'

set @vSQL = 'update V2_繳款記錄 set last_in_date = ''' + @DateStr + ''''
+ ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100)
+ ' = ''Y'' and last_in_date is null'

print @vSQL

execute sp_executesql @vSQL

set @RunDate = dateadd(month, -1, @RunDate)
continue end

轉貼時請加註來源引用自：藍色小惡魔《用 SQL 語法動態組合 SQL》
http://www.imp.idv.tw/play/forum/viewthread?thread=743

就這樣... 原本要重貼 23 次的 SQL 就這樣取代掉了
SQL 的經典範例都很簡短 大家都是高手 自己玩自己摸應該沒問題 我就不廢話了 tongue

good luck

xuite, pixnet

2016/7/27:120
2016/7/27:328
----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣，越多人知道越有價值；知識最好的備份方法，散播！
藍色小惡魔(林永傑)： 臉書
----------------------------------------
[編輯文章 15 次, 最後修改： jieh 於 2017/1/11 下午 04:18:40]
[2007/3/20 下午 07:11:19]   [返迴此篇文章頂端 ]  回到頂端 
會員大頭照
 男寶寶 jieh 《騎士團團長》
文章: 6884
v3.8.8

動態欄位抄寫 Table

 
declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(500), @ColumnName varchar(50), @RunDate datetime

-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式)
select @RunDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '01') + 19110000)
from sysobjects obj join syscolumns col on obj.id = col.id
where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'

set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE'

while @RunDate >= '2006-02-01' begin

set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate))
set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName
set @RunDate = dateadd(month, -1, @RunDate)

continue end

set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2'
+ ' join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN'

execute sp_executesql @vSQL1

(轉貼時請加註來源)
引用自:藍色小惡魔《用 SQL 語法動態組合 SQL》
http://www.imp.idv.tw/play/forum/viewthread?thread=743

好啦... 吸收完前兩個簡單的範例後 再來個難一點的
主要是稍加改變一下動態欄位的組合方式
另外考慮這種呆呆欄位 可能無限制開下去 加入永續執行的概念

當然 欄位範圍的撈取需要再加強其他條件 避免撈到錯誤的欄位進行比對 才能一勞永逸
主要是因為已經存在的欄位規則是如此了
如果各位以後有計畫要這樣玩 SQL 那欄位名稱就要開奇怪點
避免後續維護的人 開出影響判斷的欄位 畢竟 "INCOME" 是很通俗的單字
其他加強的部份 大家自己慢慢玩喔......

good luck

xuite, pixnet

2016/7/27:465
2016/7/27:51

----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣，越多人知道越有價值；知識最好的備份方法，散播！
藍色小惡魔(林永傑)： 臉書
----------------------------------------
[編輯文章 7 次, 最後修改： jieh 於 2017/1/11 下午 04:21:40]
[2007/3/21 上午 10:59:31]   [返迴此篇文章頂端 ]  回到頂端 
會員大頭照
 男寶寶 jieh 《騎士團團長》
文章: 6884
v3.8.8

SQL 動態語法完整範例 - 撐著用繳款資訊更新 Ver 1.1

 
drop table ICMS30Bak.dbo.JobBak_V2_繳款記錄
select * into ICMS30Bak.dbo.JobBak_V2_繳款記錄 from confer.dbo.V2_繳款記錄

-- 從債協專用更新最新繳款記錄 - vSQL1

insert into confer.dbo.V2_繳款記錄 (cust_idn)
select distinct cust_idn
from confer.dbo.V2_繳款記錄_債協專用 where cust_idn not in (select cust_idn from confer.dbo.V2_繳款記錄)

declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(1000)
declare @ColumnName varchar(50), @RunDate datetime, @MaxDate datetime, @DateStr char(8)

-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式)
select @MaxDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '25') + 19110000)
from sysobjects obj join syscolumns col on obj.id = col.id
where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'

set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE'
set @vSQL2 = 'update V2_繳款記錄 set first_in_date = dateadd(day, 15, first_pay_date) where (0=1'

set @RunDate = @MaxDate
while @RunDate >= '2006-02-01' begin

set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate))
set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName
set @vSQL2 = @vSQL2 + ' or upper(' + @ColumnName + ') = ''Y'''

set @RunDate = dateadd(month, -1, @RunDate)
continue end

set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2 join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN'
print @vSQL1
execute sp_executesql @vSQL1

update V2_繳款記錄 set last_in_date = null, first_in_date = null, times = 0
set @vSQL2 = @vSQL2 + ')'
print @vSQL2
execute sp_executesql @vSQL2


set @RunDate = @MaxDate
while @RunDate > '2006-02-01' begin
set @DateStr = convert(char(8), @RunDate, 112)

set @vSQL1 = 'update V2_繳款記錄 set times = times + 1,'
+ ' last_in_date = case when last_in_date is null then ''' + @DateStr + ''' else last_in_date end'
+ ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100)
+ ' = ''Y'''
print @vSQL1
execute sp_executesql @vSQL1

set @RunDate = dateadd(month, -1, @RunDate)
continue end

(轉貼時請加註來源)
引用自:藍色小惡魔《用 SQL 語法動態組合 SQL》
http://www.imp.idv.tw/play/forum/viewthread?thread=743

這種操死 User 的作法當然不是長久之計 但是也不能操死自己 所以只好玩 SQL 填補需求裂口

good luck

xuite, pixnet

2016/7/27:71
2016/7/27:18
----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣，越多人知道越有價值；知識最好的備份方法，散播！
藍色小惡魔(林永傑)： 臉書
----------------------------------------
[編輯文章 11 次, 最後修改： jieh 於 2017/1/11 下午 04:21:57]
[2007/3/21 下午 12:13:00]   [返迴此篇文章頂端 ]  回到頂端 

Powered by mvnForum 1.0.2 GA (Build: 15 July 2006)
Copyright © 2002-2006 by MyVietnam.net