Although the quiz looks similar to my question, due to different target, my idea of the solution is completely different.

deep_blue(BLUE)

SELECT Item, Line, MAX(sequence) maxSequence INTO #seqMax FROM SourceTable
Group by Item, Line
HAVING MAX(sequence) > COUNT(*) +1
--Although it can get same result without above HAVING condition, it improves performance for large amount data.

declare @max int
SELECT @max = maxSequence from #seqMax
declare @i int
create table #seqNum (sequence int)
SELECT @i =0
while @i <=@max
Begin
insert #seqNum values (@i)
set @i = @i +1
End

SELECT M.Item, M.Line, S.sequence
FROM #seqNum S JOIN #seqMax M ON S.sequence <= M.maxSequence
Left JOIN SourceTable I ON I.Item = M.Item AND I.Line = M.Line AND I.sequence = S.sequence
Where I.sequence IS NULL
ORDER BY M.Item, M.Line, S.sequence

(#7486075@0)
Last Updated: 2012-5-3
This post has been archived. It cannot be replied.

兴趣还是最重要的，有了兴趣自然会去学那些该用到的数学 -binghongcha76(一只大猫); 2012-5-2 (#7483698@0)

At least give one your example. -deep_blue(BLUE); 2012-5-2 (#7483937@0)

看做什么啦，在微软开发SQL或LINQ就要想着如何查询得快。 -tjhong(以后再说); 2012-5-2 (#7483904@0)

Don’t fully understand. -deep_blue(BLUE); 2012-5-2 {55} (#7484066@0)
You are not going to say the slower the better, do you?

把你写的SQL秀一下看看？SQL不就是关系代数的一种实现吗？ -newkid(newkid); 2012-5-2 (#7484079@0)

You are expert of SQL. It’s just B. M. N. F. to show SQL code to you. -deep_blue(BLUE); 2012-5-2 (#7484200@0)

it's similar to this SQL quiz, but I only know Oracle syntax and would like to know how it looks like in SQL Server -newkid(newkid); 2012-5-2 {108} (#7484469@0)
http://www.itpub.net/forum.php?mod=redirect&goto=findpost&ptid=1506936&pid=19372285

see the quiz in #191.
http://www.itpub.net/forum.php?mod=redirect&goto=findpost&ptid=1506936&pid=19372285

Although the quiz looks similar to my question, due to different target, my idea of the solution is completely different. -deep_blue(BLUE); 2012-5-3 {712} (#7486075@0)
SELECT Item, Line, MAX(sequence) maxSequence INTO #seqMax FROM SourceTable
Group by Item, Line
HAVING MAX(sequence) > COUNT(*) +1
--Although it can get same result without above HAVING condition, it improves performance for large amount data.

declare @max int
SELECT @max = maxSequence from #seqMax
declare @i int
create table #seqNum (sequence int)
SELECT @i =0
while @i <=@max
Begin
insert #seqNum values (@i)
set @i = @i +1
End

SELECT M.Item, M.Line, S.sequence
FROM #seqNum S JOIN #seqMax M ON S.sequence <= M.maxSequence
Left JOIN SourceTable I ON I.Item = M.Item AND I.Line = M.Line AND I.sequence = S.sequence
Where I.sequence IS NULL
ORDER BY M.Item, M.Line, S.sequence

你这写法似乎有问题？ -newkid(newkid); 2012-5-3 {1750} (#7486714@0)
本文发表在 rolia.net 枫下论坛SELECT @max = maxSequence from #seqMax

难度不应该是这样：

SELECT @max = MAX(maxSequence) from #seqMax

同样的思路用ORACLE实现：

CREATE TABLE SourceTable(Item VARCHAR2(10),Line NUMBER, seq NUMBER);

INSERT INTO SourceTable VALUES ('A',1,0);
INSERT INTO SourceTable VALUES ('A',1,1);
INSERT INTO SourceTable VALUES ('A',1,2);
INSERT INTO SourceTable VALUES ('A',1,3);
INSERT INTO SourceTable VALUES ('A',1,4);
INSERT INTO SourceTable VALUES ('A',1,5);

INSERT INTO SourceTable VALUES ('B',1,0);
INSERT INTO SourceTable VALUES ('B',1,1);
INSERT INTO SourceTable VALUES ('B',1,2);
INSERT INTO SourceTable VALUES ('B',1,3);
INSERT INTO SourceTable VALUES ('B',1,4);
INSERT INTO SourceTable VALUES ('B',1,5);
INSERT INTO SourceTable VALUES ('B',1,8);
INSERT INTO SourceTable VALUES ('B',1,9);

INSERT INTO SourceTable VALUES ('A',2,1);
INSERT INTO SourceTable VALUES ('A',2,2);
INSERT INTO SourceTable VALUES ('A',2,5);
INSERT INTO SourceTable VALUES ('A',2,7);
INSERT INTO SourceTable VALUES ('A',2,8);

SELECT b.item,b.line,a.n
FROM (SELECT LEVEL-1 n FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(seq)+1 FROM SourceTable)) a
LEFT JOIN (SELECT s.*,MAX(seq) OVER(PARTITION BY item,line) maxseq FROM SourceTable s) b
PARTITION BY (b.item,b.line,b.maxseq)
ON (A.n=b.seq)
WHERE b.seq IS NULL AND a.n<=b.maxseq;

ITEM LINE N
---------- ---------- ----------
A 2 0
A 2 3
A 2 4
A 2 6
B 1 6
B 1 7

6 rows selected.

ORACLE的PARTITION OUTER JOIN免除了中间一次笛卡尔积。更多精彩文章及讨论，请光临枫下论坛 rolia.net

抛砖引玉一下。不算算法。如果不用DataView，自己做个快速排序的方法，那就是算法了。 -tjhong(以后再说); 2012-5-3 {763} (#7486233@0)

class Program
{
static void Main(string[] args)
{
DataTable DT = new DataTable();
//数据产生过程
..........

DataView DV = DT.DefaultView ;
DV.Sort = "Item,line,sequence";
int i, j;
for (i = 0; i < DV.Count - 1; i++)
{
if (DV[i]["Item"] == DV[i + 1]["Item"] && DV[i]["line"] == DV[i + 1]["line"])
{
for (j = (int)DV[i]["sequence"] + 1; j < (int)DV[i + 1]["sequence"] ; j++)
{
Console.WriteLine(DV[i]["Item"] + " || "+DV[i]["line"] +" || " + j.ToString());
}
}
}

}
}

My point is not how to achieve it. Rather than, when we think our solution, do we try to use any math theory help our brainstorm? -deep_blue(BLUE); 2012-5-3 (#7487240@0)

in most cases simple arithmetics will do it so why bother to employ math? like this one is just sorting of a data set then compare the values with the neighbour rows. -newkid(newkid); 2012-5-3 (#7487663@0)

First of all, can you compare 100 thousands records by eyes? Second, in what criterion you define simple or complex. -deep_blue(BLUE); 2012-5-3 (#7487790@0)

it's simple and repeated,that's why we use computer. for me, "simple" means anything that can be solved by high school math -newkid(newkid); 2012-5-3 (#7487833@0)

前两年无意间读了codd 的那篇文章，然后看了些SQL的书籍，玩了一下as400，感觉真正的数学在数据库内部，然后对SQL就彻底没兴趣了。 -hhyang(哈哈羊); 2012-5-15 (#7518081@0)

Most IT people know math is base of IT. -deep_blue(BLUE); 2012-5-16 {547} (#7518464@0)
As newkid mentioned that underneath of SQL is relational algebra. Though, in actual SQL practice, does anyone apply relational algebra solution to data searching question then translate the solution to SQL commands?

And actually here many IT people are excellent developer/analyser, system/networking/database administers even they did not systemically learn IT/ math.

Our Chinese people over-emphasize knowledge of base theories but ignore practical skills especially real world skills.

BTW, AS400 is a platform rather than a database.

I'm not an IT guy. -hhyang(哈哈羊); 2012-5-16 {766} (#7519318@0)
Math is a tool for most sciences and technologies, including IT.

Actual SQL practice, in my understanding, belongs to part of software engineering. It is more an art than a science. Experience plays a critical role in such practice.

IT practitioners are not mathematicians, and they needn't to be a mathematician before doing their jobs. I personally am more interested in doing research job, but this doesn't mean researches are more important than industry practices.

Many Chinese people put more attention on theories than practical skills. This is not right. I believe that a theory is nearly useless in industry if it can't be implemented with reasonable amount of time and money.

I ever played DB2 on AS/400, correct me if I remember it wrongly.

In terms R&D works in many fields, math is a tool, IT technologies, such as SQL, programming, are tools too (if needed). But math is used to help building model of R&D target rather than to help SQL or programming. -deep_blue(BLUE); 2012-5-16 (#7519853@0)

Yep, those tools tackle different problems. Process calculus/algebra sounds like the math for modelling concurrent systems, but writing real-time software with process calculus is very expensive. -hhyang(哈哈羊); 2012-5-16 {256} (#7520179@0)
Software programming is such a complicated process that hasn't been fully understood. I'm afraid there won't be practical math theory for that in near future, otherwise a software tool may generate another software given user requirements as input. :-)

If it was true, I'm afraid -deep_blue(BLUE); 2012-5-16 {49} (#7520586@0)
it’s close to end of the world, like Terminator.

Here is another simply question on matrix: graph can be represented as adjacency matrix. Now I understand but still don't understand why, the square of this matrix means the # of pathways between nodes. -renjl0810(Virtual Void); 2012-5-18 {99} (#7526028@0)

And more importantly, what about the matrix multiply the transpose of itself?

Thanks in advance!

@Florida

Although the quiz looks similar to my question, due to different target, my idea of the solution is completely different.

Replies, comments and Discussions:

More Topics