请教一个sql语句

Question

表信息：
table1
person   type   index
a              0        1
a              1        2
a              1        3
a              1        3
a              0        3
a              0        2
a              1        1
b              1        2
b              1        4
b              1        3
b              0        3index的值是1-4这4个整数。
对于每一个person，type有时会出现连续的1（单个1也算），当有连续1出现时要记录第一个1的index和最后一个1的index，并产生一个数对，比如a有（2,3）、（2,2），b有（2,3）。每一个person会产生几个数对（type都为0时没有）。最后在一张index的二维表中，记下不同数对产生的个数，二维表结构如下：
       1      2     3      4
1     0      0     0      0
2     0      1     2      0
3     0      0     0      0
4     0      0     0      0
或者简单一点，直接记录产生的数对：
i      j
2    3
2    2
2    3新手请大神不吝赐教

Answer 1

先看person a，爱的type从上到下中间会出现连续的1，第一次出现1所对应的index为2，往下第一次变为0的前一个1所对应的index为3，这时产生一个数对（2，3）。再往下看，再一次出现1的时候index为1，因为只有一个1，所以数对为（1,1）。这里我前面写错了，不好意思，误导了大家。同理person b，产生数对（2,3）。
最后的表应该为：
       1      2     3      4
1     1      0     0      0
2     0      0     2      0
3     0      0     0      0
4     0      0     0      0
或者简单一点，直接记录产生的数对：
i      j
2    3
1    1
2    3

Answer 2

type表示的是不同的状态，1为开车状态，0为走路状态，index可以表示地点（地点已标序，这里就是1,2,3,4四个地点）。
最后那张表其实就是统计了在开车状态下，地点i到地点j的车流量

Answer 3

a 走的是2、3；
b走的是2、4、3
这能一样？
如果算流量，我理解，怎么也应该这样计算：
a：2、3
a：1、1
b：2、4
b：4、3

Answer 4

table1表有自增的ID列吗？如果有应该会好做一点，如果没有就要用到游标。

Answer 5

DECLARE @t TABLE(id INT IDENTITY(1,1),persion VARCHAR(20),[TYPE] INT,[INDEX] int)
INSERT INTO @t(  persion, TYPE, [INDEX] )
SELECT 'a',              0 ,       1 UNION ALL 
SELECT 'a',              1 ,       2 UNION ALL 
SELECT 'a',              1 ,       3 UNION ALL 
SELECT 'a',              1 ,       3 UNION ALL 
SELECT 'a',              0 ,       3 UNION ALL 
SELECT 'a',              0 ,       2 UNION ALL 
SELECT 'a',              1 ,       1 UNION ALL 
SELECT 'b',              1 ,       2 UNION ALL 
SELECT 'b',              1 ,       4 UNION ALL 
SELECT 'b',              1 ,       3 UNION ALL 
SELECT 'b',              0 ,       3;
WITH etc AS (
SELECT *,MIN(partId) OVER(PARTITION BY persion,TYPE,id-partId) AS minid,max(partId) OVER(PARTITION BY persion,TYPE,id-partId) AS maxid FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY id) AS partId
FROM @t 
)a
)
SELECT DISTINCT persion ,TYPE,b.[INDEX],c.[INDEX]
FROM etc a
CROSS APPLY(SELECT [INDEX] FROM etc WHERE persion = a.persion AND TYPE = a.TYPE AND id - partId = a.id - a.partId AND partId = minid) b
CROSS APPLY(SELECT [INDEX] FROM etc WHERE persion = a.persion AND TYPE = a.TYPE AND id - partId = a.id - a.partId AND partId = maxid) c
WHERE TYPE = 1

Answer 6

WITH t(person,[type],[index]) AS (--数据源
SELECT 'a',0,1 UNION ALL
SELECT 'a',1,2 UNION ALL
SELECT 'a',1,3 UNION ALL
SELECT 'a',1,3 UNION ALL
SELECT 'a',0,3 UNION ALL
SELECT 'a',0,2 UNION ALL
SELECT 'a',1,1 UNION ALL
SELECT 'b',1,2 UNION ALL
SELECT 'b',1,4 UNION ALL
SELECT 'b',1,3 UNION ALL
SELECT 'b',0,3 
)
,t1 AS (
SELECT * ,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS id
FROM t
)
,t2 AS (
SELECT t1.* 
,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) id2
FROM t1 
WHERE t1.type=1
)
,t3 AS (
SELECT MIN(id)b,MAX(id)e
FROM t2
GROUP BY person,id-id2
)
SELECT tb.[index] i,te.[index] j
FROM t3 
JOIN t1 tB ON tb.id=t3.b
JOIN t1 tE ON te.id=t3.e

Answer 7

思路我明白了，就是mysql不能用row_number()，能不能换一种写法？

Answer 8

这样写比较容易理解，但row_number()那里能不能换一种写法，mysql不能用

Answer 9

http://blog.csdn.net/muzizhuben/article/details/49449853
row_number()你可以找下替换的写法。当然如果不想用row_number()的话可以试试游标

Answer 10

下面是游标的写法
DECLARE @t TABLE(persion VARCHAR(20),[TYPE] INT,[INDEX] int)
INSERT INTO @t(  persion, TYPE, [INDEX] )
SELECT 'a',              0 ,       1 UNION ALL 
SELECT 'a',              1 ,       2 UNION ALL 
SELECT 'a',              1 ,       3 UNION ALL 
SELECT 'a',              1 ,       3 UNION ALL 
SELECT 'a',              0 ,       3 UNION ALL 
SELECT 'a',              0 ,       2 UNION ALL 
SELECT 'a',              1 ,       1 UNION ALL 
SELECT 'b',              1 ,       2 UNION ALL 
SELECT 'b',              1 ,       4 UNION ALL 
SELECT 'b',              1 ,       3 UNION ALL 
SELECT 'b',              0 ,       3SELECT * FROM @tDECLARE cur CURSOR 
FOR SELECT persion ,type,[index] FROM @tDECLARE @persion VARCHAR(10),@TYPE int,@INDEX INT,
@tmp_persion VARCHAR(10),@tmp_TYPE int,@tmp_INDEX int ,
@tmp_maxIndex INT DECLARE @ret TABLE(persion VARCHAR(20),[TYPE] INT,[MinINDEX] int,[MaxINDEX] int)OPEN cur lab:
FETCH NEXT FROM cur INTO @persion ,@TYPE ,@INDEX WHILE @@FETCH_STATUS = 0 
BEGIN
IF @type <> 1
FETCH NEXT FROM cur INTO @persion ,@TYPE ,@INDEX 
ELSE 
BEGIN 
SELECT @tmp_maxIndex = @INDEX FETCH NEXT FROM cur INTO @tmp_persion ,@tmp_TYPE ,@tmp_INDEX WHILE @@FETCH_STATUS = 0 
BEGIN
IF @type <> 1
BEGIN
INSERT INTO @ret( persion, TYPE, MinINDEX, MaxINDEX )
SELECT @persion,@TYPE,@INDEX,@tmp_maxIndex

GOTO lab 

END 
ELSE 
BEGIN  IF @persion = @tmp_persion AND @TYPE = @tmp_TYPE AND @tmp_TYPE = 1
BEGIN 
SELECT @tmp_maxIndex = @tmp_INDEX
FETCH NEXT FROM cur INTO @tmp_persion ,@tmp_TYPE ,@tmp_INDEX
END 
ELSE 
BEGIN
INSERT INTO @ret( persion, TYPE, MinINDEX, MaxINDEX )
SELECT @persion,@TYPE,@INDEX,@tmp_maxIndex

IF @tmp_TYPE = 1
SELECT @persion = @tmp_persion , @TYPE = @tmp_TYPE ,@INDEX = @tmp_INDEX
ELSE 
GOTO lab 
END
END   
END
END 
ENDCLOSE cur 
DEALLOCATE cur SELECT * FROM @ret

Answer 11

已解决。14楼给了挺清楚的方法，我自己直接加了一个自增长的id列，t3表中再把person字段保留，这样也可以实现。19楼的游标也可以。