搜尋 Table 結果：
今天發生一個SQL奇怪的現象，記錄一下
就是訂單列表(使用分頁的SQL 找出 top 20)跑很久才出來，大約10多秒
SQL指令條件僅有訂單日期(預設是1年內訂單，訂單總數有130萬筆)，而訂單日期也有做index
之前都大約1~2秒就出來，這一兩天卻要10秒才跑出來
用執行計畫評估功能查也看不出哪裡有問題，把一些子查詢拿掉也沒有改善
所以就改一下搜尋條件，改搜尋半年內訂單，大約 5秒
改搜尋兩年內訂單，1秒!!! 真是神奇，把條件區間拉大反而很快???????
直覺上覺得索引table是不是太大太亂了，剛好看到CreateDate索引有個重建按鈕
就勇敢把他按下去，想說會不會跑很久，結果重建只要不到1秒就好了
然後神奇的事發生了，訂單列表不須一秒就顯示出來啦!
SQL 指令大概長這樣
參考 will will web 的舊文章，原來定期把索引整理整理，也是要記得做的。
不過週期上我想 1季或半年跑一次應該就可以了
-------------------------------------------------------------------
後記: 2020/6/22 - 一年後又發生一樣的狀況，真是神奇
這次 OrderMain CreateDate 沒有很分散。只有 2.X %
想說其他 index 是不是該重建，但是都沒有效果
最後只好重建 CreateDate --> 結果居然解決了
看 2021年會在發生嗎?
就是訂單列表(使用分頁的SQL 找出 top 20)跑很久才出來，大約10多秒
SQL指令條件僅有訂單日期(預設是1年內訂單，訂單總數有130萬筆)，而訂單日期也有做index
之前都大約1~2秒就出來，這一兩天卻要10秒才跑出來
用執行計畫評估功能查也看不出哪裡有問題，把一些子查詢拿掉也沒有改善
所以就改一下搜尋條件，改搜尋半年內訂單，大約 5秒
改搜尋兩年內訂單，1秒!!! 真是神奇，把條件區間拉大反而很快???????
直覺上覺得索引table是不是太大太亂了，剛好看到CreateDate索引有個重建按鈕
就勇敢把他按下去，想說會不會跑很久，結果重建只要不到1秒就好了
然後神奇的事發生了，訂單列表不須一秒就顯示出來啦!
SQL 指令大概長這樣
Select * from (
Select Top 20 * from (
select Top 20 a.*
--, CompelteOrderId= (select top 1 K.Id from packing_list_main k With(NoLock) join Order_Main p With(NoLock) on k.Order_Id=p.Id where k.En_Packing_List_Status=300 and P.MEmber_Id=a.Member_Id )
--, [MemoCount_前台問題] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 1)
--, [MemoCount_業務備註] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 3)
--, [MemoCount_系統檢查] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 4)
--, QnACount = (select count(*) from Order_QnA With(NoLock) where Order_Id = a.Id)
from V_Order_Main_For_Admin_List a With(NoLock) left join Member b with(NoLock) on a.Member_Id=b.Id where (1=1) and En_Order_Status <> -300 and a.Create_Date >='2018-06-20' order by Id Desc
) AS T1 order by Id
) AS T2 order by Id Desc
參考 will will web 的舊文章，原來定期把索引整理整理，也是要記得做的。
不過週期上我想 1季或半年跑一次應該就可以了
-------------------------------------------------------------------
後記: 2020/6/22 - 一年後又發生一樣的狀況，真是神奇
這次 OrderMain CreateDate 沒有很分散。只有 2.X %
想說其他 index 是不是該重建，但是都沒有效果
最後只好重建 CreateDate --> 結果居然解決了
看 2021年會在發生嗎?
darren, 2019/6/21 上午 12:05:00
如下, 要記得修改 database_name, userOLD, userNEW 三個參數
CREATE TABLE #Command
(
Id int NOT NULL IDENTITY (1, 1),
command nvarchar(MAX) NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
USE database_name -- Use the database from which you want to extract the permissions
GO
SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname
SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from
SET @NewUser = 'userNEW' --The user or role to which to copy the permissions to
insert into #Command(command)
Select convert(nvarchar(max), '--Database Context')
insert into #Command(command)
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME())
insert into #Command(command)
SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)
insert into #Command(command)
SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
insert into #Command(command)
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC
insert into #Command(command)
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Select command from #Command order by Id
drop table #Command
Bike, 2019/6/5 下午 08:42:56
建立兩個表 city 和 area, 並填入值:
CREATE TABLE [dbo].[city](
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (1, '基隆市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (2, '台北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (3, '新北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (4, '桃園市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (5, '新竹市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (6, '新竹縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (7, '苗栗縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (8, '台中市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (9, '彰化縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (10, '南投縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (11, '雲林縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (12, '嘉義市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (13, '嘉義縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (14, '台南市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (15, '高雄市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (16, '屏東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (17, '台東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (18, '花蓮縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (19, '宜蘭縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (20, '澎湖縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (21, '金門縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (22, '連江縣')
CREATE TABLE [dbo].[area](
[city_id] [int] NULL,
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
[post_code] [varchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(1, 1, '仁愛區', '200')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(2, 1, '信義區', '201')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(3, 1, '中正區', '202')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(4, 1, '中山區', '203')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(5, 1, '安樂區', '204')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(6, 1, '暖暖區', '205')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(7, 1, '七堵區', '206')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(8, 2, '中正區', '100')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(9, 2, '大同區', '103')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(10, 2, '中山區', '104')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(11, 2, '松山區', '105')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(12, 2, '大安區', '106')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(13, 2, '萬華區', '108')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(14, 2, '信義區', '110')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(15, 2, '士林區', '111')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(16, 2, '北投區', '112')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(17, 2, '內湖區', '114')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(18, 2, '南港區', '115')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(19, 2, '文山區', '116')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(20, 3, '萬里區', '207')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(21, 3, '金山區', '208')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(22, 3, '板橋區', '220')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(23, 3, '汐止區', '221')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(24, 3, '深坑區', '222')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(25, 3, '石碇區', '223')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(26, 3, '瑞芳區', '224')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(27, 3, '平溪區', '226')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(28, 3, '雙溪區', '227')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(29, 3, '貢寮區', '228')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(30, 3, '新店區', '231')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(31, 3, '坪林區', '232')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(32, 3, '烏來區', '233')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(33, 3, '永和區', '234')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(34, 3, '中和區', '235')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(35, 3, '土城區', '236')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(36, 3, '三峽區', '237')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(37, 3, '樹林區', '238')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(38, 3, '鶯歌區', '239')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(39, 3, '三重區', '241')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(40, 3, '新莊區', '242')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(41, 3, '泰山區', '243')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(42, 3, '林口區', '244')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(43, 3, '蘆洲區', '247')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(44, 3, '五股區', '248')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(45, 3, '八里區', '249')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(46, 3, '淡水區', '251')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(47, 3, '三芝區', '252')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(48, 3, '石門區', '253')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(49, 4, '中壢區', '320')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(50, 4, '平鎮區', '324')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(51, 4, '龍潭區', '325')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(52, 4, '楊梅區', '326')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(53, 4, '新屋區', '327')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(54, 4, '觀音區', '328')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(55, 4, '桃園區', '330')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(56, 4, '龜山區', '333')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(57, 4, '八德區', '334')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(58, 4, '大溪區', '335')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(59, 4, '復興區', '336')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(60, 4, '大園區', '337')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(61, 4, '蘆竹區', '338')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(62, 5, '新竹市', '300')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(63, 6, '竹北市', '302')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(64, 6, '湖口鄉', '303')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(65, 6, '新豐鄉', '304')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(66, 6, '新埔鎮', '305')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(67, 6, '關西鎮', '306')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(68, 6, '芎林鄉', '307')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(69, 6, '寶山鄉', '308')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(70, 6, '竹東鎮', '310')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(71, 6, '五峰鄉', '311')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(72, 6, '橫山鄉', '312')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(73, 6, '尖石鄉', '313')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(74, 6, '北埔鄉', '314')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(75, 6, '峨眉鄉', '315')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(76, 7, '竹南鎮', '350')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(77, 7, '頭份鎮', '351')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(78, 7, '三灣鄉', '352')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(79, 7, '南庄鄉', '353')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(80, 7, '獅潭鄉', '354')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(81, 7, '後龍鎮', '356')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(82, 7, '通霄鎮', '357')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(83, 7, '苑裡鎮', '358')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(84, 7, '苗栗市', '360')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(85, 7, '造橋鄉', '361')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(86, 7, '頭屋鄉', '362')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(87, 7, '公館鄉', '363')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(88, 7, '大湖鄉', '364')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(89, 7, '泰安鄉', '365')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(90, 7, '銅鑼鄉', '366')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(91, 7, '三義鄉', '367')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(92, 7, '西湖鄉', '368')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(93, 7, '卓蘭鎮', '369')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(94, 8, '中區', '400')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(95, 8, '東區', '401')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(96, 8, '南區', '402')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(97, 8, '西區', '403')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(98, 8, '北區', '404')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(99, 8, '北屯區', '406')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(100, 8, '西屯區', '407')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(101, 8, '南屯區', '408')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(102, 8, '太平區', '411')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(103, 8, '大里區', '412')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(104, 8, '霧峰區', '413')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(105, 8, '烏日區', '414')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(106, 8, '豐原區', '420')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(107, 8, '后里區', '421')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(108, 8, '石岡區', '422')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(109, 8, '東勢區', '423')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(110, 8, '和平區', '424')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(111, 8, '新社區', '426')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(112, 8, '潭子區', '427')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(113, 8, '大雅區', '428')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(114, 8, '神岡區', '429')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(115, 8, '大肚區', '432')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(116, 8, '沙鹿區', '433')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(117, 8, '龍井區', '434')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(118, 8, '梧棲區', '435')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(119, 8, '清水區', '436')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(120, 8, '大甲區', '437')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(121, 8, '外埔區', '438')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(122, 8, '大安區', '439')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(123, 9, '彰化市', '500')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(124, 9, '芬園鄉', '502')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(125, 9, '花壇鄉', '503')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(126, 9, '秀水鄉', '504')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(127, 9, '鹿港鎮', '505')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(128, 9, '福興鄉', '506')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(129, 9, '線西鄉', '507')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(130, 9, '和美鄉', '508')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(131, 9, '伸港鄉', '509')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(132, 9, '員林鎮', '510')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(133, 9, '社頭鄉', '511')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(134, 9, '永靖鄉', '512')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(135, 9, '埔心鄉', '513')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(136, 9, '溪湖鎮', '514')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(137, 9, '大村鄉', '515')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(138, 9, '埔鹽鄉', '516')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(139, 9, '田中鎮', '520')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(140, 9, '北斗鎮', '521')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(141, 9, '田尾鄉', '522')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(142, 9, '埤頭鄉', '523')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(143, 9, '溪州鄉', '524')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(144, 9, '竹塘鄉', '525')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(145, 9, '二林鎮', '526')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(146, 9, '大城鄉', '527')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(147, 9, '芳苑鄉', '528')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(148, 9, '二水鄉', '530')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(149, 10, '南投市', '540')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(150, 10, '中寮鄉', '541')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(151, 10, '草屯鎮', '542')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(152, 10, '國姓鄉', '544')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(153, 10, '埔里鎮', '545')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(154, 10, '仁愛鄉', '546')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(155, 10, '名間鄉', '551')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(156, 10, '集集鎮', '552')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(157, 10, '水里鄉', '553')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(158, 10, '魚池鄉', '555')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(159, 10, '信義鄉', '556')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(160, 10, '竹山鎮', '557')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(161, 10, '鹿谷鄉', '558')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(162, 11, '斗南鎮', '630')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(163, 11, '大埤鄉', '631')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(164, 11, '虎尾鎮', '632')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(165, 11, '土庫鎮', '633')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(166, 11, '褒忠鄉', '634')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(167, 11, '東勢鄉', '635')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(168, 11, '台西鄉', '636')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(169, 11, '崙背鄉', '637')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(170, 11, '麥寮鄉', '638')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(171, 11, '斗六市', '640')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(172, 11, '林內鄉', '643')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(173, 11, '古坑鄉', '646')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(174, 11, '莿桐鄉', '647')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(175, 11, '西螺鎮', '648')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(176, 11, '二崙鄉', '649')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(177, 11, '北港鎮', '651')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(178, 11, '水林鄉', '652')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(179, 11, '口湖鄉', '653')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(180, 11, '四湖鄉', '654')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(181, 11, '元長鄉', '655')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(182, 12, '嘉義市', '600')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(183, 13, '番路鄉', '602')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(184, 13, '梅山鄉', '603')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(185, 13, '竹崎鄉', '604')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(186, 13, '阿里山', '605')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(187, 13, '中埔鄉', '606')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(188, 13, '大埔鄉', '607')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(189, 13, '水上鄉', '608')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(190, 13, '鹿草鄉', '611')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(191, 13, '太保鄉', '612')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(192, 13, '朴子市', '613')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(193, 13, '東石鄉', '614')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(194, 13, '六腳鄉', '615')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(195, 13, '新港鄉', '616')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(196, 13, '民雄鄉', '621')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(197, 13, '大林鎮', '622')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(198, 13, '溪口鄉', '623')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(199, 13, '義竹鄉', '624')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(200, 13, '布袋鄉', '625')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(201, 14, '中西區', '700')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(202, 14, '東區', '701')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(203, 14, '南區', '702')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(204, 14, '北區', '704')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(205, 14, '安平區', '708')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(206, 14, '安南區', '709')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(207, 14, '永康區', '710')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(208, 14, '歸仁區', '711')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(209, 14, '新化區', '712')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(210, 14, '左鎮區', '713')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(211, 14, '玉井區', '714')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(212, 14, '楠西區', '715')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(213, 14, '南化區', '716')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(214, 14, '仁德區', '717')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(215, 14, '關廟區', '718')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(216, 14, '龍崎區', '719')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(217, 14, '官田區', '720')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(218, 14, '麻豆區', '721')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(219, 14, '佳里區', '722')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(220, 14, '西港區', '723')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(221, 14, '七股區', '724')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(222, 14, '將軍區', '725')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(223, 14, '學甲區', '726')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(224, 14, '北門區', '727')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(225, 14, '新營區', '730')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(226, 14, '後壁區', '731')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(227, 14, '白河區', '732')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(228, 14, '東山區', '733')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(229, 14, '六甲區', '734')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(230, 14, '下營區', '735')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(231, 14, '柳營區', '736')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(232, 14, '鹽水區', '737')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(233, 14, '善化區', '741')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(234, 14, '大內區', '742')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(235, 14, '山上區', '743')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(236, 14, '新市區', '744')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(237, 14, '安定區', '745')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(238, 15, '新興區', '800')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(239, 15, '前金區', '801')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(240, 15, '苓雅區', '802')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(241, 15, '鹽埕區', '803')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(242, 15, '鼓山區', '804')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(243, 15, '旗津區', '805')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(244, 15, '前鎮區', '806')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(245, 15, '三民區', '807')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(246, 15, '楠梓區', '811')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(247, 15, '小港區', '812')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(248, 15, '左營區', '813')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(249, 15, '仁武區', '814')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(250, 15, '大社區', '815')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(251, 15, '岡山區', '820')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(252, 15, '路竹區', '821')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(253, 15, '阿蓮區', '822')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(254, 15, '田寮區', '823')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(255, 15, '燕巢區', '824')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(256, 15, '橋頭區', '825')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(257, 15, '梓官區', '826')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(258, 15, '彌陀區', '827')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(259, 15, '永安區', '828')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(260, 15, '湖內區', '829')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(261, 15, '鳳山區', '830')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(262, 15, '大寮區', '831')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(263, 15, '林園區', '832')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(264, 15, '鳥松區', '833')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(265, 15, '大樹區', '840')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(266, 15, '旗山區', '842')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(267, 15, '美濃區', '843')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(268, 15, '六龜區', '844')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(269, 15, '內門區', '845')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(270, 15, '杉林區', '846')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(271, 15, '甲仙區', '847')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(272, 15, '桃源區', '848')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(273, 15, '那瑪夏區', '849')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(274, 15, '茂林區', '851')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(275, 15, '茄萣區', '852')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(276, 15, '東沙', '817')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(277, 15, '南沙', '819')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(278, 16, '屏東市', '900')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(279, 16, '三地鄉', '901')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(280, 16, '霧台鄉', '902')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(281, 16, '瑪家鄉', '903')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(282, 16, '九如鄉', '904')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(283, 16, '里港鄉', '905')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(284, 16, '高樹鄉', '906')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(285, 16, '鹽埔鄉', '907')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(286, 16, '長治鄉', '908')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(287, 16, '麟洛鄉', '909')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(288, 16, '竹田鄉', '911')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(289, 16, '內埔鄉', '912')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(290, 16, '萬丹鄉', '913')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(291, 16, '潮州鎮', '920')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(292, 16, '泰武鄉', '921')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(293, 16, '來義鄉', '922')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(294, 16, '萬巒鄉', '923')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(295, 16, '崁頂鄉', '924')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(296, 16, '新埤鄉', '925')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(297, 16, '南州鄉', '926')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(298, 16, '林邊鄉', '927')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(299, 16, '東港鄉', '928')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(300, 16, '琉球鄉', '929')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(301, 16, '佳冬鄉', '931')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(302, 16, '新園鄉', '932')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(303, 16, '枋寮鄉', '940')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(304, 16, '枋山鄉', '941')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(305, 16, '春日鄉', '942')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(306, 16, '獅子鄉', '943')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(307, 16, '車城鄉', '944')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(308, 16, '牡丹鄉', '945')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(309, 16, '恆春鎮', '946')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(310, 16, '滿洲鄉', '947')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(311, 17, '台東市', '950')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(312, 17, '綠島鄉', '951')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(313, 17, '蘭嶼鄉', '952')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(314, 17, '延平鄉', '953')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(315, 17, '卑南鄉', '954')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(316, 17, '鹿野鄉', '955')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(317, 17, '關山鎮', '956')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(318, 17, '海端鄉', '957')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(319, 17, '池上鄉', '958')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(320, 17, '東河鄉', '959')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(321, 17, '成功鎮', '961')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(322, 17, '長濱鄉', '962')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(323, 17, '太麻里', '963')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(324, 17, '金峰鄉', '964')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(325, 17, '大武鄉', '965')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(326, 17, '達仁鄉', '966')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(327, 18, '花蓮市', '970')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(328, 18, '新城鄉', '971')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(329, 18, '秀林鄉', '972')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(330, 18, '吉安鄉', '973')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(331, 18, '壽豐鄉', '974')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(332, 18, '鳳林鎮', '975')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(333, 18, '光復鄉', '976')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(334, 18, '豐濱鄉', '977')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(335, 18, '瑞穗鄉', '978')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(336, 18, '萬榮鄉', '979')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(337, 18, '玉里鎮', '981')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(338, 18, '卓溪鄉', '982')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(339, 18, '富里鄉', '983')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(340, 19, '宜蘭巿', '260')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(341, 19, '頭城鎮', '261')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(342, 19, '礁溪鄉', '262')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(343, 19, '壯圍鄉', '263')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(344, 19, '員山鄉', '264')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(345, 19, '羅東鎮', '265')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(346, 19, '三星鄉', '266')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(347, 19, '大同鄉', '267')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(348, 19, '五結鄉', '268')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(349, 19, '冬山鄉', '269')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(350, 19, '蘇澳鎮', '270')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(351, 19, '南澳鄉', '272')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(352, 19, '釣魚台', '290')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(353, 20, '馬公市', '880')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(354, 20, '西嶼鄉', '881')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(355, 20, '望安鄉', '882')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(356, 20, '七美鄉', '883')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(357, 20, '白沙鄉', '884')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(358, 20, '湖西鄉', '885')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(359, 21, '金沙鎮', '890')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(360, 21, '金湖鎮', '891')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(361, 21, '金寧鄉', '892')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(362, 21, '金城鎮', '893')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(363, 21, '烈嶼鄉', '894')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(364, 21, '烏坵', '896')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(365, 22, '南竿', '209')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(366, 22, '北竿', '210')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(367, 22, '莒光', '211')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(368, 22, '東引', '212')
CREATE TABLE [dbo].[city](
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (1, '基隆市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (2, '台北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (3, '新北市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (4, '桃園市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (5, '新竹市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (6, '新竹縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (7, '苗栗縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (8, '台中市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (9, '彰化縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (10, '南投縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (11, '雲林縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (12, '嘉義市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (13, '嘉義縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (14, '台南市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (15, '高雄市')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (16, '屏東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (17, '台東縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (18, '花蓮縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (19, '宜蘭縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (20, '澎湖縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (21, '金門縣')
INSERT INTO [dbo].[city] ([id] ,[name]) VALUES (22, '連江縣')
CREATE TABLE [dbo].[area](
[city_id] [int] NULL,
[id] [int] NOT NULL,
[name] [varchar](8) NULL,
[post_code] [varchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(1, 1, '仁愛區', '200')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(2, 1, '信義區', '201')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(3, 1, '中正區', '202')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(4, 1, '中山區', '203')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(5, 1, '安樂區', '204')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(6, 1, '暖暖區', '205')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(7, 1, '七堵區', '206')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(8, 2, '中正區', '100')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(9, 2, '大同區', '103')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(10, 2, '中山區', '104')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(11, 2, '松山區', '105')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(12, 2, '大安區', '106')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(13, 2, '萬華區', '108')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(14, 2, '信義區', '110')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(15, 2, '士林區', '111')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(16, 2, '北投區', '112')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(17, 2, '內湖區', '114')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(18, 2, '南港區', '115')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(19, 2, '文山區', '116')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(20, 3, '萬里區', '207')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(21, 3, '金山區', '208')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(22, 3, '板橋區', '220')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(23, 3, '汐止區', '221')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(24, 3, '深坑區', '222')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(25, 3, '石碇區', '223')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(26, 3, '瑞芳區', '224')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(27, 3, '平溪區', '226')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(28, 3, '雙溪區', '227')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(29, 3, '貢寮區', '228')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(30, 3, '新店區', '231')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(31, 3, '坪林區', '232')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(32, 3, '烏來區', '233')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(33, 3, '永和區', '234')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(34, 3, '中和區', '235')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(35, 3, '土城區', '236')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(36, 3, '三峽區', '237')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(37, 3, '樹林區', '238')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(38, 3, '鶯歌區', '239')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(39, 3, '三重區', '241')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(40, 3, '新莊區', '242')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(41, 3, '泰山區', '243')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(42, 3, '林口區', '244')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(43, 3, '蘆洲區', '247')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(44, 3, '五股區', '248')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(45, 3, '八里區', '249')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(46, 3, '淡水區', '251')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(47, 3, '三芝區', '252')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(48, 3, '石門區', '253')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(49, 4, '中壢區', '320')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(50, 4, '平鎮區', '324')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(51, 4, '龍潭區', '325')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(52, 4, '楊梅區', '326')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(53, 4, '新屋區', '327')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(54, 4, '觀音區', '328')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(55, 4, '桃園區', '330')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(56, 4, '龜山區', '333')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(57, 4, '八德區', '334')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(58, 4, '大溪區', '335')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(59, 4, '復興區', '336')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(60, 4, '大園區', '337')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(61, 4, '蘆竹區', '338')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(62, 5, '新竹市', '300')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(63, 6, '竹北市', '302')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(64, 6, '湖口鄉', '303')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(65, 6, '新豐鄉', '304')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(66, 6, '新埔鎮', '305')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(67, 6, '關西鎮', '306')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(68, 6, '芎林鄉', '307')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(69, 6, '寶山鄉', '308')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(70, 6, '竹東鎮', '310')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(71, 6, '五峰鄉', '311')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(72, 6, '橫山鄉', '312')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(73, 6, '尖石鄉', '313')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(74, 6, '北埔鄉', '314')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(75, 6, '峨眉鄉', '315')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(76, 7, '竹南鎮', '350')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(77, 7, '頭份鎮', '351')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(78, 7, '三灣鄉', '352')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(79, 7, '南庄鄉', '353')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(80, 7, '獅潭鄉', '354')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(81, 7, '後龍鎮', '356')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(82, 7, '通霄鎮', '357')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(83, 7, '苑裡鎮', '358')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(84, 7, '苗栗市', '360')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(85, 7, '造橋鄉', '361')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(86, 7, '頭屋鄉', '362')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(87, 7, '公館鄉', '363')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(88, 7, '大湖鄉', '364')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(89, 7, '泰安鄉', '365')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(90, 7, '銅鑼鄉', '366')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(91, 7, '三義鄉', '367')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(92, 7, '西湖鄉', '368')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(93, 7, '卓蘭鎮', '369')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(94, 8, '中區', '400')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(95, 8, '東區', '401')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(96, 8, '南區', '402')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(97, 8, '西區', '403')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(98, 8, '北區', '404')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(99, 8, '北屯區', '406')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(100, 8, '西屯區', '407')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(101, 8, '南屯區', '408')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(102, 8, '太平區', '411')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(103, 8, '大里區', '412')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(104, 8, '霧峰區', '413')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(105, 8, '烏日區', '414')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(106, 8, '豐原區', '420')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(107, 8, '后里區', '421')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(108, 8, '石岡區', '422')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(109, 8, '東勢區', '423')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(110, 8, '和平區', '424')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(111, 8, '新社區', '426')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(112, 8, '潭子區', '427')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(113, 8, '大雅區', '428')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(114, 8, '神岡區', '429')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(115, 8, '大肚區', '432')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(116, 8, '沙鹿區', '433')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(117, 8, '龍井區', '434')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(118, 8, '梧棲區', '435')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(119, 8, '清水區', '436')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(120, 8, '大甲區', '437')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(121, 8, '外埔區', '438')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(122, 8, '大安區', '439')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(123, 9, '彰化市', '500')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(124, 9, '芬園鄉', '502')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(125, 9, '花壇鄉', '503')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(126, 9, '秀水鄉', '504')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(127, 9, '鹿港鎮', '505')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(128, 9, '福興鄉', '506')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(129, 9, '線西鄉', '507')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(130, 9, '和美鄉', '508')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(131, 9, '伸港鄉', '509')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(132, 9, '員林鎮', '510')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(133, 9, '社頭鄉', '511')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(134, 9, '永靖鄉', '512')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(135, 9, '埔心鄉', '513')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(136, 9, '溪湖鎮', '514')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(137, 9, '大村鄉', '515')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(138, 9, '埔鹽鄉', '516')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(139, 9, '田中鎮', '520')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(140, 9, '北斗鎮', '521')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(141, 9, '田尾鄉', '522')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(142, 9, '埤頭鄉', '523')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(143, 9, '溪州鄉', '524')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(144, 9, '竹塘鄉', '525')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(145, 9, '二林鎮', '526')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(146, 9, '大城鄉', '527')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(147, 9, '芳苑鄉', '528')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(148, 9, '二水鄉', '530')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(149, 10, '南投市', '540')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(150, 10, '中寮鄉', '541')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(151, 10, '草屯鎮', '542')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(152, 10, '國姓鄉', '544')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(153, 10, '埔里鎮', '545')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(154, 10, '仁愛鄉', '546')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(155, 10, '名間鄉', '551')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(156, 10, '集集鎮', '552')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(157, 10, '水里鄉', '553')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(158, 10, '魚池鄉', '555')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(159, 10, '信義鄉', '556')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(160, 10, '竹山鎮', '557')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(161, 10, '鹿谷鄉', '558')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(162, 11, '斗南鎮', '630')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(163, 11, '大埤鄉', '631')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(164, 11, '虎尾鎮', '632')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(165, 11, '土庫鎮', '633')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(166, 11, '褒忠鄉', '634')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(167, 11, '東勢鄉', '635')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(168, 11, '台西鄉', '636')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(169, 11, '崙背鄉', '637')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(170, 11, '麥寮鄉', '638')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(171, 11, '斗六市', '640')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(172, 11, '林內鄉', '643')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(173, 11, '古坑鄉', '646')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(174, 11, '莿桐鄉', '647')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(175, 11, '西螺鎮', '648')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(176, 11, '二崙鄉', '649')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(177, 11, '北港鎮', '651')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(178, 11, '水林鄉', '652')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(179, 11, '口湖鄉', '653')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(180, 11, '四湖鄉', '654')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(181, 11, '元長鄉', '655')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(182, 12, '嘉義市', '600')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(183, 13, '番路鄉', '602')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(184, 13, '梅山鄉', '603')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(185, 13, '竹崎鄉', '604')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(186, 13, '阿里山', '605')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(187, 13, '中埔鄉', '606')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(188, 13, '大埔鄉', '607')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(189, 13, '水上鄉', '608')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(190, 13, '鹿草鄉', '611')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(191, 13, '太保鄉', '612')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(192, 13, '朴子市', '613')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(193, 13, '東石鄉', '614')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(194, 13, '六腳鄉', '615')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(195, 13, '新港鄉', '616')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(196, 13, '民雄鄉', '621')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(197, 13, '大林鎮', '622')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(198, 13, '溪口鄉', '623')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(199, 13, '義竹鄉', '624')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(200, 13, '布袋鄉', '625')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(201, 14, '中西區', '700')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(202, 14, '東區', '701')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(203, 14, '南區', '702')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(204, 14, '北區', '704')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(205, 14, '安平區', '708')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(206, 14, '安南區', '709')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(207, 14, '永康區', '710')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(208, 14, '歸仁區', '711')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(209, 14, '新化區', '712')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(210, 14, '左鎮區', '713')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(211, 14, '玉井區', '714')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(212, 14, '楠西區', '715')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(213, 14, '南化區', '716')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(214, 14, '仁德區', '717')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(215, 14, '關廟區', '718')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(216, 14, '龍崎區', '719')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(217, 14, '官田區', '720')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(218, 14, '麻豆區', '721')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(219, 14, '佳里區', '722')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(220, 14, '西港區', '723')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(221, 14, '七股區', '724')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(222, 14, '將軍區', '725')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(223, 14, '學甲區', '726')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(224, 14, '北門區', '727')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(225, 14, '新營區', '730')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(226, 14, '後壁區', '731')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(227, 14, '白河區', '732')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(228, 14, '東山區', '733')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(229, 14, '六甲區', '734')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(230, 14, '下營區', '735')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(231, 14, '柳營區', '736')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(232, 14, '鹽水區', '737')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(233, 14, '善化區', '741')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(234, 14, '大內區', '742')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(235, 14, '山上區', '743')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(236, 14, '新市區', '744')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(237, 14, '安定區', '745')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(238, 15, '新興區', '800')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(239, 15, '前金區', '801')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(240, 15, '苓雅區', '802')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(241, 15, '鹽埕區', '803')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(242, 15, '鼓山區', '804')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(243, 15, '旗津區', '805')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(244, 15, '前鎮區', '806')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(245, 15, '三民區', '807')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(246, 15, '楠梓區', '811')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(247, 15, '小港區', '812')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(248, 15, '左營區', '813')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(249, 15, '仁武區', '814')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(250, 15, '大社區', '815')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(251, 15, '岡山區', '820')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(252, 15, '路竹區', '821')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(253, 15, '阿蓮區', '822')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(254, 15, '田寮區', '823')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(255, 15, '燕巢區', '824')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(256, 15, '橋頭區', '825')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(257, 15, '梓官區', '826')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(258, 15, '彌陀區', '827')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(259, 15, '永安區', '828')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(260, 15, '湖內區', '829')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(261, 15, '鳳山區', '830')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(262, 15, '大寮區', '831')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(263, 15, '林園區', '832')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(264, 15, '鳥松區', '833')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(265, 15, '大樹區', '840')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(266, 15, '旗山區', '842')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(267, 15, '美濃區', '843')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(268, 15, '六龜區', '844')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(269, 15, '內門區', '845')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(270, 15, '杉林區', '846')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(271, 15, '甲仙區', '847')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(272, 15, '桃源區', '848')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(273, 15, '那瑪夏區', '849')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(274, 15, '茂林區', '851')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(275, 15, '茄萣區', '852')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(276, 15, '東沙', '817')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(277, 15, '南沙', '819')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(278, 16, '屏東市', '900')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(279, 16, '三地鄉', '901')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(280, 16, '霧台鄉', '902')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(281, 16, '瑪家鄉', '903')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(282, 16, '九如鄉', '904')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(283, 16, '里港鄉', '905')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(284, 16, '高樹鄉', '906')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(285, 16, '鹽埔鄉', '907')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(286, 16, '長治鄉', '908')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(287, 16, '麟洛鄉', '909')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(288, 16, '竹田鄉', '911')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(289, 16, '內埔鄉', '912')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(290, 16, '萬丹鄉', '913')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(291, 16, '潮州鎮', '920')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(292, 16, '泰武鄉', '921')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(293, 16, '來義鄉', '922')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(294, 16, '萬巒鄉', '923')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(295, 16, '崁頂鄉', '924')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(296, 16, '新埤鄉', '925')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(297, 16, '南州鄉', '926')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(298, 16, '林邊鄉', '927')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(299, 16, '東港鄉', '928')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(300, 16, '琉球鄉', '929')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(301, 16, '佳冬鄉', '931')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(302, 16, '新園鄉', '932')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(303, 16, '枋寮鄉', '940')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(304, 16, '枋山鄉', '941')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(305, 16, '春日鄉', '942')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(306, 16, '獅子鄉', '943')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(307, 16, '車城鄉', '944')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(308, 16, '牡丹鄉', '945')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(309, 16, '恆春鎮', '946')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(310, 16, '滿洲鄉', '947')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(311, 17, '台東市', '950')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(312, 17, '綠島鄉', '951')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(313, 17, '蘭嶼鄉', '952')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(314, 17, '延平鄉', '953')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(315, 17, '卑南鄉', '954')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(316, 17, '鹿野鄉', '955')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(317, 17, '關山鎮', '956')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(318, 17, '海端鄉', '957')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(319, 17, '池上鄉', '958')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(320, 17, '東河鄉', '959')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(321, 17, '成功鎮', '961')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(322, 17, '長濱鄉', '962')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(323, 17, '太麻里', '963')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(324, 17, '金峰鄉', '964')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(325, 17, '大武鄉', '965')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(326, 17, '達仁鄉', '966')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(327, 18, '花蓮市', '970')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(328, 18, '新城鄉', '971')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(329, 18, '秀林鄉', '972')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(330, 18, '吉安鄉', '973')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(331, 18, '壽豐鄉', '974')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(332, 18, '鳳林鎮', '975')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(333, 18, '光復鄉', '976')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(334, 18, '豐濱鄉', '977')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(335, 18, '瑞穗鄉', '978')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(336, 18, '萬榮鄉', '979')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(337, 18, '玉里鎮', '981')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(338, 18, '卓溪鄉', '982')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(339, 18, '富里鄉', '983')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(340, 19, '宜蘭巿', '260')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(341, 19, '頭城鎮', '261')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(342, 19, '礁溪鄉', '262')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(343, 19, '壯圍鄉', '263')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(344, 19, '員山鄉', '264')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(345, 19, '羅東鎮', '265')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(346, 19, '三星鄉', '266')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(347, 19, '大同鄉', '267')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(348, 19, '五結鄉', '268')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(349, 19, '冬山鄉', '269')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(350, 19, '蘇澳鎮', '270')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(351, 19, '南澳鄉', '272')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(352, 19, '釣魚台', '290')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(353, 20, '馬公市', '880')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(354, 20, '西嶼鄉', '881')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(355, 20, '望安鄉', '882')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(356, 20, '七美鄉', '883')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(357, 20, '白沙鄉', '884')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(358, 20, '湖西鄉', '885')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(359, 21, '金沙鎮', '890')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(360, 21, '金湖鎮', '891')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(361, 21, '金寧鄉', '892')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(362, 21, '金城鎮', '893')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(363, 21, '烈嶼鄉', '894')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(364, 21, '烏坵', '896')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(365, 22, '南竿', '209')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(366, 22, '北竿', '210')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(367, 22, '莒光', '211')
insert into area([id] ,[city_id] ,[name] ,[post_code]) values(368, 22, '東引', '212')
Bike, 2018/8/7 下午 12:06:12
UW -> DB 物件產生器會產生兩個function
GetAllDataFromBaseTableWithCache(string OrderFields = "_Default", bool IsCopy = true )
以及
Get物件名稱FromCachedDT(string Key)
-----------------------------------------------------------
設計上是希望將所有資料Cache在記憶體，然後前端直接抓出資料，不須到資料庫抓資料
理論上網頁速度應該比較佳，實際上卻發現是效能殺手
原因在於 GetAllDataFromBaseTableWithCache 是抓出整個table的資料放到 Cache
然後又做了一件事，當有人要資料時，會把整個 DataTable 做 Copy() 再 return
也就是說，當DataTable有數千筆資料列時
每叫用一次 "Get物件名稱FromCachedDT(string Key)" 就會把數千筆資料整個複製一次
再從複製出來的DataTable Select 出一列 return
------------------------------------------------------------
解決方法:
Get物件名稱FromCachedDT(string Key) --> 改成一筆一筆資料做 Cache，這樣 Copy() 只會Copy一筆
實測上，原本頁面要跑1.8秒，調整後可以 0.4 秒就完成
-----------------------------------------------------------
結論:
PG產生器就把 GetAllDataFromBaseTableWithCache 拿掉吧
GetAllDataFromBaseTableWithCache(string OrderFields = "_Default", bool IsCopy = true )
以及
Get物件名稱FromCachedDT(string Key)
-----------------------------------------------------------
設計上是希望將所有資料Cache在記憶體，然後前端直接抓出資料，不須到資料庫抓資料
理論上網頁速度應該比較佳，實際上卻發現是效能殺手
原因在於 GetAllDataFromBaseTableWithCache 是抓出整個table的資料放到 Cache
然後又做了一件事，當有人要資料時，會把整個 DataTable 做 Copy() 再 return
也就是說，當DataTable有數千筆資料列時
每叫用一次 "Get物件名稱FromCachedDT(string Key)" 就會把數千筆資料整個複製一次
再從複製出來的DataTable Select 出一列 return
------------------------------------------------------------
解決方法:
Get物件名稱FromCachedDT(string Key) --> 改成一筆一筆資料做 Cache，這樣 Copy() 只會Copy一筆
實測上，原本頁面要跑1.8秒，調整後可以 0.4 秒就完成
-----------------------------------------------------------
結論:
PG產生器就把 GetAllDataFromBaseTableWithCache 拿掉吧
darren, 2018/5/21 下午 03:17:00
以前年輕不懂事, 寫了一些很遜的程式碼, 例如:
舊的程式碼:
新的程式碼:
可以參考:
https://dotblogs.com.tw/yc421206/archive/2012/01/03/64154.aspx
舊的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 0)
{
SqlDataAdapter DA = new SqlDataAdapter(Sql, GetDBC(DBC));
DataTable DT = new DataTable();
try
{
DA = new SqlDataAdapter(Sql, DBC);
if (timeout > 0)
{
DA.SelectCommand.CommandTimeout = timeout;
}
DA.Fill(DT);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
DA.Dispose();
}
return DT;
}
新的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 30)
{
using (var DA = new SqlDataAdapter(Sql, GetDBC(DBC)))
{
//不可為 null
DataTable DT = new DataTable();
DA.SelectCommand.CommandTimeout = timeout;
DA.Fill(DT);
return DT;
}
}
可以參考:
https://dotblogs.com.tw/yc421206/archive/2012/01/03/64154.aspx
Bike, 2018/4/4 上午 07:56:39
轉換文章內容成為excel 檔案的時候
發生的錯誤
網路上查都好像是跟圖片有關的錯誤
後來才確認是 emoji 的問題
文章裡有出現
🙋♀️🙋♀️🙋♀️
這種東西
SQL:
SQL 好像也沒辦法完美的取代 ( 如果有辦法對準是哪個字元就可以 但如果是一個字串好像會無法取代)
只有剛好對到那個字元開頭時才能換
NCHAR(65039) NCHAR(8205)
Select TOP 10
Replace(SUBSTRING(content,10,15),NCHAR(65039) ,'XX') , ---HongKong️️️🙋♀️怎
REPLACE(SUBSTRING(content,17,8),NCHAR(65039),'OO') ---OO怎
FROM [Table]
C#
最後回到C#來處理
首先用 把string .ToArray() 變成char
找出字元後 轉成 int 來確認要怎麼表達這個 char
最後結果就變成>>
Convert.ToInt32(Table.Rows[0]["Content"].ToString().ToArray());
之後直接變成 char去取代
row["mycolumn"].ToString().Replace((char)65039, ' ').Replace((char)8205, ' ');
然後就暫時 沒錯誤了
只是這網站一直再更新
https://emojipedia.org/unicode-12.0/
所以未來可能還會有新的問題
目前還不知道甚麼快速的解法
發生的錯誤
網路上查都好像是跟圖片有關的錯誤
後來才確認是 emoji 的問題
文章裡有出現
🙋♀️🙋♀️🙋♀️
這種東西
SQL:
SQL 好像也沒辦法完美的取代 ( 如果有辦法對準是哪個字元就可以 但如果是一個字串好像會無法取代)
只有剛好對到那個字元開頭時才能換
NCHAR(65039) NCHAR(8205)
Select TOP 10
Replace(SUBSTRING(content,10,15),NCHAR(65039) ,'XX') , ---HongKong️️️🙋♀️怎
REPLACE(SUBSTRING(content,17,8),NCHAR(65039),'OO') ---OO怎
FROM [Table]
C#
最後回到C#來處理
首先用 把string .ToArray() 變成char
找出字元後 轉成 int 來確認要怎麼表達這個 char
最後結果就變成>>
Convert.ToInt32(Table.Rows[0]["Content"].ToString().ToArray());
之後直接變成 char去取代
row["mycolumn"].ToString().Replace((char)65039, ' ').Replace((char)8205, ' ');
然後就暫時 沒錯誤了
只是這網站一直再更新
https://emojipedia.org/unicode-12.0/
所以未來可能還會有新的問題
目前還不知道甚麼快速的解法
sean, 2018/3/22 下午 03:21:41
修改欄位設定時，出現需要刪除資料表(drop table)再建立資料表(create table)才能修改時，
於 [工具] > [選項] > [設計工具]
清除防止儲存需要重新建立資料表的變更] 核取方塊，即可。
如下圖
參考網址：https://support.microsoft.com/zh-tw/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-change
於 [工具] > [選項] > [設計工具]
清除防止儲存需要重新建立資料表的變更] 核取方塊，即可。
如下圖
參考網址：https://support.microsoft.com/zh-tw/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-change
choco, 2017/12/27 下午 01:49:41
<範例>
Table: [員工資料表]
欄位: [主管], [員工]
需求是: 當輸入一個員工姓名, 要把該員工下面所有階層的員工都Show出來
SQL語法:
參考網站: [SQL] 遞迴的寫法
Table: [員工資料表]
欄位: [主管], [員工]
需求是: 當輸入一個員工姓名, 要把該員工下面所有階層的員工都Show出來
SQL語法:
WITH [員工資料表new] AS (
--找出 [主管] 為 某人的資料當作 依據
SELECT [主管], [員工]
FROM [員工資料表]
WHERE [主管] = 'XXXX'
UNION ALL
--之後以 上面查出的結果 為依據遞迴查詢
SELECT e.[主管], e.[員工]
FROM [員工資料表] e
INNER JOIN [員工資料表new] ecte ON ecte.[員工] = e.[主管]
)
SELECT *
FROM [員工資料表new]
參考網站: [SQL] 遞迴的寫法
Reiko, 2017/12/18 下午 08:21:51
由於從欄位抓出的值 雖然是由逗點分隔 但整體資料容易被當成一組單一字串
例如
Table
#SomeTable
欄位 :
Id MemberIds
值
1 12,30,58
2 45,68,88
想要篩選 where member_Id in (select MemberIds from SomeTable where Id = 2)
會被當成 Id in ('45,68,88') 文字跟整數無法被篩選
解決方法之一
create table #SomeTable (Id int,MemberIds nvarchar(30))
insert into #SomeTable (Id,MemberIds) values (1,'12,30,58')
insert into #SomeTable (Id,MemberIds) values (2,'45,68,88')
select * from #SomeTable
create table #TargetTable (Id int,member_Id nvarchar(30))
insert into #TargetTable (Id,member_Id ) values (1,12)
insert into #TargetTable (Id,member_Id ) values (2,30)
insert into #TargetTable (Id,member_Id ) values (3,45)
insert into #TargetTable (Id,member_Id ) values (4,58)
insert into #TargetTable (Id,member_Id ) values (5,68)
insert into #TargetTable (Id,member_Id ) values (6,88)
select * from #TargetTable
DECLARE @MemberIds nvarchar(200);DECLARE @SQLString nvarchar(200);
select @MemberIds =MemberIds from #SomeTable(nolocK) where Id = 2
SET @SQLString = N' select * from #TargetTable where member_Id in (' + @MemberIds + ')' ;
EXECUTE sp_executesql @SQLString
將整體都當成字串 就可以執行了
如果用逗點隔開如果用逗點隔開的是字串
如:
Hi,Im,String
可以先把它加上單引號
select '''' + Replace(MemberIds ,',','''' +',' + '''') + '''' from #SomeTable
例如
Table
#SomeTable
欄位 :
Id MemberIds
值
1 12,30,58
2 45,68,88
想要篩選 where member_Id in (select MemberIds from SomeTable where Id = 2)
會被當成 Id in ('45,68,88') 文字跟整數無法被篩選
解決方法之一
create table #SomeTable (Id int,MemberIds nvarchar(30))
insert into #SomeTable (Id,MemberIds) values (1,'12,30,58')
insert into #SomeTable (Id,MemberIds) values (2,'45,68,88')
select * from #SomeTable
create table #TargetTable (Id int,member_Id nvarchar(30))
insert into #TargetTable (Id,member_Id ) values (1,12)
insert into #TargetTable (Id,member_Id ) values (2,30)
insert into #TargetTable (Id,member_Id ) values (3,45)
insert into #TargetTable (Id,member_Id ) values (4,58)
insert into #TargetTable (Id,member_Id ) values (5,68)
insert into #TargetTable (Id,member_Id ) values (6,88)
select * from #TargetTable
DECLARE @MemberIds nvarchar(200);DECLARE @SQLString nvarchar(200);
select @MemberIds =MemberIds from #SomeTable(nolocK) where Id = 2
SET @SQLString = N' select * from #TargetTable where member_Id in (' + @MemberIds + ')' ;
EXECUTE sp_executesql @SQLString
將整體都當成字串 就可以執行了
如果用逗點隔開如果用逗點隔開的是字串
如:
Hi,Im,String
可以先把它加上單引號
select '''' + Replace(MemberIds ,',','''' +',' + '''') + '''' from #SomeTable
sean, 2017/6/12 下午 12:09:48
我想大家一定會遇到要把資料匯出成 Excel 的需求. 以現有的工具, 大家想到作法大概都是先把資料放到一個 datatable 之中, 後叫用 UW.ExcelPOI.DTToExcelAndWriteToClient 就結束了.
前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
2. 如何設定字型大小和對齊方式:
這裡有件有有趣的事, 我一開始是這樣寫的.
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
Public Class Cell
Public Content As String
Public Colspan As Int32 = 1
Public Alignment As NPOI.SS.UserModel.HorizontalAlignment
Public FontHeightInPoints As Int32 = 0
Sub New(Content As String, Optional Colspan As Int32 = 1,
Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,
Optional FontHeightInPoints As Int32 = 0)
Me.Content = Content
Me.Colspan = Colspan
Me.Alignment = Alignment
Me.FontHeightInPoints = FontHeightInPoints
End Sub
End Class
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
Dim WR As HSSFRow
If ltRows IsNot Nothing Then
For Each ltRow As List(Of Cell) In ltRows
WR = WS.CreateRow(StartRow)
Dim C As Int32 = 0
For Each cell As Cell In ltRow
Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)
ic.SetCellValue(cell.Content)
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
If cell.Colspan > 1 Then
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
C += cell.Colspan - 1
End If
C += 1
Next
StartRow += 1
Next
End If
End Sub
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
2. 如何設定字型大小和對齊方式:
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
這裡有件有有趣的事, 我一開始是這樣寫的.
ic.CellStyle.Alignment = cell.Alignment
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))
Dim ltLine As New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,
NPOI.SS.UserModel.HorizontalAlignment.Center, 28))
ltHeader.Add(ltLine)
'第二行
ltLine = New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,
NPOI.SS.UserModel.HorizontalAlignment.Left, 20))
ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,
NPOI.SS.UserModel.HorizontalAlignment.Right, 20))
ltHeader.Add(ltLine)
UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)
Bike, 2017/6/4 下午 07:19:27