|
|
|
| 作者：未知 |
| 日期：2005-04-29 |
| 人氣： |
| 投稿：(轉貼) |
| 來源：未知 |
| 字體：大 中 小 |
| 收藏：加入瀏覽器收藏 |
|
|
|
/*--示例
--出處:鄒建
在代碼中，首先定義了一個最簡單的出入庫數據記錄明細表（tb），用來記錄每筆出入庫的交易情況。明細帳查詢要求得到每種Item每天的期初數量、當天進貨數、進貨退回數、出貨數、出貨退回數及當天結餘數。--*/
--明細帳數據CREATE TABLE tb(ID int IDENTITY PRIMARY KEY,Item varchar(10), --產品編號Quantity int, --交易數量Flag bit, --交易標誌,1代表入庫,0代表出庫,這樣可以有效區分退貨(負數)Date datetime) --交易日期INSERT tb SELECT 'aa',100,1,'2005-1-1'UNION ALL SELECT 'aa',90 ,1,'2005-2-1'UNION ALL SELECT 'aa',55 ,0,'2005-2-1'UNION ALL SELECT 'aa',-10,1,'2005-2-2'UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'UNION ALL SELECT 'aa',200,1,'2005-2-2'UNION ALL SELECT 'aa',90 ,1,'2005-2-1'UNION ALL SELECT 'bb',95 ,1,'2005-2-2'UNION ALL SELECT 'bb',65 ,0,'2005-2-3'UNION ALL SELECT 'bb',-15,1,'2005-2-5'UNION ALL SELECT 'bb',-20,0,'2005-2-5'UNION ALL SELECT 'bb',100,1,'2005-2-7'UNION ALL SELECT 'cc',100,1,'2005-1-7'GO
--select * from TB
--結果
ID Item Quantity Flag Date ----------- ---------- ----------- ---- ------------------------------------------------------ 1 aa 100 1 2005-01-01 00:00:00.0002 aa 90 1 2005-02-01 00:00:00.0003 aa 55 0 2005-02-01 00:00:00.0004 aa -10 1 2005-02-02 00:00:00.0005 aa -5 0 2005-02-03 00:00:00.0006 aa 200 1 2005-02-02 00:00:00.0007 aa 90 1 2005-02-01 00:00:00.0008 bb 95 1 2005-02-02 00:00:00.0009 bb 65 0 2005-02-03 00:00:00.00010 bb -15 1 2005-02-05 00:00:00.00011 bb -20 0 2005-02-05 00:00:00.00012 bb 100 1 2005-02-07 00:00:00.00013 cc 100 1 2005-01-07 00:00:00.000
--查詢時間段定義DECLARE @dt1 datetime,@dt2 datetimeSELECT @dt1='2005-2-1',@dt2='2005-2-10'
--查詢--統計時間段內無發生額的數據(如果這個不是查詢需要的,去掉這段查詢)SELECT Item, Date=CONVERT(char(10),@dt1,120), Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END), [IN]=0, [IN_Retrun]=0, [OUT]=0, [OUT_Return]=0, Balance=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)FROM tb aWHERE Date<@dt1 AND NOT EXISTS( SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))GROUP BY ItemUNION ALL--指定時間段內有交易發生的數據SELECT Item, Date=CONVERT(char(10),Date,120), Opening=ISNULL((SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END) FROM tb WHERE Item=a.Item AND Date<MIN(a.Date)),0), [IN]=ISNULL(SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),0), [IN_Retrun]=ISNULL(SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),0), [OUT]=ISNULL(SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),0), [OUT_Return]=ISNULL(SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),0), Balance=ISNULL((SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END) FROM tb WHERE Item=a.Item AND Date<=MAX(a.Date)),0)FROM tb aWHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)GROUP BY CONVERT(char(10),Date,120),ItemORDER BY Item,Date
--結果
Item Date Opening IN IN_Retrun OUT OUT_Return Balance ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- aa 2005-02-01 100 180 0 55 0 225aa 2005-02-02 225 200 10 0 0 415aa 2005-02-03 415 0 0 0 5 420bb 2005-02-02 0 95 0 0 0 95bb 2005-02-03 95 0 0 65 0 30bb 2005-02-05 30 0 15 0 20 35bb 2005-02-07 35 100 0 0 0 135cc 2005-02-01 100 0 0 0 0 100
（所影響的行數為 8 行）
|
|
|
當前位置：開發者網絡 >> 技術教程 >> 數據庫專欄 >> 其他相關 >> 內容
