读书人
栏目▼

结构化SQL话语练习

发布时间： 2013-10-19 20:58:22 作者： rapoo

结构化SQL语句练习
在不使用流程语句(如：if、while...)的情况下写出如下效果：

CREATE TABLE SheBeiRunHuaBiao --润滑设备表
(
F_NAME VARCHAR(50) --润滑名称
,F_DATE DATETIME --润滑日期
,F_CYCLE INT --润滑周期
,F_MARK VARCHAR(10) --润滑标记，已完成，未完成
)
--基础数据
INSERT INTO SheBeiRunHuaBiao(F_NAME,F_DATE,F_CYCLE,F_MARK)
--设备A
SELECT '设备A','2013-09-01',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-03',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-05',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-07',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-09',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-11',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-13',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-15',2,'已完成'

--设备B
SELECT '设备B','2013-09-01',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-04',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-07',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-10',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-13',3,'已完成'

--设备C
SELECT '设备C','2013-09-01',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-05',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-09',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-13',4,'已完成'

--设备D
SELECT '设备D','2013-09-01',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-06',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-11',5,'已完成'

/*
现在要求写出一个存储过程，要求如下
1.该存储过程有两个变量 @BeginDate ,@EndDate，查询出在这两个日期范围内润滑设备表SheBeiRunHuaBiao 的所有记录
2.如果润滑设备表SheBeiRunHuaBiao在@BeginDate 和 @EndDate 没有润滑记录,
需先根据每个设备的最后润滑日期F_DATE+润滑周期F_CYCLE,不断向后循环,
计算出下一个润滑日期,润滑标记F_MARK默认为'未完成'。
现有的记录则查出符合条件的。

比如:要查询 "2013-09-10"到"2013-09-20" 这两个日期范围内润滑设备表SheBeiRunHuaBiao 的所有记录
我们就先看 "设备D" 的信息
在这个范围内，设备D 只有一条记录

'设备D','2013-09-11',5,'已完成'
通过润滑日期F_DATE+润滑周期F_CYCLE，我们可以计算出下一个设备润滑记录
'设备D','2013-09-16',5,'未完成'
以此类推，统计出'设备A','设备B','设备C'在这个时间范围内的所有记录。

*/




过20楼 贴出本菜鸟的SQL~~~ 抛砖引玉 求大神来~~~~~~~~~~~~~~~~~~~~结构化SQL话语练习 围观新版主表演
[解决办法]
小F说这个真心难，交给别人吧。

楼下继续，看能到20楼不。。
[解决办法]
也当观众了，
这个趋势，板板们是都要当观众了
[解决办法]
楼主说：过20楼 贴出本菜鸟的SQL~~~ 抛砖引玉 求大神来

不知道各位板板的板砖会不会出来啊，能不能把楼主的玉给引出来呢。

弱弱的问一句，楼主，你现在的条件不变，改为sql2000环境来写，会什么效果呢？
[解决办法]
新版主呢～～坐等
[解决办法]
try this,


CREATE TABLE SheBeiRunHuaBiao  --润滑设备表
(
F_NAME VARCHAR(10)  --润滑名称
,F_DATE DATETIME    --润滑日期
,F_CYCLE INT        --润滑周期
,F_MARK VARCHAR(10) --润滑标记，已完成，未完成
)

INSERT INTO SheBeiRunHuaBiao(F_NAME,F_DATE,F_CYCLE,F_MARK)
SELECT '设备A','2013-09-01',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-03',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-05',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-07',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-09',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-11',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-13',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-15',2,'已完成'
UNION ALL
SELECT '设备B','2013-09-01',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-04',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-07',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-10',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-13',3,'已完成'
UNION ALL
SELECT '设备C','2013-09-01',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-05',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-09',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-13',4,'已完成'
UNION ALL
SELECT '设备D','2013-09-01',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-06',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-11',5,'已完成'


-- 建存储过程
create proc sp_getSheBei 
 
(@BeginDate varchar(20),
 @EndDate varchar(20))
as
begin
 set nocount on
 create table #t(F_NAME varchar(10),F_DATE datetime,F_CYCLE int,F_MARK varchar(10))

 insert into #t(F_NAME,F_DATE,F_CYCLE,F_MARK)
 select F_NAME,F_DATE,F_CYCLE,F_MARK 
  from SheBeiRunHuaBiao
  where F_DATE between @BeginDate and @EndDate
 
 while(@@rowcount>0)
 begin
  ;with t as
  (select F_NAME,F_DATE,F_CYCLE,F_MARK,
          row_number() over(partition by F_NAME order by F_DATE desc) 'rn'
   from #t)
  insert into #t(F_NAME,F_DATE,F_CYCLE,F_MARK)
  select a.F_NAME,dateadd(d,b.F_CYCLE,a.F_DATE),b.F_CYCLE,'未完成'
   from t a
   inner join t b on a.F_NAME=b.F_NAME and a.rn=1 and b.rn=1
   where dateadd(d,b.F_CYCLE,a.F_DATE) between @BeginDate and @EndDate
   
 end
 
 select F_NAME,F_DATE,F_CYCLE,F_MARK from #t order by F_NAME,F_DATE
end

-- 执行
exec sp_getSheBei @BeginDate='2013-09-10',@EndDate='2013-09-20'

-- 结果
/*
F_NAME     F_DATE                  F_CYCLE     F_MARK
---------- ----------------------- ----------- ----------
设备A        2013-09-11 00:00:00.000 2           已完成
设备A        2013-09-13 00:00:00.000 2           已完成
设备A        2013-09-15 00:00:00.000 2           已完成
设备A        2013-09-17 00:00:00.000 2           未完成
设备A        2013-09-19 00:00:00.000 2           未完成
设备B        2013-09-10 00:00:00.000 3           已完成
设备B        2013-09-13 00:00:00.000 3           已完成
设备B        2013-09-16 00:00:00.000 3           未完成
设备B        2013-09-19 00:00:00.000 3           未完成
设备C        2013-09-13 00:00:00.000 4           已完成
设备C        2013-09-17 00:00:00.000 4           未完成
设备D        2013-09-11 00:00:00.000 5           已完成
设备D        2013-09-16 00:00:00.000 5           未完成
*/

[解决办法]
唐诗，楼主不许用if,while，你违规了
[解决办法]
引用:
Quote: 引用:

try this,

CREATE TABLE SheBeiRunHuaBiao  --润滑设备表
(
F_NAME VARCHAR(10)  --润滑名称
,F_DATE DATETIME    --润滑日期
,F_CYCLE INT        --润滑周期
,F_MARK VARCHAR(10) --润滑标记，已完成，未完成
)

INSERT INTO SheBeiRunHuaBiao(F_NAME,F_DATE,F_CYCLE,F_MARK)
SELECT '设备A','2013-09-01',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-03',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-05',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-07',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-09',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-11',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-13',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-15',2,'已完成'
UNION ALL
SELECT '设备B','2013-09-01',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-04',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-07',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-10',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-13',3,'已完成'
UNION ALL
SELECT '设备C','2013-09-01',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-05',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-09',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-13',4,'已完成'
UNION ALL
SELECT '设备D','2013-09-01',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-06',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-11',5,'已完成' 
 


-- 建存储过程
create proc sp_getSheBei
(@BeginDate varchar(20),
 @EndDate varchar(20))
as
begin
 set nocount on
 create table #t(F_NAME varchar(10),F_DATE datetime,F_CYCLE int,F_MARK varchar(10))

 insert into #t(F_NAME,F_DATE,F_CYCLE,F_MARK)
 select F_NAME,F_DATE,F_CYCLE,F_MARK 
  from SheBeiRunHuaBiao
  where F_DATE between @BeginDate and @EndDate
 
 while(@@rowcount>0)
 begin
  ;with t as
  (select F_NAME,F_DATE,F_CYCLE,F_MARK,
          row_number() over(partition by F_NAME order by F_DATE desc) 'rn'
   from #t)
  insert into #t(F_NAME,F_DATE,F_CYCLE,F_MARK)
  select a.F_NAME,dateadd(d,b.F_CYCLE,a.F_DATE),b.F_CYCLE,'未完成'
   from t a
   inner join t b on a.F_NAME=b.F_NAME and a.rn=1 and b.rn=1
   where dateadd(d,b.F_CYCLE,a.F_DATE) between @BeginDate and @EndDate
   
 end
 
 select F_NAME,F_DATE,F_CYCLE,F_MARK from #t order by F_NAME,F_DATE
end

-- 执行
exec sp_getSheBei @BeginDate='2013-09-10',@EndDate='2013-09-20'

-- 结果
/*
F_NAME     F_DATE                  F_CYCLE     F_MARK
---------- ----------------------- ----------- ----------
设备A        2013-09-11 00:00:00.000 2           已完成
设备A        2013-09-13 00:00:00.000 2           已完成
设备A        2013-09-15 00:00:00.000 2           已完成
设备A        2013-09-17 00:00:00.000 2           未完成
设备A        2013-09-19 00:00:00.000 2           未完成
设备B        2013-09-10 00:00:00.000 3           已完成
设备B        2013-09-13 00:00:00.000 3           已完成
设备B        2013-09-16 00:00:00.000 3           未完成
设备B        2013-09-19 00:00:00.000 3           未完成
设备C        2013-09-13 00:00:00.000 4           已完成
设备C        2013-09-17 00:00:00.000 4           未完成
设备D        2013-09-11 00:00:00.000 5           已完成
设备D        2013-09-16 00:00:00.000 5           未完成
*/



if、while 都不可以使用 不然嵌套俩while那多没效率 过程话的语句尽量不使用~~


这个WHILE真心的没多大问题 只是判断有没有插入数据
[解决办法]
看上去很复杂的样子啊，看着都头晕了哈。

楼主说，要写个存储过程，但又不用if，while，难度非常大呀。

版主们上吧。结构化SQL话语练习
[解决办法]
sorry,17楼有Bug,修正如下,

CREATE TABLE SheBeiRunHuaBiao  --润滑设备表
(
F_NAME VARCHAR(10)  --润滑名称
,F_DATE DATETIME    --润滑日期
,F_CYCLE INT        --润滑周期
,F_MARK VARCHAR(10) --润滑标记，已完成，未完成
)
 
INSERT INTO SheBeiRunHuaBiao(F_NAME,F_DATE,F_CYCLE,F_MARK)
SELECT '设备A','2013-09-01',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-03',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-05',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-07',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-09',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-11',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-13',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-15',2,'已完成'
UNION ALL
SELECT '设备B','2013-09-01',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-04',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-07',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-10',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-13',3,'已完成'
UNION ALL
SELECT '设备C','2013-09-01',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-05',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-09',4,'已完成' 
 
UNION ALL
SELECT '设备C','2013-09-13',4,'已完成'
UNION ALL
SELECT '设备D','2013-09-01',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-06',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-11',5,'已完成'
 
 
-- 建存储过程
create proc sp_getSheBei
(@BeginDate varchar(20),
 @EndDate varchar(20))
as
begin
 set nocount on

 select distinct 
        a.F_NAME,
        dateadd(d,a.F_CYCLE*b.number,a.F_DATE) 'F_DATE',
        a.F_CYCLE,
        case b.number when 0 then a.F_MARK else '未完成' end 'F_MARK' 
 from
 (select F_NAME,F_DATE,F_CYCLE,F_MARK 
  from SheBeiRunHuaBiao
  where F_DATE between @BeginDate and @EndDate) a
 cross join (select number from master.dbo.spt_values 
             where type='P') b
 where dateadd(d,a.F_CYCLE*b.number,a.F_DATE) between @BeginDate and @EndDate
 and not exists
 (select 1 from SheBeiRunHuaBiao c
  where b.number>0 and c.F_DATE between @BeginDate and @EndDate
  and c.F_NAME=a.F_NAME and c.F_DATE=dateadd(d,a.F_CYCLE*b.number,a.F_DATE))
 order by a.F_NAME,dateadd(d,a.F_CYCLE*b.number,a.F_DATE)
end
 
-- 执行
exec sp_getSheBei @BeginDate='2013-09-10',@EndDate='2013-09-20'
 
-- 结果
/*
F_NAME     F_DATE                  F_CYCLE     F_MARK
---------- ----------------------- ----------- ----------
设备A        2013-09-11 00:00:00.000 2           已完成
设备A        2013-09-13 00:00:00.000 2           已完成
设备A        2013-09-15 00:00:00.000 2           已完成
设备A        2013-09-17 00:00:00.000 2           未完成
设备A        2013-09-19 00:00:00.000 2           未完成
设备B        2013-09-10 00:00:00.000 3           已完成
设备B        2013-09-13 00:00:00.000 3           已完成
设备B        2013-09-16 00:00:00.000 3           未完成
设备B        2013-09-19 00:00:00.000 3           未完成
设备C        2013-09-13 00:00:00.000 4           已完成
设备C        2013-09-17 00:00:00.000 4           未完成
设备D        2013-09-11 00:00:00.000 5           已完成
设备D        2013-09-16 00:00:00.000 5           未完成
*/

[解决办法]
引用:
Quote: 引用:


[/code]


19楼版主写的不错哦,但如果我输入一个日期使得A产品没有记录，你的查询结果就有问题了，只会显示在这个时间段内有记录的数据如A并推理A出未完成的数据，B\C\D在这个时间段没有记录的话，就一个结果都没有。应该是B\C\D在这个时间段没有记录，也要根据最后时间去推理出来在这个时间段的所有数据。 用你现在的存储过程试下“2013-09-20”至“2013-10-20”

修改如下,测试2013-09-20至2013-10-20.

CREATE TABLE SheBeiRunHuaBiao  --润滑设备表
(
F_NAME VARCHAR(10)  --润滑名称
,F_DATE DATETIME    --润滑日期
,F_CYCLE INT        --润滑周期
,F_MARK VARCHAR(10) --润滑标记，已完成，未完成
)
  
INSERT INTO SheBeiRunHuaBiao(F_NAME,F_DATE,F_CYCLE,F_MARK)
SELECT '设备A','2013-09-01',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-03',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-05',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-07',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-09',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-11',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-13',2,'已完成'
UNION ALL
SELECT '设备A','2013-09-15',2,'已完成'
UNION ALL
SELECT '设备B','2013-09-01',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-04',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-07',3,'已完成'
UNION ALL
SELECT '设备B','2013-09-10',3,'已完成' 
 
UNION ALL
SELECT '设备B','2013-09-13',3,'已完成'
UNION ALL
SELECT '设备C','2013-09-01',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-05',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-09',4,'已完成'
UNION ALL
SELECT '设备C','2013-09-13',4,'已完成'
UNION ALL
SELECT '设备D','2013-09-01',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-06',5,'已完成'
UNION ALL
SELECT '设备D','2013-09-11',5,'已完成'
  
  
-- 建存储过程
create proc sp_getSheBei
(@BeginDate varchar(20),
 @EndDate varchar(20))
as
begin
 set nocount on

 select distinct
        a.F_NAME,
        dateadd(d,a.F_CYCLE*b.number,a.F_DATE) 'F_DATE',
        a.F_CYCLE,
        case b.number when 0 then a.F_MARK else '未完成' end 'F_MARK' 
 from
 (select F_NAME,F_DATE,F_CYCLE,F_MARK 
   from SheBeiRunHuaBiao
   where F_DATE between @BeginDate and @EndDate
  union all
  select F_NAME,F_DATE,F_CYCLE,F_MARK 
   from SheBeiRunHuaBiao e
   where not exists(select 1 from SheBeiRunHuaBiao f 
                    where f.F_NAME=e.F_NAME and f.F_DATE>e.F_DATE)
   and e.F_NAME not in(select distinct F_NAME from SheBeiRunHuaBiao
                       where F_DATE between @BeginDate and @EndDate)
  ) a
 cross join (select number from master.dbo.spt_values 
             where type='P') b
 where dateadd(d,a.F_CYCLE*b.number,a.F_DATE) between @BeginDate and @EndDate
 and not exists
 (select 1 from SheBeiRunHuaBiao c
  where b.number>0 and c.F_DATE between @BeginDate and @EndDate
  and c.F_NAME=a.F_NAME and c.F_DATE=dateadd(d,a.F_CYCLE*b.number,a.F_DATE))
 order by a.F_NAME,dateadd(d,a.F_CYCLE*b.number,a.F_DATE)
end
  
-- 执行
exec sp_getSheBei @BeginDate='2013-09-20',@EndDate='2013-10-20'

-- 结果
/*
F_NAME     F_DATE                  F_CYCLE     F_MARK
---------- ----------------------- ----------- ----------
设备A        2013-09-21 00:00:00.000 2           未完成
设备A        2013-09-23 00:00:00.000 2           未完成
设备A        2013-09-25 00:00:00.000 2           未完成
设备A        2013-09-27 00:00:00.000 2           未完成
设备A        2013-09-29 00:00:00.000 2           未完成
设备A        2013-10-01 00:00:00.000 2           未完成
设备A        2013-10-03 00:00:00.000 2           未完成
设备A        2013-10-05 00:00:00.000 2           未完成
设备A        2013-10-07 00:00:00.000 2           未完成
设备A        2013-10-09 00:00:00.000 2           未完成
设备A        2013-10-11 00:00:00.000 2           未完成
设备A        2013-10-13 00:00:00.000 2           未完成
设备A        2013-10-15 00:00:00.000 2           未完成
设备A        2013-10-17 00:00:00.000 2           未完成
设备A        2013-10-19 00:00:00.000 2           未完成
设备B        2013-09-22 00:00:00.000 3           未完成
设备B        2013-09-25 00:00:00.000 3           未完成 
 
设备B        2013-09-28 00:00:00.000 3           未完成
设备B        2013-10-01 00:00:00.000 3           未完成
设备B        2013-10-04 00:00:00.000 3           未完成
设备B        2013-10-07 00:00:00.000 3           未完成
设备B        2013-10-10 00:00:00.000 3           未完成
设备B        2013-10-13 00:00:00.000 3           未完成
设备B        2013-10-16 00:00:00.000 3           未完成
设备B        2013-10-19 00:00:00.000 3           未完成
设备C        2013-09-21 00:00:00.000 4           未完成
设备C        2013-09-25 00:00:00.000 4           未完成
设备C        2013-09-29 00:00:00.000 4           未完成
设备C        2013-10-03 00:00:00.000 4           未完成
设备C        2013-10-07 00:00:00.000 4           未完成
设备C        2013-10-11 00:00:00.000 4           未完成
设备C        2013-10-15 00:00:00.000 4           未完成
设备C        2013-10-19 00:00:00.000 4           未完成
设备D        2013-09-21 00:00:00.000 5           未完成
设备D        2013-09-26 00:00:00.000 5           未完成
设备D        2013-10-01 00:00:00.000 5           未完成
设备D        2013-10-06 00:00:00.000 5           未完成
设备D        2013-10-11 00:00:00.000 5           未完成
设备D        2013-10-16 00:00:00.000 5           未完成
*/

读书人网 >SQL Server

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net