最近在練習透過C#取得access的資料，資料量沒有很大(約1000筆)，但搜尋的時候大概會卡個4~5秒左右，還請協助
查詢語句:
//尋找人員序號的最小漏號
string sql2 = "Select Min(序號+1) As '最小漏號' From 客戶資料庫 " +
"Where(序號 + 1) Not In(Select 序號 From 客戶資料庫 where 公司='" + tb_company.Text + "')";
//尋找公司代碼的最小漏號
string sql3 = "Select Min(代碼+1) As '最小漏號' From (SELECT distinct right(代碼,3)+0 as 代碼 from 客戶資料庫 where 代碼<>'') " +
"Where (代碼 + 1) Not In(SELECT right(代碼,3)+0 as 代碼 from 客戶資料庫 where 代碼<>'')";
一開始想說建立一個新線程讓查詢在背景執行，但一樣會卡住
也有試過產生一個等待視窗，等到查詢完成再讓視窗關閉，但視窗產生時內容會變成空白
//顯示等待視窗
w = new Watting();
w.Show();
//另開線程處理查詢
thread8 = new Thread(new ThreadStart(check));
thread8.Start();
附上資料表內容
CREATE TABLE 客戶資料庫(
代碼 text
, 公司 text
, 序號 text
, 人員 text
);
INSERT INTO
客戶資料庫(代碼,公司,序號,人員)
VALUES
('A0001', 'Tsla','1','p1')
, ('A0001', 'Tsla','2','p2')
, ('A0001', 'Tsla','4','p4')
, ('A0002', 'TSMC','1','p1')
, ('A0003', 'Google','1','p1')
, ('A0005', 'Apple','1','p1')
希望得到兩個數值
1.某公司的最小遺失序號，如tsla 返回3
2.整個資料表的最小遺失代碼，此例返回4
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE TestX(
X1 varchar(20) --代碼
, X2 varchar(20) --公司
, X3 varchar(20) --序號
, X4 varchar(20) --人員
);
INSERT INTO
TestX(X1,X2,X3,X4)
VALUES
('A0001', 'Tsla','1','p1')
, ('A0001', 'Tsla','2','p2')
, ('A0001', 'Tsla','4','p4')
, ('A0002', 'TSMC','1','p1')
, ('A0003', 'Google','1','p1')
, ('A0005', 'Apple','1','p1')
-- case 1
SELECT Top 1 X2,X3,Num as NumLose
FROM (
SELECT AA.X2,AA.X3,COUNT(1) AS Num
FROM TestX AS AA
INNER JOIN TestX AS BB ON BB.X2 = AA.X2 AND BB.X3 <= AA.X3
GROUP BY AA.X2,AA.X3
) AS TEMP
WHERE X2='Tsla'
AND X3<>CONVERT(VARCHAR,Num)
ORDER BY X2,Num
case 1 for access 要改成 :
AND X3<>CStr(Num)
-- case 2
SELECT Top 1 X1,Code as CodeLose
FROM (
SELECT AA.X1,'A'+RIGHT(CONVERT(VARCHAR,10000+COUNT(1)),4) AS Code
FROM (
SELECT DISTINCT X1
FROM TestX) AS AA
INNER JOIN (SELECT DISTINCT X1
FROM TestX) AS BB ON BB.X1 <= AA.X1
GROUP BY AA.X1
) AS TEMP
WHERE X1<>Code
ORDER BY X1,Code
case 2 for access 要改成 :
SELECT AA.X1,'A'+RIGHT(CStr(10000+COUNT(1)),4) AS Code
access sql :
case 1 :
SELECT Top 1 公司,序號,Num as 最小漏號
FROM (
SELECT AA.公司,AA.序號,COUNT(1) AS Num
FROM 客戶資料庫 AS AA
INNER JOIN 客戶資料庫 AS BB ON BB.公司 = AA.公司 AND BB.序號 <= AA.序號
GROUP BY AA.公司,AA.序號
) AS TEMP
WHERE 公司='Tsla'
AND 序號<>CStr(Num)
ORDER BY 公司,Num
case 2 :
SELECT Top 1 代碼,Code as 最小漏號
FROM (
SELECT AA.代碼,'A'+RIGHT(CStr(10000+COUNT(1)),4) AS Code
FROM (
SELECT DISTINCT 代碼
FROM 客戶資料庫) AS AA
INNER JOIN (SELECT DISTINCT 代碼
FROM 客戶資料庫) AS BB ON BB.代碼 <= AA.代碼
GROUP BY AA.代碼
) AS TEMP
WHERE 代碼<>Code
ORDER BY 代碼,Code
感謝回答，剛試了一下 access似乎不支援Temp，會報錯
我這邊測試是正確的,
access 是哪一個版本?
有完整的錯誤訊息嗎?
error:FROM 子句中的語法錯誤
access版本:access2000
case2如果把as temp去掉可以正常執行
case1會錯誤
可以貼出你的 case1 跟 case2 嗎?
//case1
string sql2 = "SELECT Top 1 公司,序號,Num as NumLose FROM(" +"SELECT AA.公司, AA.序號, COUNT(1) AS Num FROM 客戶資料庫 AS AA " +
"INNER JOIN 客戶資料庫 AS BB ON BB.公司 = AA.公司 AND BB.序號 <= AA.序號 " +
"GROUP BY AA.公司, AA.序號) " +
"WHERE 公司 = '" + tb_company.Text + "' AND 序號<>CStr(Num) ORDER BY 公司, Num";
//case2
string sql3 = "SELECT Top 1 代碼,Code as CodeLose FROM(" +"SELECT AA.代碼,RIGHT(CStr(10000+COUNT(1)),4) AS Code" +
" FROM(SELECT DISTINCT 代碼 FROM 客戶資料庫) AS AA" +
" INNER JOIN(SELECT DISTINCT 代碼" +
" FROM 客戶資料庫) AS BB ON BB.代碼 <= AA.代碼" +
" GROUP BY AA.代碼) " +
"WHERE 代碼<>Code ORDER BY 代碼, Code";
access sql case1 跟 case2 我已轉成你的資料表了,也測過了,
你直接貼到你的 sql2 跟 sql3 測看看吧
可以正常運行了 感謝
另外我問題描述可能有誤，假設資料表為:
DEMO
當公司=Tsla時，我希望得到的序號為2(此公司第幾個人)
若公司不在資料表內，則序號=A0002
只能猜你要的結果 , 可能有三種狀況 :
A.公司數量=0 時
=> 要填入的代碼=A0001 , 序號=1
B.公司數量>0 時
=>
1.公司不存在時 : 要填入的代碼=最大代碼 +1 號 , 序號=1
2.公司存在時 : 要填入的代碼不變,序號=序號+1
如果是這樣的話,建議把資料結構改成 :
1.表頭(客戶資料庫):
CREATE TABLE TestX(
X1 varchar(20) --代碼
, X2 varchar(20) --公司
);
2.表身(業務人員):
CREATE TABLE TestY(
Y1 varchar(20) --代碼
, Y2 varchar(20) --序號
, Y3 varchar(20) --人員
);
索引建立了嗎? 例如公司、代碼。
I code so I am小弟剛接觸資料庫這一塊，可以提供一些方向嗎
索引會使查詢快上好幾倍，通常針對where欄位建立索引。可參閱：
https://support.microsoft.com/en-us/office/create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce
等等來學習 謝謝
iThome鐵人賽
看影片追技術