---
# System prepended metadata

title: 大魯閣常用SQL指令

---

## 大魯閣常用SQL指令

1.大魯閣棒保會員共用，持有大魯閣應援/學生卡在保齡和棒壘都可以享有會員優惠。
2.持有多張卡別的顯示順序：Homereun > 應援(可能有多張，依卡號顯示) > 學生

資料庫：密碼taroko 

localhost (主機，自用) 

10.1.1.19   citrus會員(非即時，隔日早上6點前匯入)、taroko(POS即時消費資料) 

10.1.1.17   CHEIFPOS-TRK致富寶會員

* 致富寶POS使用期間：2020.07.01~2023.04.30
* Cirtus POS使用期間：2023.05.01~

注意事項：
1. 要測試資料欄位是否是你所需，可使用以下指令，避免資料跑太久。
```
select top 1000*
```
2. ==嚴禁在localhost以外的資料庫、用select以外的語法==，避免動到資料庫資料。 
3. 查詢結果可以直接全選複製到excel，並把格式調整成字串，手機號碼格式就不會跑掉了。 


---

### Cirtus 資料 10.1.1.19


#### citrus.dbo.cardTbl 會員基本資料(2024/04/01後未更新)
| 欄位名稱  | 欄位意義   | 說明                       | 備註                                             |
| --------- | ---------- | -------------------------- | ------------------------------------------------ |
| memid     | 思圖會員id | "memid":"ST14491223"       |                                                  |
| memberid  | 思圖會員id | "memberid":"ST14491223"    |                                                  |
| cardNo |會員卡號|"cardNo":"J190396057"|APP會員無卡號，以思圖id為主。J19開頭學生卡；A19開頭應援卡；A01開頭HOMERUN卡|
| ctype     | 卡別       | "ctype":"APP"              | APP、大魯閣應援卡、大魯閣學生卡、大魯閣HOMERUN卡 |
| activeYmd | 卡片啟用日 | "activeYmd":"20230709"     |                                                  |
| checkYmd  | 卡片到期日 | "checkYmd":"20991231"      |                                                  |
| uName     | 姓名       | "uName":"林琮凱"           |                                                  |
| uSex      | 性別       | "uSex":"X"                 | X：無法確認；F：女性；M：男性                    |
| birthday  | 生日       | "birthday":"20000101"      |                                                  |
| mobile    | 手機       | "mobile":"0981803774"      | 唯一值，經過雙重驗證                             |
| email     | Email      | "email":"123@yahoo.com.tw" | 未經過雙重驗證                                   |
| lastYmd   | 最後消費日 | "lastYmd":"20240101"       |                                                  |
| addr      | 居住地     | "addr":"高雄市"            | 城市名稱                                         |
| createYmd | 資料創建日 | "creatYmd":"20230905"      |                                                  |

#### taroko.dbo.customer (即時會員資料)


| 欄位名稱 | 欄位意義 | 說明    | 備註                                                            |
| -------- | -------- | --- | --------------------------------------------------------------- |
| CustNo  | 卡號 |"CustNo":"ST00021723" |思圖卡號，只要有APP即會賦予|
| Name | 姓名 |"Name":"王大明"     ||
| Birth | 生日 |"Birthday":"1984-11-09 00:00:00.000"     |非必填|
| Gender | 性別 |"Gender":"M"     |非必填。M男生；F女生；X未填|
| Mobile | 手機 |"Mobile":"0900000000"     |唯一值|
| Email | 信箱 |"Email":"taroko.sports@gmail.com"     |非必填|


#### citrus.dbo.cardCat 卡別名稱


| 欄位名稱 | 欄位意義 | 說明    | 備註                                                            |
| -------- | -------- | --- | --------------------------------------------------------------- |
| cardCat  | 卡別編號 |"cardCat":"A00302" |                      |
| cardName | 卡別名稱 |"cardName":"大魯閣應援卡"     | A0301：大魯閣HOMERUN卡；A00302:大魯閣應援卡；A0303:大魯閣學生卡 |


#### taroko.dbo.Orders 交易訂單


| 欄位名稱  | 欄位意義     | 說明                                  | 備註              |
| --------- | ------------ | ------------------------------------- | ----------------- |
| StoreID   | 店鋪編號     | "StoreID":"12103"                     |                   |
| OrderNO   | 交易訂單編號 | "OrderNO":"P012023010100001"          | 因為是流水號，訂單編號非唯一值，不同館會有同樣的編號                  |
| OrderTime | 交易時間     | "OrderTime":"2023-01-01 10:54:29.000" |                   |
| SPrice    | 交易金額     | "SPrice":"250"                        | 含稅金額          |
| IsDel     | 交易是否取消 | "IsDel":"Y"                           | Y：取消；N:未取消 |
| PayType   | 付款方式     | "PayType":"現金"                      |                   |
| CustNo|APP會員編號|"CustNo":"ST43353023"|思圖會員編號，只要有下載APP即會賦予一個|
| CardType| 會員卡別     | "CardType":"A0301"                             | A0301：大魯閣HOMERUN卡；A00302:大魯閣應援卡；A0303:大魯閣學生卡       |
| CardNo|會員編號| "CardNo":"ST03634424A0302"                    |ST卡號+A0302=有綁定數位付費會員卡；若只有A01、A19、J19開頭，代表尚未綁定|
| Qty       | 交易數量     | "Qty":"6"                             | 明細項目數量      |
| CName     | 姓名     | "CName":"王大明"                             |       |
| Tel       | 電話     | "Tel":"0900000000"                             | 唯一值    |


#### taroko.dbo.OrdersItem 交易明細

| 欄位名稱  | 欄位意義     | 說明                                  | 備註 |
| --------- | ------------ | ------------------------------------- | ---- |
| StoreID   | 店鋪編號     | "StoreID":"12103"                     |      |
| OrderNO   | 交易訂單編號 | "OrderNO":"P012023010100001"          |      |
| Oty  | 項目數量 |"Oty":"1"|      |
| SPrice    | 交易金額     | "SPrice":"250"|含稅金額|
| Gname|商品名稱|"Gname":"平日2H門票"|      |

#### taroko.dbo.Store 店鋪資料
| 欄位名稱  | 欄位意義     | 說明                                  | 備註 |
| --------- | ------------ | ------------------------------------- | ---- |
| StoreID   |店鋪編號| "StoreID":"12103"|      |
| StoreName |店鋪名稱 | "StoreName":"新時代館"          | 基創為12500~12599之間|

#### taroko.dbo.Goods 商品資料
| 欄位名稱   | 欄位意義     | 說明                                   | 備註                 |
| ---------- | ------------ | -------------------------------------- | -------------------- |
| GKID       |              |                                        |                      |
| GID        | 商品代號     | "GID":"010501000372"                   |                      |
| GName      | 商品名稱     | "GName":"學生代幣15枚"                 |                      |
| Price      | ?            |                                        | *看起來不是商品價錢* |
| IsVaild    | 商品是否有效 | "IsVaild":"Y"                          |                      |
| GType      | 商品類別?|                                        |*不知道意義*|
| UpdateDate | 更新日期     | "UpdateDate":"2023-04-06 14:16:44.630" ||

#### taroko.dbo.OrderPayway 訂單支付方式
| 欄位名稱 | 欄位意義     | 說明                         | 備註 |
| -------- | ------------ | ---------------------------- | ---- |
| StoreID  | 店鋪編號     | "StoreID":"12103"            |      |
|OrderNO          |交易訂單編號              |"OrderNO":"P012023010100001"                               |      |
| Paydesc  | 支付方式 | "Paydesc":"台新刷卡離線" |常用：現金、信用卡、電子支付、動滋券      |

#### taroko.dbo.InvoiceOrders 訂單發票
| 欄位名稱  | 欄位意義     | 說明                                  | 備註                                 |
| --------- | ------------ | ------------------------------------- | ------------------------------------ |
| StoreID   | 店鋪編號     | "StoreID":"12103"                     |                                      |
| INVWord   | 發票開頭     | "INVWord":"AK"                        |                                      |
| INVNo     | 發票號碼     | "INVNo":"28704041"                    |                                      |
| OrderNO   | 交易訂單編號 | "OrderNO":"P012023010100001"          |                                      |
| OrderTime | 交易時間     | "OrderTime":"2023-01-01 10:54:29.000" |                                      |
| Status    | 交易狀態     | "Status":"Y"                          | Y為正常交易，N為取消或換貨           |
| Payamt   | 交易金額     | "Payamt":"500"                       |                                      |
| DelReason | 取消原因     | "DelReason":"客戶換貨" |客戶取消、客戶抱怨、客戶換貨、退貨                                      |
| Paydesc   | 支付方式     | "Paydesc":"台新刷卡離線"              | 常用：現金、信用卡、電子支付、動滋券 |


---


### 撈取目前有效會員資料
目的：可針對未過期會員投遞簡訊。
```
SELECT c.mobile, s.StoreName, o.StoreID, o.OrderTime, o.CustNo, o.SPrice, o.IsDel, c.cardNo,c.uSex, c.ctype, c.uName,c.birthday,DATEDIFF(year,TRY_CAST(c.birthday AS DATE),GETDATE())AS agediff,c.activeYmd,c.checkYmd,c.lastYmd
FROM taroko.dbo.Orders o 
JOIN taroko.dbo.Store s ON o.StoreID=s.StoreID
JOIN citrus.dbo.cardTbl c ON o.CustNo=c.memberid
WHERE convert(varchar(10), o.OrderTime, 112 ) between '20230501' and '20240409' ###選取期間
   AND len(o.CustNo) !=0 and o.IsDel='N'
   AND o.StoreID between 12500 and 12599
   AND DATEDIFF(day,GETDATE(),TRY_CAST(c.checkYmd AS DATE)) >=0  ###篩選未過期會員
   AND s.StoreName = '中華館'  ###篩館別
```


### 撈取訂單資料
目的：撈取會員消費紀錄
```
SELECT DISTINCT c.mobile,o.OrderNO,o.OrderTime,o.SPrice,o.CardType,o.StoreID,s.StoreName,CC.cardName
FROM taroko.dbo.Orders o
JOIN citrus.dbo.cardTbl c on o.CustNo = c.memberid
JOIN taroko.dbo.Store s on o.StoreID = s.StoreID
JOIN citrus.dbo.cardCat CC on o.CardType = CC.cardCat
WHERE  convert(varchar(10), o.OrderTime, 112 ) between '20230601' and '20230630'
AND len(o.CustNo) !=0 and o.IsDel='N'
AND o.StoreID between 12500 and 12599
```

### 計算購買品項
目的：計算特定時段內，消費者購買某品項的總數量
```
SELECT Gname, SPrice, COUNT(Gname) AS TotalCount, SUM(SPrice) AS TotalSum
FROM taroko.dbo.OrdersItem
WHERE CONVERT(varchar(10), CreateDate, 112) BETWEEN '20230801' AND '20230831'
    AND IsDel = 'N'
    AND StoreID BETWEEN 12500 AND 12599
GROUP BY Gname, SPrice;
```

```
SELECT 
    oi.Gname,
    COUNT(*) AS SaleCount
FROM 
    taroko.dbo.Orders o
JOIN 
    taroko.dbo.OrdersItem oi ON o.StoreID = oi.StoreID AND o.OrderNO = oi.OrderNO
WHERE 
    oi.Gname IN ('113年年中慶套票3組', '113年年中慶套票2組', '巨城-113年年中慶套票3組', '113年年中慶電子套票3組', '113年年中慶電子套票2組', '會員30枚', '會員80枚', '學生代幣15枚','學生代幣15枚(虛擬代幣)','會員30枚(虛擬代幣)')
    AND o.CardType = 'A0303'
	AND CONVERT(varchar(10),OrderTime,112) between '20240701' AND '20240722'
	AND o.IsDel = 'N'
GROUP BY 
    oi.Gname;
```

### 計算平均客單價
```
SELECT 
    AVG(SPrice) AS avgprice
FROM 
    taroko.dbo.Orders o
WHERE 
    o.CardType = 'A0303'
	AND CONVERT(varchar(10),OrderTime,112) between '20240701' AND '20240722'
	AND o.IsDel = 'N'
	AND SPrice != 0
```

### 青春動滋券使用會員
目的：撈取使用會員名單、卡別、金額
```
SELECT StoreName,OrderTime,SPrice,CardType,c.Name,c.Mobile
FROM taroko.dbo.Orders o
JOIN taroko.dbo.Store s ON s.StoreID=o.StoreID
JOIN taroko.dbo.Customer c ON c.Mobile=o.Tel
JOIN taroko.dbo.OrdersPayway op ON op.StoreID=o.StoreID AND op.OrderNO = o.OrderNO
WHERE o.StoreID between 12500 and 12599
AND CONVERT(varchar(10),OrderTime,112) between '20240101' and '20240714'
AND o.IsDel = 'N'
AND op.Paydesc = '動滋券'
AND CardType IN ('A0301','A0302','A0303')
```

### 滿額發票數量
目的：mapping消費滿額會員名單，做簡訊提醒用
```
SELECT *
FROM taroko.dbo.InvoiceOrders io
JOIN taroko.dbo.Orders o ON o.OrderNO = io.OrderNO AND o.StoreID = io.StoreID
WHERE io.StoreID between 12500 and 12599
AND io.Status ='Y'
AND Payamt >= 500 #輸發票金額
AND  CONVERT(varchar(10),io.OrderTime,112) between '20240701' and '20240820' #發票日期區間
AND o.CardType in ('A0301','A0302','A0303'); #卡別
```
