搜尋 lock 結果：
建立兩個表 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
抓出每個 Block_Id 中, End_Date 最小的資料.
語法 A:
With TempBlock as (
Select *,
ROW_NUMBER() OVER(PARTITION BY B.Block_Id ORDER BY B.End_Date, B.Id) AS rk
from BlockData B Where IsDelete = 'N' and End_Date >= getDate())
Select * from TempBlock Where rk = 1
語法 B:
Select * from (
Select *,
ROW_NUMBER() OVER(PARTITION BY B.Block_Id ORDER BY B.End_Date, B.Id) AS rk
from BlockData B Where IsDelete = 'N' and End_Date >= getDate()) TempBlock
Where rk = 1
語法 A:
With TempBlock as (
Select *,
ROW_NUMBER() OVER(PARTITION BY B.Block_Id ORDER BY B.End_Date, B.Id) AS rk
from BlockData B Where IsDelete = 'N' and End_Date >= getDate())
Select * from TempBlock Where rk = 1
語法 B:
Select * from (
Select *,
ROW_NUMBER() OVER(PARTITION BY B.Block_Id ORDER BY B.End_Date, B.Id) AS rk
from BlockData B Where IsDelete = 'N' and End_Date >= getDate()) TempBlock
Where rk = 1
Bike, 2017/12/27 下午 04:25:20
想到的先列出來.
1. 幫 String 擴充屬性(javascript, .NET)
2. SQL Injection.
3. Cross Site Script Attack.
4. 避免排程未完全執行以及重覆執行.
5. 更新 Cache 與 Lock.
6. SQL Transaction.
1. 幫 String 擴充屬性(javascript, .NET)
2. SQL Injection.
3. Cross Site Script Attack.
4. 避免排程未完全執行以及重覆執行.
5. 更新 Cache 與 Lock.
6. SQL Transaction.
Bike, 2017/12/16 下午 04:56:39
lock 的 object 和 cachename 最好是相關的.
ex: lock (S3.LockObjs.GetLockerObject(CacheName))
內容後補..
ex: lock (S3.LockObjs.GetLockerObject(CacheName))
內容後補..
Bike, 2017/12/12 上午 08:33:07
由於從欄位抓出的值 雖然是由逗點分隔 但整體資料容易被當成一組單一字串
例如
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
用了大概一年半的 git flow(是 git 的一種開發規範) ，最近才開始有一點領會到分散式的精神，
先簡單比較一下，在開發新功能上 集中式與分散式的差別
SVN(集中式):
從 SVN Server Update到最新的版本 ->
開始照著需求修改程式 ->
遇到程式被鎖住請他unlock ->
繼續開發 ->
開發完成把 修改過的程式 commit 到 SVN Server 上
git(分散式): git flow
先從server 上的 repository 拉下最新的版本 到自己的 repository
(這邊很不一樣 git 會有一個主要的repository 然後開發端又有自己的) ->
建立一個新的Branch來作為開發用 ->
開發程式(不會去Server 上確認是有人在改相同的程式) ->
開發完 commit 到自己的local端的 repository ->
送一個合併 Branch 的需求 ->
管理的人做 Code Review 然後把開發的新分支合併
優缺點:
集中式從頭到尾都只有 1 個 repository，確保了所有人的程式都是同一個版本，
如果現在有兩個新的 feature 都要改同一支程式，就必須先後開發的必要，或是
日後做 merge 的動作，但集中式的設計，並不強迫我們做 code review 的動作
，所以要解決 conflict 時會額外耗時也抗拒。
或是我同時要開發兩個新功能，在集中式管理，我們勢必會包一包一起commit，
但在git flow 上就會開兩個 branch，在未來我們更容易了解整個開發歷史。
git flow 使用經驗中，獲得最多的其實是 Code Review 的過程，Code Review
大家會更能接受自己的做法被質疑，從討論中得到更簡易解法，進而改善整個專案
的品質和可維護性，同時可以降低duplicate code 的情況。
更重要的了解 git 後，可以在 github 上面跟全世界的人一起開發(雖然我沒機會到共
同開發其他專案)，有更通用的 naming convention，更多解決方案工具。
先簡單比較一下，在開發新功能上 集中式與分散式的差別
SVN(集中式):
從 SVN Server Update到最新的版本 ->
開始照著需求修改程式 ->
遇到程式被鎖住請他unlock ->
繼續開發 ->
開發完成把 修改過的程式 commit 到 SVN Server 上
git(分散式): git flow
先從server 上的 repository 拉下最新的版本 到自己的 repository
(這邊很不一樣 git 會有一個主要的repository 然後開發端又有自己的) ->
建立一個新的Branch來作為開發用 ->
開發程式(不會去Server 上確認是有人在改相同的程式) ->
開發完 commit 到自己的local端的 repository ->
送一個合併 Branch 的需求 ->
管理的人做 Code Review 然後把開發的新分支合併
優缺點:
集中式從頭到尾都只有 1 個 repository，確保了所有人的程式都是同一個版本，
如果現在有兩個新的 feature 都要改同一支程式，就必須先後開發的必要，或是
日後做 merge 的動作，但集中式的設計，並不強迫我們做 code review 的動作
，所以要解決 conflict 時會額外耗時也抗拒。
或是我同時要開發兩個新功能，在集中式管理，我們勢必會包一包一起commit，
但在git flow 上就會開兩個 branch，在未來我們更容易了解整個開發歷史。
git flow 使用經驗中，獲得最多的其實是 Code Review 的過程，Code Review
大家會更能接受自己的做法被質疑，從討論中得到更簡易解法，進而改善整個專案
的品質和可維護性，同時可以降低duplicate code 的情況。
更重要的了解 git 後，可以在 github 上面跟全世界的人一起開發(雖然我沒機會到共
同開發其他專案)，有更通用的 naming convention，更多解決方案工具。
瞇瞇, 2015/7/25 下午 08:09:54
自製input 的 radio button 和 checkbox 之前常見的做法是，在input旁邊加入:not(old)的選擇器，如下所例 (以下寫法為SCSS格式)
但是chrome的新版本(39版)後開始，不能直接在input旁邊加:not(old)，這段語法，點擊後，會無法正常顯示，所以解決的方法有兩個
1. 要使用:not(old)這段語法，不能在input旁邊加，必須是在他的外層上加，如下所例
2. 不使用:not(old)，改用:not(:checked)寫法，Selectors 4也建議使用此語法，如下所例
input[type="radio"]:not(old){
width:28px;
margin:0;
padding:0;
@include opacity(0);
}
input[type="radio"]:not(old) + label{
display: inline-block;
margin-left: -28px;
padding-left: 18px;
background: url("//www.shopunt.com/images/eng/btn/radio.png") no-repeat;
width:auto;
line-height: 12px;
}
input[type="radio"]:not(old):checked + label{
background: url("//www.shopunt.com/images/eng/btn/radio-r.png") no-repeat;
width:auto;
}但是chrome的新版本(39版)後開始，不能直接在input旁邊加:not(old)，這段語法，點擊後，會無法正常顯示，所以解決的方法有兩個
1. 要使用:not(old)這段語法，不能在input旁邊加，必須是在他的外層上加，如下所例
.r-btn:not(old){
input[type="radio"]{
width:28px;
margin:0;
padding:0;
@include opacity(0);
}
input[type="radio"] + label{
display: inline-block;
margin-left: -28px;
padding-left: 18px;
background: url("//www.shopunt.com/images/eng/btn/radio.png") no-repeat;
width:auto;
line-height: 12px;
}
input[type="radio"]:checked + label{
background: url("//www.shopunt.com/images/eng/btn/radio-r.png") no-repeat;
width:auto;
}
}2. 不使用:not(old)，改用:not(:checked)寫法，Selectors 4也建議使用此語法，如下所例
input[type="radio"]:not(:checked), input[type="radio"]:checked {
position: absolute;
left: -9999px;
}
input[type="radio"]:not(checked) + label{
display: inline-block;
margin-left: 4px;
padding-left: 18px;
background: url("//www.shopunt.com/images/eng/btn/radio.png") no-repeat;
width:auto;
line-height: 12px;
}
input[type="radio"]:checked + label{
background: url("//www.shopunt.com/images/eng/btn/radio-r.png") no-repeat;
width:auto;
}nelson, 2014/11/21 下午 07:04:21
HTML:
CSS:
CSS:
display: inline-block;
width: 160px;
}
.bbb li div{
width: 150px;
padding: 5px;
background: #ececec;
overflow: hidden;
-webkit-border-radius: 50%;
-moz-border-radius: 50%;
-ms-border-radius: 50%;
-o-border-radius: 50%;
border-radius: 50%;
}
.bbb li img{
width: 150px;
-webkit-border-radius: 50%;
-moz-border-radius: 50%;
-ms-border-radius: 50%;
-o-border-radius: 50%;
border-radius: 50%;
}
Safari OUTPUT:
aaa:
bbb:
<ul class="aaa">
<li><img src="http://placehold.it/150x150" /></li>
</ul>
<ul class="bbb">
<li><div><img src="http://placehold.it/150x150" /></div></li>
</ul>
CSS:
.aaa li{
display: inline-block;
width: 160px;
}
.aaa li img{
width: 150px;
border: 5px solid #ececec;
-webkit-border-radius: 50%;
-moz-border-radius: 50%;
-ms-border-radius: 50%;
-o-border-radius: 50%;
border-radius: 50%;
}
CSS:
.bbb li{display: inline-block;
width: 160px;
}
.bbb li div{
width: 150px;
padding: 5px;
background: #ececec;
overflow: hidden;
-webkit-border-radius: 50%;
-moz-border-radius: 50%;
-ms-border-radius: 50%;
-o-border-radius: 50%;
border-radius: 50%;
}
.bbb li img{
width: 150px;
-webkit-border-radius: 50%;
-moz-border-radius: 50%;
-ms-border-radius: 50%;
-o-border-radius: 50%;
border-radius: 50%;
}
Safari OUTPUT:
aaa:
bbb:
nelson, 2014/11/10 下午 03:48:03
半夜被老闆 Line，跟我說網頁發生錯誤，他無法看統計報表，會出現作業逾時的錯誤 我查了一下網頁程式，發現是這筆SQL指令跑太久
Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date
From V_Order_main With(Nolock)
Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))
AND Create_Date >= '2014/10/3' And Create_Date < '2014/10/12'
--> 跑了50秒~ 1分鐘
Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date
From V_Order_main With(Nolock)
Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))
--> 拿掉日期限制 > 秒殺， 結果跑出 47 筆資料
跑一下執行計畫 原來子查詢的 Ad_Trace 沒有建立Index造成
加上 Index 之後，再跑第一支SQL --> 秒殺
奇怪的地方:
明明訂單日期欄位(Create_Date) 跟 Ad_Trace 沒有多大關係，但是加上日期限制之後，居然會引發 Ad_Trace 搜尋過久的現象
他好像是先依照訂單日期找出所有訂單，然後再從子查詢 Ad_Trace 裡面搜尋有無符合的條件訂單
而不是先跑子查詢找出 Ad_Trace 所有的 Order_Id，再去找符合 Create_Date 的訂單
SQL內部運作的機制真是有點讓人想不透，幸好有評估計畫可以看出問題在哪裡
這讓我想起以前學SQL時的一句話 => 盡量用Join來取代子查詢
使用子查詢真的是效能殺手啊
------繼續補充--------------------------------------
上面的SQL在雙11活動後，由於當日訂單過多，一樣造成SQL timeout 的現象，研判還是子查詢造成的問題
Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date
From V_Order_main With(Nolock)
Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14720))
And Create_Date > '2014-11-10' And Create_Date < '2014-11-11'
--> 跑了51秒
所以先將子查詢拿出來跑出一串字串 865828,865890,865901,865903,865928,865955,865990,865993,866005,866035.....
(共1452筆)
再把他拼入sql跑 --> 7秒
darren, 2014/10/11 上午 09:58:18
方法1：改HTML架構
<ul>
<li>one</li><li>two</li><li>three</li>
</ul>
或
<ul>
<li>
one</li><li>
two</li><li>
three</li>
</ul>
或
<ul>
<li>one</li
><li>two</li
><li>three</li>
</ul>
方法2：拿掉結尾標籤
<ul>
<li>one
<li>two
<li>three
</ul>
方法3：調整margin值
.nav li {
display: inline-block;
margin-right: -4px;
}
方法4：在父元素設定font-size為0
.nav {
font-size: 0;
}
.nav li {
font-size: 16px;
}
*此法對於某些android系統版本上無效。或設定如font-size: .001px，可解決大部分android版本問題
最好辦法是改成float:left
nelson, 2014/9/3 下午 03:13:38