在SqlServer中有ROW_NUMBER函数
现在我只能实现
ROW_NUMBER() OVER ( ORDER BY INDEX) AS "ROW_NUMBER"
MySql中这么写
SET @r=0;
SELECT (@r:=@r+1) as row, * FROM `users`;
但是
ROW_NUMBER() OVER ( PARTITION BY DESTINATION ORDER BY INDEX) AS "ROW_NUMBER"
中有PARTITION 关键字 分组计算的
这样的MySql如何实现
现在我只能实现
ROW_NUMBER() OVER ( ORDER BY INDEX) AS "ROW_NUMBER"
MySql中这么写
SET @r=0;
SELECT (@r:=@r+1) as row, * FROM `users`;
但是
ROW_NUMBER() OVER ( PARTITION BY DESTINATION ORDER BY INDEX) AS "ROW_NUMBER"
中有PARTITION 关键字 分组计算的
这样的MySql如何实现
from yourTable a
+----+-------------+-------+
| id | DESTINATION | INDEX |
+----+-------------+-------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 1 | 7 |
| 8 | 1 | 8 |
| 9 | 1 | 9 |
| 10 | 1 | 10 |
| 11 | 2 | 11 |
| 12 | 2 | 12 |
| 13 | 2 | 13 |
| 14 | 2 | 14 |
| 15 | 2 | 15 |
| 16 | 2 | 16 |
| 17 | 2 | 17 |
| 18 | 2 | 18 |
| 19 | 2 | 19 |
| 20 | 2 | 20 |
| 21 | 3 | 21 |
| 22 | 3 | 22 |
| 23 | 3 | 23 |
| 24 | 3 | 24 |
| 25 | 3 | 25 |
| 26 | 3 | 26 |
| 27 | 3 | 27 |
| 28 | 3 | 28 |
| 29 | 3 | 29 |
| 30 | 3 | 30 |
+----+-------------+-------+
30 rows in set (0.00 sec)mysql>
mysql> select *,(select count(*) from t_chopper7278 where DESTINATION=a.DESTINATION and `index`<=a.`index`) as ROW_NUMBER
-> from t_chopper7278 a;
+----+-------------+-------+------------+
| id | DESTINATION | INDEX | ROW_NUMBER |
+----+-------------+-------+------------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 3 | 3 |
| 4 | 1 | 4 | 4 |
| 5 | 1 | 5 | 5 |
| 6 | 1 | 6 | 6 |
| 7 | 1 | 7 | 7 |
| 8 | 1 | 8 | 8 |
| 9 | 1 | 9 | 9 |
| 10 | 1 | 10 | 10 |
| 11 | 2 | 11 | 1 |
| 12 | 2 | 12 | 2 |
| 13 | 2 | 13 | 3 |
| 14 | 2 | 14 | 4 |
| 15 | 2 | 15 | 5 |
| 16 | 2 | 16 | 6 |
| 17 | 2 | 17 | 7 |
| 18 | 2 | 18 | 8 |
| 19 | 2 | 19 | 9 |
| 20 | 2 | 20 | 10 |
| 21 | 3 | 21 | 1 |
| 22 | 3 | 22 | 2 |
| 23 | 3 | 23 | 3 |
| 24 | 3 | 24 | 4 |
| 25 | 3 | 25 | 5 |
| 26 | 3 | 26 | 6 |
| 27 | 3 | 27 | 7 |
| 28 | 3 | 28 | 8 |
| 29 | 3 | 29 | 9 |
| 30 | 3 | 30 | 10 |
+----+-------------+-------+------------+
30 rows in set (0.00 sec)mysql>
假设只有两个字段DESTINATION、INDEX
select a.DESTINATION,a.INDEX,count(b.INDEX) from a left join b
on a.DESTINATION=b.a.DESTINATION and a.INDEX<=b.INDEX
on a.DESTINATION=b.a.DESTINATION and a.INDEX <=b.INDEX
group by a.DESTINATION,a.INDEX如果有其它字段,在SELECT、GROUP BY 中加入
Query OK, 0 rows affected (0.00 sec)mysql> set @k=0;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * ,@r:=if(@k=DESTINATION, @r+1,1) as row,@k:=DESTINATION
-> FROM `t_chopper7278`
-> order by DESTINATION,`index`;
+----+-------------+-------+------+-----------------+
| id | DESTINATION | INDEX | row | @k:=DESTINATION |
+----+-------------+-------+------+-----------------+
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 | 1 |
| 3 | 1 | 3 | 3 | 1 |
| 4 | 1 | 4 | 4 | 1 |
| 5 | 1 | 5 | 5 | 1 |
| 6 | 1 | 6 | 6 | 1 |
| 7 | 1 | 7 | 7 | 1 |
| 8 | 1 | 8 | 8 | 1 |
| 9 | 1 | 9 | 9 | 1 |
| 10 | 1 | 10 | 10 | 1 |
| 11 | 2 | 11 | 1 | 2 |
| 12 | 2 | 12 | 2 | 2 |
| 13 | 2 | 13 | 3 | 2 |
| 14 | 2 | 14 | 4 | 2 |
| 15 | 2 | 15 | 5 | 2 |
| 16 | 2 | 16 | 6 | 2 |
| 17 | 2 | 17 | 7 | 2 |
| 18 | 2 | 18 | 8 | 2 |
| 19 | 2 | 19 | 9 | 2 |
| 20 | 2 | 20 | 10 | 2 |
| 21 | 3 | 21 | 1 | 3 |
| 22 | 3 | 22 | 2 | 3 |
| 23 | 3 | 23 | 3 | 3 |
| 24 | 3 | 24 | 4 | 3 |
| 25 | 3 | 25 | 5 | 3 |
| 26 | 3 | 26 | 6 | 3 |
| 27 | 3 | 27 | 7 | 3 |
| 28 | 3 | 28 | 8 | 3 |
| 29 | 3 | 29 | 9 | 3 |
| 30 | 3 | 30 | 10 | 3 |
+----+-------------+-------+------+-----------------+
30 rows in set (0.00 sec)mysql>
SET @INDEX1 = 0;
SET @INDEX2 = 0;
SELECT
d1.OperationDT As StartDate,d1.UserNM,d1.CustomerNM,d1.DetailCD,d1.OperationNM,d1.Memo,
d2.OperationDT As EndDate, TIMEDIFF(d2.OperationDT,d1.OperationDT) As UseTime
FROM
(
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
(@INDEX1 := @INDEX1 + 1) AS IdxNum
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT ) AS d1
LEFT OUTER JOIN
(
SELECT UserCD,OperationDT,ConstructionNO, (
@INDEX2 := @INDEX2 + 1)-1 AS IdxNum
FROM daily_reports
ORDER BY UserCD,OperationDT ) AS d2
ON d1.IdxNum = d2.IdxNum
AND d1.UserCD = d2.UserCD
WHERE d1.ConstructionNO = 10WWWWWWA 我那个INDEX不是原本的表中有的 你看我代码
如果这样怎么写呢?
d1.OperationDT As StartDate,d1.UserNM,d1.CustomerNM,d1.DetailCD,d1.OperationNM,d1.Memo,
d2.OperationDT As EndDate, TIMEDIFF(d2.OperationDT,d1.OperationDT) As UseTime
要对这个SQL语句用PARTITION?另:楼主提问时一定要用真实的SQL语句
(@INDEX1 := @INDEX1 + 1) AS IdxNum
如何加上PARTITION的功能
参照6楼的代码自己改一下吧。 原理应该很清楚了。 无非是把 DESTINATION , index 换成你自己的字段名。
CREATE TABLE LSB AS
SELECT d1.OperationDT As StartDate,d1.UserNM,d1.CustomerNM,d1.DetailCD,d1.OperationNM,d1.Memo,
d2.OperationDT As EndDate, TIMEDIFF(d2.OperationDT,d1.OperationDT) As UseTime,d1.IdxNum
FROM
(
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
(@INDEX1 := @INDEX1 + 1) AS IdxNum
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT ) AS d1
LEFT OUTER JOIN
(
SELECT UserCD,OperationDT,ConstructionNO, (
@INDEX2 := @INDEX2 + 1)-1 AS IdxNum
FROM daily_reports
ORDER BY UserCD,OperationDT ) AS d2
ON d1.IdxNum = d2.IdxNum
AND d1.UserCD = d2.UserCD
WHERE d1.ConstructionNO = 10select a.StartDate,a.UserNM,a.CustomerNM,a.DetailCD,a.OperationNM,a.Memo,a.EndDate,a.UseTime,a.IdxNum,count(b.IdxNum) from LSB
a left join LSB B
ONa.StartDate=B.StartDate AND a.UserNM=B.UserNM AND a.CustomerNM=B.CustomerNM AND a.DetailCD=B.DetailCD AND a.OperationNM=B.OperationNM AND a.Memo=B.Memo AND a.EndDate=B.EndDate AND a.UseTime=B.UseTime a.IdxNum<=B.IdxNumGROUP BY a.StartDate,a.UserNM,a.CustomerNM,a.DetailCD,a.OperationNM,a.Memo,a.EndDate,a.UseTime,a.IdxNum
select a.StartDate,a.UserNM,a.CustomerNM,a.DetailCD,a.OperationNM,a.Memo,a.EndDate,a.UseTime,a.IdxNum,count(b.IdxNum) from LSB
a left join LSB B
ON a.StartDate=B.StartDate AND a.UserNM=B.UserNM AND a.CustomerNM=B.CustomerNM AND a.DetailCD=B.DetailCD AND a.OperationNM=B.OperationNM AND a.Memo=B.Memo AND a.EndDate=B.EndDate AND a.UseTime=B.UseTime and a.IdxNum <=B.IdxNum GROUP BY a.StartDate,a.UserNM,a.CustomerNM,a.DetailCD,a.OperationNM,a.Memo,a.EndDate,a.UseTime,a.IdxNum
ACMAIN_CHM 的方法能实现么?
估计他把我那INDEX理解成原表有的了
那ACMAIN_CHM 的方法能实现么?
如果只判断OperationDT字段
set @eOperationDT='';
set @i=1;
SELECT d1.OperationDT As StartDate,d1.UserNM,d1.CustomerNM,d1.DetailCD,d1.OperationNM,d1.Memo,
d2.OperationDT As EndDate, TIMEDIFF(d2.OperationDT,d1.OperationDT) As UseTime,d1.IdxNum,
@i:=if(@eOperationDT=DESTINATION, @i+1,1) as row,@eOperationDT:=DESTINATIONFROM
(
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
(@INDEX1 := @INDEX1 + 1) AS IdxNum
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT ) AS d1
LEFT OUTER JOIN
(
SELECT UserCD,OperationDT,ConstructionNO, (
@INDEX2 := @INDEX2 + 1)-1 AS IdxNum
FROM daily_reports
ORDER BY UserCD,OperationDT ) AS d2
ON d1.IdxNum = d2.IdxNum
AND d1.UserCD = d2.UserCD
WHERE d1.ConstructionNO = 10
order by IdxNum
仔细看一下六楼的例子,按不同的 DESTINATION 然后按 字段`index`由小到大排序分别赋予行号。把这个字段改成你自己的字段就行了。
set @i=0;
SELECT d1.OperationDT As StartDate,d1.UserNM,d1.CustomerNM,d1.DetailCD,d1.OperationNM,d1.Memo,
d2.OperationDT As EndDate, TIMEDIFF(d2.OperationDT,d1.OperationDT) As UseTime,d1.IdxNum,
@i:=if(@eOperationDT=d1.OperationDT, @i+1,1) as px,@eOperationDT:=d1.OperationDTFROM
(
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
(@INDEX1 := @INDEX1 + 1) AS IdxNum
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT ) AS d1
LEFT OUTER JOIN
(
SELECT UserCD,OperationDT,ConstructionNO, (
@INDEX2 := @INDEX2 + 1)-1 AS IdxNum
FROM daily_reports
ORDER BY UserCD,OperationDT ) AS d2
ON d1.IdxNum = d2.IdxNum
AND d1.UserCD = d2.UserCD
WHERE d1.ConstructionNO = 10
order by IdxNum
SET @PRVE1 = 0;
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,@PRVE1=d.UserCD,
@INDEX1 := if(@PRVE1=d.UserCD,@INDEX1+1,1) AS IdxNum
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT结果row的结果IdxNum是一直是0而@PRVE1:=d.UserCD一直是0
UserCD是char类型
SET @PRVE1 = '';
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
@INDEX1 := if(@PRVE1=d.UserCD,@INDEX1+1,1) AS IdxNum,
@PRVE1=d.UserCD
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT
楼主啊,注意细节!看一下这句和你的有什么不一样?
注意字段赋值的先后次序。
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
@PRVE1=d.UserCD,
@INDEX1 := if(@PRVE1=d.UserCD,@INDEX1+1,1) AS IdxNum
改成
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
@INDEX1 := if(@PRVE1=d.UserCD,@INDEX1+1,1) AS IdxNum,
@PRVE1=d.UserCD
SET @PRVE1 = '';
select *,@INDEX1 := if(@PRVE1=UserCD,@INDEX1+1,1) AS IdxNum,
@PRVE1=UserCD from (
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT) aa;
我知道放在后面的作用是为了下一次的循环比较
但是不理解为什么@PRVE1=d.UserCD的结果是0?难道d.UserCD没有赋值给@PRVE1?
SET @PRVE1 = '';
select *,@INDEX1 := if(@PRVE1=UserCD,@INDEX1+1,1) AS IdxNum,
@PRVE1:=UserCD from (
SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT) aa;
上传B到www.access911.net/csdn
,用WINRAR压缩
SELECT @AA = UserCD FROM daily_reports
或者
SET @AA = '';
SELECT @AA = UserCD FROM daily_reports这样查询都是0!!
SELECT @AA := UserCD FROM daily_reports无所谓,只要得到结果就行,在SELECT中使用变量,必须用:= 赋值
SELECT * ,@r:=if(@k=DESTINATION, @r+1,1) as row,@k:=DESTINATION
而你的则不是!·不了个冒号。SET @INDEX1 = 0;
SET @PRVE1 = 0;SELECT d.UserCD,d.OperationDT,d.ConstructionNO,
u.UserNM,c.CustomerNM,o.DetailCD,o.OperationNM,d.Memo,
@INDEX1 := if(@PRVE1=d.UserCD,@INDEX1+1,1) AS IdxNum,
@PRVE1:=d.UserCD
FROM daily_reports d
LEFT OUTER JOIN users AS u ON d.UserCD = u.UserCD
LEFT OUTER JOIN customers AS c ON d.CustomerCD = c.CustomerCD
LEFT OUTER JOIN operations AS o ON d.OperationCD = o.OperationCD
ORDER BY d.UserCD,d.OperationDT