SQL进阶提升（试题）学习sql server2005 step by step(九)

问题一

--问题描述

--第一是.关于加班时 ?的问 ?

-- 公司规 ©w一周内 ³Ì多只能加班和个而 ¤é期¥|A001 5

--周五A001 7

------------------

-- 共计 ¥[班：H，超出H

--从¨Ì次Ñ减的H),一轮 ¦Z如果还 ¤£够 ¡A再继 ?下一轮 ¡A直到刚 ¦n为为止(只对 ¶W过的而言)

--最后的结 ªG应 ?是：

--日期工号加班小时

--周一A001 3 --减了次

--周二A001 1 --减了次

--周三A001 1 --减了次

--周四A001 2 --减了次

--周五A001 5 --减了次

-------------------------

--以上只列具体某一个 ¤H的，公司有好几千人，

--用SQL语句实 ?，考虑 ¨ì效率问 ?规 ©w：

--1.不能用游标

--2.不能用循环

--其它方法都可以

代码

  1 DECLARE @T TABLE (日期VARCHAR(4),工号 VARCHAR(4),加班小时 INT)
  2 
  3  INSERT INTO @T
  4 
  5  SELECT '周一','A001',6 UNION ALL
  6 
  7  SELECT '周二','A001',2 UNION ALL
  8 
  9  SELECT '周三','A001',1 UNION ALL
 10 
 11  SELECT '周四','A001',5 UNION ALL
 12 
 13  SELECT '周五','A001',7
 14 
 15  
 16 
 17  --SQL查询 ¦p下:
 18 
 19  
 20 
 21 ;WITH LiangLoveLan AS
 22 
 23 (
 24 
 25     SELECT *
 26 
 27     FROM (
 28 
 29          SELECT 日期,工号 ,加班小时 FROM @T 
 30 
 31          UNION ALL
 32 
 33          SELECT '总 ?',工号 ,SUM(加班小时 ) FROM @T GROUP BY 工号     ) AS T 
 34 
 35        PIVOT(MAX(加班小时 ) FOR 日期IN([周一],[周二],[周三],[周四],[周五],[总数])) AS pvt
 36 
 37 ),
 38 
 39 LiangLoveLan2 AS
 40 
 41 (
 42 
 43     SELECT *,number = 1
 44 
 45     FROM LiangLoveLan
 46 
 47     UNION ALL
 48 
 49     SELECT
 50 
 51         工号 ,
 52 
 53         周一=CASE WHEN 周一>1 AND number = 1  
 54 
 55                    THEN 周一-1 
 56 
 57              ELSE 周一END,
 58 
 59         周二=CASE WHEN 周二>1 AND number = 2  
 60 
 61                    THEN 周二-1 
 62 
 63              ELSE 周二END,
 64 
 65         周三=CASE WHEN 周三>1 AND number = 3  
 66 
 67                    THEN 周三-1 
 68 
 69              ELSE 周三END,
 70 
 71         周四=CASE WHEN 周四>1 AND number = 4  
 72 
 73                    THEN 周四-1 
 74 
 75              ELSE 周四END,
 76 
 77         周五=CASE WHEN 周五>1 AND number = 5  
 78 
 79                    THEN 周五-1 
 80 
 81              ELSE 周五END,
 82 
 83         总数=CASE WHEN number=1 AND 周一>1 THEN 总 ?-1
 84 
 85                   WHEN number=2 AND 周二>1 THEN 总 ?-1
 86 
 87                   WHEN number=3 AND 周三>1 THEN 总 ?-1
 88 
 89                   WHEN number=4 AND 周四>1 THEN 总 ?-1
 90 
 91                   WHEN number=5 AND 周五>1 THEN 总 ?-1
 92 
 93                   ELSE 总 ?
 94 
 95              END,
 96 
 97         number=CASE WHEN number=1 THEN 2 
 98 
 99                     WHEN number=2 THEN 3
100 
101                     WHEN number=3 THEN 4
102 
103                     WHEN number=4 THEN 5
104 
105                     WHEN number=5 THEN 1
106 
107                 END
108 
109     FROM LiangLoveLan2
110 
111     WHERE 总 ?>12
112 
113 )
114 
115 SELECT 
116 
117     CAST(日期AS VARCHAR(20)) AS 日期,
118 
119     工号 ,
120 
121     加班小时 FROM (
122 
123     SELECT 工号 ,周一,周二,周三,周四,周五
124 
125     FROM LiangLoveLan2
126 
127     WHERE 总 ?=12
128 
129 ) AS LiangLoveLan3
130 
131     UNPIVOT(加班小时 FOR 日期IN([周一],[周二],[周三],[周四],[周五])) AS unpvt
132 
133 ORDER BY 工号 ;
134 
135

日期工号加班小时 -------------------- ---- -----------

周一 A001 3

周二 A001 1

周三 A001 1

周四 A001 2

周五 A001 5

(5 row(s) affected)

问题二

把销售表和结存表汇总成一个表
现在要把月份加进去统计，请问要怎么写？

销售表:
日期单据号码客户名称送货开票
2006-01-2 送630 亨通 6
2006-01-12 送631 亨通 4
2006-01-16 送639 金田电工 6
2006-01-20 开237 云通电子 2
2006-01-21 开238 金田电工 4
2006-01-24 送642 长城高新 10
2006-02-2 送630 亨通 6
2006-02-12 送631 亨通 4
2006-02-16 送639 金田电工 6
2006-02-20 开237 云通电子 1
2006-02-21 开238 金田电工 4
2006-02-24 送642 长城高新 10

结存表:
客户名称月份结存
亨通 2005-12 4
云通电子 2005-12 3
金田电工 2005-12 8
长城高新 2005-12 1
亨通 2006-01 14
云通电子 2006-01 1
金田电工 2006-01 10
长城高新 2006-01 11

现在要得到每个客户的销售汇总表(本期结存=上期结存+送货-开票)
日期单据号码客户名称送货开票结存
上期结存亨通 4
2006-01-2 送630 亨通 6
2006-01-12 送631 亨通 4
本期结存亨通 14
上期结存金田电工 8
2006-01-16 送639 金田电工 6
2006-01-21 开238 金田电工 4
本期结存金田电工 10
上期结存云通电子 3
2006-01-20 开237 云通电子 2
本期结存云通电子 1
上期结存长城高新 1
2006-01-24 送642 长城高新 10
本期结存长城高新 11
200601合计 26 6 28
上期结存亨通 14
2006-01-2 送630 亨通 6
2006-01-12 送631 亨通 4
本期结存亨通 24
上期结存金田电工 10
2006-01-16 送639 金田电工 6
2006-01-21 开238 金田电工 4
本期结存金田电工 12
上期结存云通电子 1
2006-01-20 开237 云通电子 1
本期结存云通电子 0
上期结存长城高新 11
2006-01-24 送642 长城高新 10
本期结存长城高新 21
200602合计 26 5 57

--答案

代码

 1 create   table   sale(rq   datetime,num   varchar(20),customer   varchar(30),send   int,code   int)   
 2   insert   into   sale   
 3   select   '2006-01-02','送630','亨通',6,null   union   all     
 4   select   '2006-01-12','送631','亨通',4,null   union   all     
 5   select   '2006-01-16','送639','金田電子',6,null   union   all     
 6   select   '2006-01-20','開237','雲通電子',null,2   union   all     
 7   select   '2006-01-21','開238','金田電子',null,4   union   all     
 8   select   '2006-01-24','送642','長城高新',10,null   union   all   
 9   select   '2006-02-02','送630','亨通',6,null   union   all     
10   select   '2006-02-12','送631','亨通',4,null   union   all     
11   select   '2006-02-16','送639','金田電子',6,null   union   all     
12   select   '2006-02-20','開237','雲通電子',null,1   union   all     
13   select   '2006-02-21','開238','金田電子',null,4   union   all     
14   select   '2006-02-24','送642','長城高新',10,null   
15     
16   create   table   locate(customer   varchar(30),monthly   varchar(07),value   int)   
17   insert   into   locate     
18   select   '亨通','2005-12',4   union   all   
19   select   '雲通電子','2005-12',3   union   all   
20   select   '金田電子','2005-12',8   union   all   
21   select   '長城高新','2005-12',1   union   all   
22     
23   select   '亨通','2006-01',14   union   all   
24   select   '雲通電子','2006-01',1   union   all   
25   select   '金田電子','2006-01',10   union   all   
26   select   '長城高新','2006-01',11   
27     
28   select     case   when   num='上期結存'     or   num='本期結存'     or   (num   like   '%合計'   and   len(num)=9)   then   null   else   rq   end   as   rq,   
29                   num,customer,send,code,結存     
30     from     
31   (select   convert(varchar(10),rq,120)   as   rq,num,customer,send,code,null   as   [結存]   from   sale   
32   union   all   
33   select   convert(varchar(07),dateadd(mm,1,monthly+'-01'),120),'上期結存',customer,null,null,value   from   locate     
34   union   all   
35   select     convert(char(07),rq,120)   as   rq,'本期結存',customer,null,null,sum(isnull(結存,0))+sum(isnull(send,0))-sum(isnull(code,0))     
36   from     
37   (select   *,null   as   [結存]   from   sale   
38   union   all   
39   select   dateadd(mm,1,monthly+'-01'),'上期結存',customer,null,null,value   from   locate)   A   
40   group   by   customer,convert(char(07),rq,120)   
41   union   all   
42     
43   select   A1.rq,A1.rq+'合計',null,send,code,結存   
44   from     
45   (select   convert(char(07),rq,120)   as   rq,   sum(send)   as   send,sum(code)   as   code   
46   from   sale   
47   group   by   convert(char(07),rq,120))   A1   
48   join     
49   (select   rq,sum(結存)   as   [結存]   
50   from   (   
51   select     convert(char(07),rq,120)   as   rq,'本期結存'   as   num   ,customer,null   as   send,null   as   code,sum(isnull(結存,0))+sum(isnull(send,0))-sum(isnull(code,0))     as   [結存]   
52   from     
53   (select   *,null   as   [結存]   from   sale   
54   union   all   
55   select   dateadd(mm,1,monthly+'-01'),'上期結存',customer,null,null,value   from   locate)   A   
56   group   by   customer,convert(char(07),rq,120)   
57   )     T   
58   group   by   rq)   A2   
59   on   A1.rq=A2.rq   
60     
61   )   T   
62     
63   order   by     left(rq,7),     customer   desc   ,case   when   num='上期結存'   then   1     
64                                                                               when   num='本期結存'   then   3   
65                                                                               else   2   end,   rq   
66     
67   drop   table   sale,locate   
68

/*結果*/
rq num customer send code 結存
---------- -------------------- ------------------------------ ----------- ----------- -----------
NULL 上期結存雲通電子 NULL NULL 3
2006-01-20 開237 雲通電子 NULL 2 NULL
NULL 本期結存雲通電子 NULL NULL 1
NULL 上期結存長城高新 NULL NULL 1
2006-01-24 送642 長城高新 10 NULL NULL
NULL 本期結存長城高新 NULL NULL 11
NULL 上期結存金田電子 NULL NULL 8
2006-01-16 送639 金田電子 6 NULL NULL
2006-01-21 開238 金田電子 NULL 4 NULL
NULL 本期結存金田電子 NULL NULL 10
NULL 上期結存亨通 NULL NULL 4
2006-01-02 送630 亨通 6 NULL NULL
2006-01-12 送631 亨通 4 NULL NULL
NULL 本期結存亨通 NULL NULL 14
NULL 2006-01合計 NULL 26 6 36
NULL 上期結存雲通電子 NULL NULL 1
2006-02-20 開237 雲通電子 NULL 1 NULL
NULL 本期結存雲通電子 NULL NULL 0
NULL 上期結存長城高新 NULL NULL 11
2006-02-24 送642 長城高新 10 NULL NULL
NULL 本期結存長城高新 NULL NULL 21
NULL 上期結存金田電子 NULL NULL 10
2006-02-16 送639 金田電子 6 NULL NULL
2006-02-21 開238 金田電子 NULL 4 NULL
NULL 本期結存金田電子 NULL NULL 12
NULL 上期結存亨通 NULL NULL 14
2006-02-02 送630 亨通 6 NULL NULL
2006-02-12 送631 亨通 4 NULL NULL
NULL 本期結存亨通 NULL NULL 24
NULL 2006-02合計 NULL 26 5 57

--问题三

产品的高规低代

假如你（客户）去买电脑，需要的内存是1G（假如预算就到这了，再贵买不起，只能放弃或去别的店里咨询），而店里正好没有现货，却有2G的内存现货（如果此2G的也是压仓库，卖不出去），那么店主（生产商）也许就会说，1G的没有了，我给你2G的吧，价格算1G的价。。。一举两得，店主减少了库存压货，并卖出了电脑赚了钱，而你也白捡了便宜。（这在生产上，叫作高规低代）

简单流程表示为

客户下单(1G电脑) => 资材人员发现1G内存缺料，并同时发现库存有2G的，两者可替代 => 客服人员联络客户商量是否可高规低代？ => 客户同意 => 组装2G的（修改manufacture bom） / 如果客户不同意的话，就得让采购去买料了。。。

在这里有一个很重要的步骤是 “资材人员发现1G内存缺料，并同时发现库存有2G的，两者可替代” ，这就引出了，很多时候，管理人员需要一份report ，

可以明确的知道一颗原材所对应的所有成品（或者说一个成品料号所对应的所有原材。我把它叫作 part where use .

在这样的一份report里，将跳过众多的半成品，直接得出最高介和最低介的关系，是一种part direct relation. 这将非常有利于管理人员掌控物料规划，仓库水位等。

怎么从普通的BOM表，得出这样的一个 part direct的结构?

下面以程式作简单说明：

用途： part where use, 原材的用料累计等，都可以用此方法来计算.

代码

  1 Create table bom
  2 
  3  
  4 
  5   (parent varchar(2) not null, 
  6 
  7  
  8 
  9    child varchar(2) not null
 10 
 11  
 12 
 13    )
 14 
 15  
 16 
 17  
 18 
 19  
 20 
 21 Insert into bom select 'A0','A1'
 22 
 23  
 24 
 25 Insert into bom select 'A0','A2'
 26 
 27  
 28 
 29 Insert into bom select 'A1','A3'
 30 
 31  
 32 
 33 Insert into bom select 'A1','A4'
 34 
 35  
 36 
 37 Insert into bom select 'A3','A5'
 38 
 39  
 40 
 41  
 42 
 43  
 44 
 45 Insert into bom select 'B0','B1'
 46 
 47  
 48 
 49 Insert into bom select 'B1','B2'
 50 
 51  
 52 
 53 Insert into bom select 'B1','B3'
 54 
 55  
 56 
 57 Insert into bom select 'B3','B4'
 58 
 59  
 60 
 61 Insert into bom select 'B3','B5'
 62 
 63  
 64 
 65  
 66 
 67  
 68 
 69 Insert into bom select 'A4','C1'
 70 
 71  
 72 
 73 Insert into bom select 'B4','C1'
 74 
 75  
 76 
 77  
 78 
 79  
 80 
 81 Go
 82 
 83  
 84 
 85 Create Proc usp_test
 86 
 87  
 88 
 89 AS
 90 
 91  
 92 
 93  
 94 
 95  
 96 
 97 declare @loop int
 98 
 99  
100 
101 set @loop=1
102 
103  
104 
105  
106 
107  
108 
109 --查找出bom表里所有最高介
110 
111  
112 
113 select  parent as top_part,child 
114 
115  
116 
117 into #top 
118 
119  
120 
121 from bom as A
122 
123  
124 
125 where not exists(select 1 from bom where child=A.parent) 
126 
127  
128 
129  
130 
131  
132 
133 While @loop>0
134 
135  
136 
137 BEGIN
138 
139  
140 
141   select  B.top_part, A.parent, A.child
142 
143  
144 
145   into #tmp_top
146 
147  
148 
149   from bom A, #top B
150 
151  
152 
153   where A.parent=B.child  
154 
155  
156 
157  
158 
159  
160 
161   Delete #top 
162 
163  
164 
165   from #top , #tmp_top
166 
167  
168 
169   where #top.child=#tmp_top.parent
170 
171  
172 
173  
174 
175  
176 
177   set @loop=@@rowcount
178 
179  
180 
181  
182 
183  
184 
185   Insert into #top 
186 
187  
188 
189   select top_part, child from #tmp_top
190 
191  
192 
193  
194 
195  
196 
197   Drop table #tmp_top
198 
199  
200 
201  
202 
203  
204 
205 END
206 
207  
208 
209  
210 
211  
212 
213 --查看#top 結果
214 
215  
216 
217 --如果建有一个专门存放part direct 的table也不错，方便以后查询
218 
219  
220 
221  select * from #top order by top_part
222 
223  
224 
225  
226 
227  
228 
229 GO
230 
231  
232 
233  
234 
235  
236 
237 exec usp_test
238

top_part child

----------------------

A0 A2

A0 A5

A0 C1

B0 B2

B0 B5

B0 C1

drop table bom

drop proc usp_test

有错误的地方欢迎大家拍砖,希望交流和共享。