#创建数字辅助表 CREATE TABLE nums( a INT UNSIGNED NOT NULL PRIMARY KEY )ENGINE=INNODB; CREATE PROCEDURE pCreateNums(n INT UNSIGNED) BEGIN DECLARE s INT UNSIGNED DEFAULT 1; TRUNCATE TABLE NUMS; WHILE s <= n DO BEGIN INSERT INTO nums SELECT s; SET s = s+1; END; END WHILE; END;#往辅助表中插入数据 CALL pCreateNums(1000); #测试数据 Create table `AAA`(`a列` varchar(21),`b列` int,`c列` int); Insert into AAA select N'A',1,5 union all select N'B',3,6;#解决方案 Select a.a列,b.a from AAA a inner join nums b where b.a between a.b列 and a.c列
区间跨度有限的时候,可以直接借助系统表 Create table `AAA`(`a列` varchar(21),`b列` int,`c列` int); Insert into AAA select N'A',1,5 union all select N'B',3,6;SELECT a.a列, a.b列 + b.id as id FROM AAA a, ( SELECT @id:=@id+1 as id FROM information_schema.columns, (SELECT @id:=-1) _ ) b WHERE B.id <= A.c列 - A.b列 ORDER BY a.A列, B.id
各位大侠,用Hive知道怎么实现吗?
各位大侠,用Hive知道怎么实现吗?
各位大侠,用Hive知道怎么实现吗?
INSERT INTO trace(te_id,te_fy_id,te_u_id,te_fy_creationtime,te_last_second) SELECT NULL,2,3,NOW(),UNIX_TIMESTAMP(NOW()) FROM trace WHERE (SELECT te_last_second + 600 FROM trace ORDER BY te_last_second DESC LIMIT 1) < UNIX_TIMESTAMP(NOW()) ORDER BY te_last_second DESC LIMIT 1这里的表 id 字段是自增主键,所有主键的值使用 null 让sql 自增,由于insert into 语句不支持 where语句,则使用insert into 支持子查询的特性,在插入数据的时候判断时间,时间自己设定,我这里演示的是10分钟,(te_last_second + 600) 该字段为 bigint 字段,在插入时,查询出该字段用倒叙排列使用 limit 取出第一个,用第一个值加上设定的时间值,大于当前时间则插入数据。
SELECT AAA.A,T.R D FROM ( SELECT 'A' A, 1 B,5 C FROM DUAL UNION ALL SELECT 'B' A, 3 B,6 C FROM DUAL ) AAA LEFT JOIN (SELECT @R:=@R +1 R FROM ( SELECT @R:=0 R FROM INFORMATION_SCHEMA.COLUMNS) T) T ON T.R BETWEEN AAA.B AND AAA.C ORDER BY AAA.A,T.R ;
#创建数字辅助表
CREATE TABLE nums(
a INT UNSIGNED NOT NULL PRIMARY KEY
)ENGINE=INNODB; CREATE PROCEDURE pCreateNums(n INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE NUMS;
WHILE s <= n DO
BEGIN
INSERT INTO nums SELECT s;
SET s = s+1;
END;
END WHILE;
END;#往辅助表中插入数据
CALL pCreateNums(1000); #测试数据
Create table `AAA`(`a列` varchar(21),`b列` int,`c列` int);
Insert into AAA
select N'A',1,5 union all
select N'B',3,6;#解决方案
Select a.a列,b.a from AAA a
inner join nums b
where b.a between a.b列 and a.c列
Create table `AAA`(`a列` varchar(21),`b列` int,`c列` int);
Insert into AAA
select N'A',1,5 union all
select N'B',3,6;SELECT a.a列, a.b列 + b.id as id
FROM AAA a, ( SELECT @id:=@id+1 as id FROM information_schema.columns, (SELECT @id:=-1) _ ) b
WHERE B.id <= A.c列 - A.b列
ORDER BY a.A列, B.id
SELECT NULL,2,3,NOW(),UNIX_TIMESTAMP(NOW()) FROM trace
WHERE (SELECT te_last_second + 600 FROM trace ORDER BY te_last_second DESC LIMIT 1) < UNIX_TIMESTAMP(NOW()) ORDER BY te_last_second DESC LIMIT 1这里的表 id 字段是自增主键,所有主键的值使用 null 让sql 自增,由于insert into 语句不支持 where语句,则使用insert into 支持子查询的特性,在插入数据的时候判断时间,时间自己设定,我这里演示的是10分钟,(te_last_second + 600) 该字段为 bigint 字段,在插入时,查询出该字段用倒叙排列使用 limit 取出第一个,用第一个值加上设定的时间值,大于当前时间则插入数据。
SELECT 'A' A, 1 B,5 C FROM DUAL UNION ALL
SELECT 'B' A, 3 B,6 C FROM DUAL ) AAA
LEFT JOIN
(SELECT @R:=@R +1 R FROM (
SELECT @R:=0 R FROM INFORMATION_SCHEMA.COLUMNS) T) T ON
T.R BETWEEN AAA.B AND AAA.C ORDER BY AAA.A,T.R ;