IT工作備忘錄

Function Num2Str(Str As String) As String
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
Select Case True
Case a = 0
aa = aa & “　零”
Case a = 1
aa = aa & “　壹”
Case a = 2
aa = aa & “　貳”
Case a = 3
aa = aa & “　參”
Case a = 4
aa = aa & “　肆”
Case a = 5
aa = aa & “　伍”
Case a = 6
aa = aa & “　陸”
Case a = 7
aa = aa & “　柒”
Case a = 8
aa = aa & “　捌”
Case a = 9
aa = aa & “　玖”
Case Else
aa = aa & a
End Select
Next i
aa = Right(“零　零　零　零　零　零　零　零　零” & aa, 18)
bb = Left(aa, 6) & ” ” & Mid(aa, 7, 2) & ” ” & Mid(aa, 9, 2) & ” ” & Mid(aa, 11, 2) & Right(aa, 6)
Num2Str = bb
End Function

To delete the duplicate rows from the table in SQL Server, you follow these steps:

• Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
• Use DELETE statement to remove the duplicate rows.

Let’s set up a sample table for the demonstration.

Setting up a sample table

First, create a new table named sales.contacts as follows:

DROP TABLE IF EXISTS sales.contacts;

CREATE TABLE sales.contacts(
    contact_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) NOT NULL,
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the sales.contacts table:

INSERT INTO sales.contacts
    (first_name,last_name,email) 
VALUES
    ('Syed','Abbas','syed.abbas@example.com'),
    ('Catherine','Abel','catherine.abel@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Pilar','Ackerman','pilar.ackerman@example.com');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sales.contacts table:

SELECT 
   contact_id, 
   first_name, 
   last_name, 
   email
FROM 
   sales.contacts;Code language: SQL (Structured Query Language) (sql)

The following picture shows the output of the query:

There are many duplicate rows (3,4,5), (6,7), and (8,9) for the contacts that have the same first name, last name, and email.

Delete duplicate rows from a table example

The following statement uses a common table expression (CTE) to delete duplicate rows:

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;
Code language: SQL (Structured Query Language) (sql)

In this statement:

• First, the CTE uses the ROW_NUMBER() function to find the duplicate rows specified by values in the first_name, last_name, and email columns.
• Then, the DELETE statement deletes all the duplicate rows but keeps only one occurrence of each duplicate group.

SQL Server issued the following message indicating that the duplicate rows have been removed.

(4 rows affected)

If you query data from the sales.contacts table again, you will find that all duplicate rows are deleted.

SELECT contact_id, 
       first_name, 
       last_name, 
       email
FROM sales.contacts
ORDER BY first_name, 
         last_name, 
         email;Code language: SQL (Structured Query Language) (sql)

https://www.sqlservertutorial.net/sql-server-basics/delete-duplicates-sql-server/

1.在連到網路的頁面，不要連上網路，也把實體網路線拔除，這時候按下鍵盤上的「Shift + F10」就可以開啟 CMD 視窗，如果按下去沒有反應，請試試看「fn + Shift + F10」組合，應該就可以順利呼叫開啟 CMD。

2.開啟之後輸入「oobe\bypassnro」，並按下 Enter 鍵。之後系統會自動重新啟動，重開機後會再進入 OOBE 畫面一次，設定都會重新來過，請依照自己需求設定。

4.到連接網路畫面時，就可以看到「我沒有網際網路」的選項，點選後就可以跳過連網！

–bom2要被減少的階層 此處用量皆為重量，算出總重量，屆時可依比例更新用量回主BOM，即bom1

update Cf_inv_bom_2
set Cf_inv_bom_2.sum_qty = t1.c_sum
from (select assy_no,sum(qty) c_sum from Cf_inv_bom_2 group by assy_no) t1
where Cf_inv_bom_2.assy_no = t1.assy_no

–bom1無下階先抓出來

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )

–再結合有bom1有下階的資料(用量=>被替換子件原用量*(下階子件用量/下階用量之和))

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no order by 1,2

–由於合併後發現兩階間有共用子件因此多一層group來加總資件用量

select Tu.assy_no,tu.item_no,sum(tu.qty),max(Tu.sum_qty) from
(select [assy_no]
,[item_no]
,[qty]
,[sum_qty] from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no) as Tu
group by Tu.assy_no,tu.item_no
order by 1,2

Sub LoopThroughRowsByRefBom()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
FirstRow = 1
i = FirstRow
FirstColumn = 1
Do Until i > LastRow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    j = FirstColumn
    Do Until j > LastColumn
        If i Mod 15 = 1 And j Mod 3 = 1 Then
            For k = 0 To 10
               Cells(i + k, j + 2).Value = Cells(i, j)
            Next k
        End If
        j = j + 3
    Loop
    i = i + 15
Loop

 Dim dynArray(1 To 5000, 1 To 4) As String
 i = 1
  k = 1
 Do Until i > LastRow
     LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    j = 1
    Do Until j > LastColumn
        If j Mod 3 = 1 And i Mod 15 <= 10 And Not IsEmpty(Cells(i, j + 1).Value) And Cells(i, j + 1).Value > 0 Then
            dynArray(k, 1) = Cells(i, j + 2)
            dynArray(k, 2) = Cells((i \ 15) * 15 + 12, j + 1)
            dynArray(k, 3) = Cells(i, j)
            dynArray(k, 4) = Cells(i, j + 1)
             k = k + 1
        End If
        j = j + 3
    Loop
  i = i + 1
Loop

Dim De1 As Range
Set De1 = Range("AK1")
De1.Resize(UBound(dynArray, 1), UBound(dynArray, 2)).Value = dynArray
End Sub