如何在记录中修好一个洞？ [英] how to fine a hole in a records?

问题描述

大家好！


我需要你的帮助来实现存储过程或触发器的算法。


工具：MS SQL server 2000， T-SQL


表：

[unique_id] [mynumber] [周]


[unique_id] - bigint，主键，身份自动增量

[周] - int，1-53，周数

[mynumber] - int，1 - 7，每周，每天每日记录一次，每周上涨

到每周7美元

所以，每周我们都有1到7美元的数量

或没有（如果当天没有记录），


我们可以随意插入或删除mynubers


示例：


第1周，mynumber 1,2,3 - 所以如果我们插入新记录，mynumber值

= 4

第2周，mynumber 1,2,3,5,7 - 所以下一个mynumber = 4


问题：


如何使用_only_ T-SQL找到特定周的错过号码

我要插入记录吗？

比ks。

Chapai

Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: MS SQL server 2000, T-SQL

TABLE:
[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment
[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ T-SQL find a missed numbers for particular week when
I''m insert a records?
Thanks.
Chapai

推荐答案

请发布DDL，以便人们不必猜猜是什么您的

架构中的密钥，
约束，声明性参照完整性，数据类型等。样本数据也是一个好主意，同时还有明确的

规格。


行不是记录而你没有关系密钥

伪代码。忽略设计基本上是坏的，因为你的b $ b应该使用临时数据类型作为时态数据，你的表应该是这样的：b $ b看起来像这样：


CREATE TABLE Foobar

（week_nbr INTEGER NOT NULL

CHECK（week_nbr> 0），

day_nbr INTEGER NOT NULL

CHECK（day_nbr BETWEEN 1和7），

PRIMARY KEY（week_nbr，day_nbr））;

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records and you have no relational key in your
pseudo-code. Ignoring that the design is fundamentally bad because you
should be using temporal datatypes for temporal data, your table should
have looked like this:

CREATE TABLE Foobar
(week_nbr INTEGER NOT NULL
CHECK(week_nbr > 0),
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 7),
PRIMARY KEY(week_nbr, day_nbr));

对于每周我们有一个day_nbr从1到7或没有（如果没有

for every week we have a day_nbr from 1 to 7 or nothing (if no

记录[sic]当天），我们可以插入或删除day_nbr in任意

订单，随意。 。当我插入记录时，如何使用_only_ T-SQL查找特定周的错过号码？[原文如此]？ <


这看起来有点难看，但速度很快。


CREATE PROCEDURE InsertNewFoobar（@new_week_nbr INTEGER）

BEGIN

DECLARE @new_day_nbr INTEGER;

SET @new_day_nbr

=案例1不在美元

（SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr）

THEN 1

WHEN 2 NOT IN

（SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr）

那么2

当3不在

（选择day_nbr来自Foobar WHERE week_nbr = @new_week_nbr）

那么3

当4不在

（选择day_nbr来自Foobar WHERE week_nbr = @new_week_nbr）

那么4

当5不在

（SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr）

那么5

当6不在

（SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr）

那么6

当7不在

（ SELECT day_nbr FROM Foobar WHERE周_nbr = @new_week_nbr）

那么7

ELSE NULL END;


INSERT INTO Foobar（week_nbr，day_nbr）

VALUES（@ new_week_nbr，@ new_day_nbr）;

- 如果你已经有7天了，那么你会得到一个主键违规

- 你给了没有关于如何处理它的规范


结束：

在标准SQL中，CASE表达式可以在VALUES（）列表中

record [sic] for that day), we can insert or delete day_nbr in any
order, at will . . How to use _only_ T-SQL to find a missed number for
particular week when I am inserting records [sic]? <<

This is a little ugly looking, but it is fast.

CREATE PROCEDURE InsertNewFoobar (@new_week_nbr INTEGER)
BEGIN
DECLARE @new_day_nbr INTEGER;
SET @new_day_nbr
= CASE WHEN 1 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 1
WHEN 2 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 2
WHEN 3 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 3
WHEN 4 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 4
WHEN 5 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 5
WHEN 6 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 6
WHEN 7 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 7
ELSE NULL END;

INSERT INTO Foobar (week_nbr, day_nbr)
VALUES (@new_week_nbr, @new_day_nbr);
-- if you have 7 days already, then you get a primary key violation
-- you gave no specs on how to handle it

END:
In Standard SQL, the CASE expression could be in the VALUES () list

Chapai（ra*****@mail.ru）写道：

Chapai (ra*****@mail.ru) writes:

工具：MS SQL server 2000，T-SQL

表：
[unique_id] [mynumber] [周]

[unique_id] - bigint，主键，身份自动增量
[周] - int， 1-53，周数
[mynumber] - int，1 - 7，每周，每日记录每天一次，每周增加7次


我没有看到unique_id的重点。从你的描述判断

（周，mynumber）是独一无二的。然后它们应该是主键。

我们可以按任意顺序插入或删除mynubers

示例：

第1周，mynumber 1,2,3 - 所以如果我们插入一个新记录，mynumber值
= 4
第2周，mynumber 1,2,3,5,7 - 所以下一个mynumber = 4
<问题：

如何使用_only_ T-SQL找到特定周的错过号码
我要插入记录？

tool: MS SQL server 2000, T-SQL

TABLE:
[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment
[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week
I don''t see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.
we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ T-SQL find a missed numbers for particular week when
I''m insert a records?

搜索Google或主题思考代码或SP，这是microsoft.public.sqlserver.programming中最近的

线程，适用于整个范围的

对类似问题的建议。


由于这个问题限制在1-7，这里有一个更简单的

解决方案：


SELECT MIN（n）

FROM（选择n = 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5

UNION ALL

SELECT 6

UNION ALL

SELECT 7）n

不存在（SELECT *

来自周w

WHERE w.weekno = @weekno

AND nn = w.mynumber）


-

Erland Sommarskog，SQL Server MVP， es****@sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp

嗨！


谢谢，我已经找到了相同的解决方案


创建proc stupidproc（@week as int）

as


将@mynumber声明为int，@ availablenumber声明为int

set @mynumber = 1


WHILE @mynumber< 8

开始

如果不是EXISTS（从MYTABLE中选择mynumber


，其中mynumber = @mynumber和[week] = .. 。@周）


开始

设置@availablenumber = @mynumber

休息

结束

其他

设置@mynumber = @mynumber + 1


续表

结束


选择@availablenumber

Hi!

Thanks, I already find the same solution with while

create proc stupidproc ( @week as int )
as

declare @mynumber as int, @availablenumber as int
set @mynumber = 1

WHILE @mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE

where mynumber = @mynumber and [week]=...@week)

begin
set @availablenumber = @mynumber
break
end
else
set @mynumber = @mynumber + 1

CONTINUE
end

select @availablenumber

这篇关于如何在记录中修好一个洞？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！