统计当前年份每个小时的数据，求大神解

奇点洋 2019-07-10 03:48:34
求教一条sql，谢谢大家了，是这样的，有一个表叫share_time，有开始时间(start_time)和结束时间(end_time)两个字段，现在，我要统计出，当前年份每个小时的数据，0-24点，比如开始时间是：2019-07-05 16:29:52，结束时间是：2019-07-05 18:00:00，那么，16点数量就要+1，18点的数量就要+1，还有他们之间的区间也得+1，他们之间有个17点，17点的数量就要+1，我现在已经大概写了一半的思路，不过只能统计开始时间的，还有点错误。

下面是我写的sql：那个小时如果一条数据没有的话就给个0，where条件就是查询状态=1的和当前年份
SELECT
a.HOUR HOUR,
ifnull( b.count, 0 ) count
FROM
(
SELECT
0 HOUR UNION ALL
SELECT
1 HOUR UNION ALL
SELECT
2 HOUR UNION ALL
SELECT
3 HOUR UNION ALL
SELECT
4 HOUR UNION ALL
SELECT
5 HOUR UNION ALL
SELECT
6 HOUR UNION ALL
SELECT
7 HOUR UNION ALL
SELECT
8 HOUR UNION ALL
SELECT
9 HOUR UNION ALL
SELECT
10 HOUR UNION ALL
SELECT
11 HOUR UNION ALL
SELECT
12 HOUR UNION ALL
SELECT
13 HOUR UNION ALL
SELECT
14 HOUR UNION ALL
SELECT
15 HOUR UNION ALL
SELECT
16 HOUR UNION ALL
SELECT
17 HOUR UNION ALL
SELECT
18 HOUR UNION ALL
SELECT
19 HOUR UNION ALL
SELECT
20 HOUR UNION ALL
SELECT
21 HOUR UNION ALL
SELECT
22 HOUR UNION ALL
SELECT
23 HOUR
) a
LEFT JOIN (
SELECT HOUR
( start_time) HOUR,
count( start_time) count
FROM
share_time
WHERE
order_status = 1 and YEAR ( start_time ) = YEAR ( NOW( ) )
GROUP BY
date_format( start_time, '%Y%m%d-%H' ) ,
HOUR
) b ON a.HOUR = b.HOUR
ORDER BY
HOUR
...全文
2324 39 打赏 收藏 转发到动态 举报
写回复
用AI写文章
39 条回复
切换为时间正序
请发表友善的回复…
发表回复
纯洁的污神 2019-07-14
  • 打赏
  • 举报
 回复
看不懂真的厉害
yangmeng3280 2019-07-14
  • 打赏
  • 举报
 回复
🤩🤩🤩🤩🤩🤩
javartisan 2019-07-13
  • 打赏
  • 举报
 回复
由年月日小时组成一个字段进行group by统计，很简单
phillip629 2019-07-12
  • 打赏
  • 举报
 回复
向你们学习了
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 23 楼 听雨停了 的回复：
[quote=引用 21 楼 奇点洋 的回复:]
[quote=引用 20 楼 听雨停了 的回复：][quote=引用 19 楼 奇点洋 的回复:]
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了[/quote]有完整呢一套代码嘛，这个任务一直在催我，私下里我再好好看[/quote]
完整的代码就是上面的mysql代码了，mysql用的少，你要再完整的真写不出来了，你自己捋一捋，不要着急[/quote]嗯嗯。好的，我也觉得我有点着急了
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 21 楼 奇点洋 的回复:
[quote=引用 20 楼 听雨停了 的回复：][quote=引用 19 楼 奇点洋 的回复:]
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了[/quote]有完整呢一套代码嘛，这个任务一直在催我，私下里我再好好看[/quote]
完整的代码就是上面的mysql代码了，mysql用的少，你要再完整的真写不出来了，你自己捋一捋，不要着急
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 20 楼 听雨停了 的回复：
[quote=引用 19 楼 奇点洋 的回复:]
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了[/quote]哥，你扣扣或者微信多少呀，以后有啥技术难题请教您下
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 20 楼 听雨停了 的回复：
[quote=引用 19 楼 奇点洋 的回复:]
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了[/quote]有完整呢一套代码嘛，这个任务一直在催我，私下里我再好好看
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 19 楼 奇点洋 的回复:
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 17 楼 听雨停了 的回复：
[quote=引用 10 楼 奇点洋 的回复:] 还有个问题呀，就是，如果开始时间和结束时间垮天了应该咋统计，比如2019-07-10-11:10～2019-07-11-12:00，那这中间的时间也得加到对应的时间里面

--步骤1(把这些数据插入一个表，比如tab)
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

--步骤2（把这个结果插入一个表，比如tab2）
--start_hour 开始小时
--end_hour 结束小时
--days 两个日期的间隔天数-1(这个很重要，理解这里的-1是关键)
SELECT *,
			 hour(start_time)  AS start_hour,
			 hour(end_time)    AS end_hour,
			 DATEDIFF(end_time,start_time) -1 AS days
FROM   share_time;

--步骤3(合并下面两个结果集到一个表，比如tab3)
--3.1(结果集1)
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23; 
--3.2(结果集2)
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour;

--步骤4(把步骤3的结果,通过右连接tab表，按照tab表的number合计总数插入到一个表，比如 tab4)
SELECT b.number,COUNT(a.number) AS cnt
FROM tab3 AS a
RIGHT JOIN tab AS b ON a.number=b.number;


--步骤5(合计步骤2中所有的间隔天数，插入到一个表，比如 tab5)
SELECT SUM(days) AS num FROM tab2

--步骤6(用tab4的结果中的cnt + tab5中的num就得到结果了)
select a.number,b.cnt+b.num AS cnt from tab4 as a
inner JOIN tab5 AS b ON 1=1
逻辑大概就是上面这样的。 

delete from share_time;

insert into share_time values("2019-07-10 08:10","2019-07-11 12:00",1);
insert into share_time values("2019-07-08 11:10","2019-07-11 08:00",1);
insert into share_time values("2019-07-11 08:10","2019-07-11 12:00",1);

select * from share_time;

create TEMPORARY table tab(
	number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

create table tab2(
	SELECT *,
				 hour(start_time)  AS start_hour,
				 hour(end_time)    AS end_hour,
				 DATEDIFF(end_time,start_time) -1 AS days
	FROM   share_time
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23 
);

create table tab4(
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.*,b.num+c.num from (
	select d.number,count(a.number) as num from (
		select * from tab3
		union ALL
		select * from tab4
	) as a
right join tab as d on a.number=d.number
	group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c 
on 1=1 ;
这个是mysql代码，mysql用的少，写的有点乱[/quote]请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 17 楼 听雨停了 的回复：
[quote=引用 10 楼 奇点洋 的回复:] 还有个问题呀，就是，如果开始时间和结束时间垮天了应该咋统计，比如2019-07-10-11:10～2019-07-11-12:00，那这中间的时间也得加到对应的时间里面

--步骤1(把这些数据插入一个表，比如tab)
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

--步骤2（把这个结果插入一个表，比如tab2）
--start_hour 开始小时
--end_hour 结束小时
--days 两个日期的间隔天数-1(这个很重要，理解这里的-1是关键)
SELECT *,
			 hour(start_time)  AS start_hour,
			 hour(end_time)    AS end_hour,
			 DATEDIFF(end_time,start_time) -1 AS days
FROM   share_time;

--步骤3(合并下面两个结果集到一个表，比如tab3)
--3.1(结果集1)
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23; 
--3.2(结果集2)
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour;

--步骤4(把步骤3的结果,通过右连接tab表，按照tab表的number合计总数插入到一个表，比如 tab4)
SELECT b.number,COUNT(a.number) AS cnt
FROM tab3 AS a
RIGHT JOIN tab AS b ON a.number=b.number;


--步骤5(合计步骤2中所有的间隔天数，插入到一个表，比如 tab5)
SELECT SUM(days) AS num FROM tab2

--步骤6(用tab4的结果中的cnt + tab5中的num就得到结果了)
select a.number,b.cnt+b.num AS cnt from tab4 as a
inner JOIN tab5 AS b ON 1=1
逻辑大概就是上面这样的。 

delete from share_time;

insert into share_time values("2019-07-10 08:10","2019-07-11 12:00",1);
insert into share_time values("2019-07-08 11:10","2019-07-11 08:00",1);
insert into share_time values("2019-07-11 08:10","2019-07-11 12:00",1);

select * from share_time;

create TEMPORARY table tab(
	number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

create table tab2(
	SELECT *,
				 hour(start_time)  AS start_hour,
				 hour(end_time)    AS end_hour,
				 DATEDIFF(end_time,start_time) -1 AS days
	FROM   share_time
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23 
);

create table tab4(
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.*,b.num+c.num from (
	select d.number,count(a.number) as num from (
		select * from tab3
		union ALL
		select * from tab4
	) as a
right join tab as d on a.number=d.number
	group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c 
on 1=1 ;
这个是mysql代码，mysql用的少，写的有点乱[/quote]羡慕，逻辑条理清晰，我咋就想不到
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 10 楼 奇点洋 的回复:
还有个问题呀，就是，如果开始时间和结束时间垮天了应该咋统计，比如2019-07-10-11:10～2019-07-11-12:00，那这中间的时间也得加到对应的时间里面

--步骤1(把这些数据插入一个表，比如tab)
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

--步骤2（把这个结果插入一个表，比如tab2）
--start_hour 开始小时
--end_hour 结束小时
--days 两个日期的间隔天数-1(这个很重要，理解这里的-1是关键)
SELECT *,
			 hour(start_time)  AS start_hour,
			 hour(end_time)    AS end_hour,
			 DATEDIFF(end_time,start_time) -1 AS days
FROM   share_time;

--步骤3(合并下面两个结果集到一个表，比如tab3)
--3.1(结果集1)
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23; 
--3.2(结果集2)
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour;

--步骤4(把步骤3的结果,通过右连接tab表，按照tab表的number合计总数插入到一个表，比如 tab4)
SELECT b.number,COUNT(a.number) AS cnt
FROM tab3 AS a
RIGHT JOIN tab AS b ON a.number=b.number;


--步骤5(合计步骤2中所有的间隔天数，插入到一个表，比如 tab5)
SELECT SUM(days) AS num FROM tab2

--步骤6(用tab4的结果中的cnt + tab5中的num就得到结果了)
select a.number,b.cnt+b.num AS cnt from tab4 as a
inner JOIN tab5 AS b ON 1=1
逻辑大概就是上面这样的。 

delete from share_time;

insert into share_time values("2019-07-10 08:10","2019-07-11 12:00",1);
insert into share_time values("2019-07-08 11:10","2019-07-11 08:00",1);
insert into share_time values("2019-07-11 08:10","2019-07-11 12:00",1);

select * from share_time;

create TEMPORARY table tab(
	number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;

create table tab2(
	SELECT *,
				 hour(start_time)  AS start_hour,
				 hour(end_time)    AS end_hour,
				 DATEDIFF(end_time,start_time) -1 AS days
	FROM   share_time
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23 
);

create table tab4(
select * from tab2 as b 
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.*,b.num+c.num from (
	select d.number,count(a.number) as num from (
		select * from tab3
		union ALL
		select * from tab4
	) as a
right join tab as d on a.number=d.number
	group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c 
on 1=1 ;
这个是mysql代码，mysql用的少，写的有点乱
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 35 楼 听雨停了 的回复：
[quote=引用 32 楼 奇点洋 的回复:]
但是你那个里面tab，tab2，tab3，tab4要写呀

沟通咋就这么费劲呢，哎呀[/quote]你看嘛，我这样弄进去的，执行了倒是没错，sql好长：SELECT b.number, b.num + c.num FROM ( SELECT d.number, count( a.number ) AS num FROM ( SELECT * FROM ( SELECT *, HOUR ( start_time ) AS start_hour, HOUR ( end_time ) AS end_hour, DATEDIFF( end_time, start_time ) - 1 AS days FROM share_time WHERE order_status = 1 AND YEAR ( start_time ) = YEAR ( now( ) ) ) AS b INNER JOIN ( SELECT 0 number UNION ALL SELECT 1 number UNION ALL SELECT 2 number UNION ALL SELECT 3 number UNION ALL SELECT 4 number UNION ALL SELECT 5 number UNION ALL SELECT 6 number UNION ALL SELECT 7 number UNION ALL SELECT 8 number UNION ALL SELECT 9 number UNION ALL SELECT 10 number UNION ALL SELECT 11 number UNION ALL SELECT 12 number UNION ALL SELECT 13 number UNION ALL SELECT 14 number UNION ALL SELECT 15 number UNION ALL SELECT 16 number UNION ALL SELECT 17 number UNION ALL SELECT 18 number UNION ALL SELECT 19 number UNION ALL SELECT 20 number UNION ALL SELECT 21 number UNION ALL SELECT 22 number UNION ALL SELECT 23 number ) AS a ON a.number BETWEEN b.start_hour AND 23 UNION ALL SELECT * FROM ( SELECT *, HOUR ( start_time ) AS start_hour, HOUR ( end_time ) AS end_hour, DATEDIFF( end_time, start_time ) - 1 AS days FROM share_time WHERE order_status = 1 AND YEAR ( start_time ) = YEAR ( now( ) ) ) AS b INNER JOIN ( SELECT 0 number UNION ALL SELECT 1 number UNION ALL SELECT 2 number UNION ALL SELECT 3 number UNION ALL SELECT 4 number UNION ALL SELECT 5 number UNION ALL SELECT 6 number UNION ALL SELECT 7 number UNION ALL SELECT 8 number UNION ALL SELECT 9 number UNION ALL SELECT 10 number UNION ALL SELECT 11 number UNION ALL SELECT 12 number UNION ALL SELECT 13 number UNION ALL SELECT 14 number UNION ALL SELECT 15 number UNION ALL SELECT 16 number UNION ALL SELECT 17 number UNION ALL SELECT 18 number UNION ALL SELECT 19 number UNION ALL SELECT 20 number UNION ALL SELECT 21 number UNION ALL SELECT 22 number UNION ALL SELECT 23 number ) AS a ON a.number BETWEEN 0 AND b.end_hour ) AS a RIGHT JOIN ( SELECT 0 number UNION ALL SELECT 1 number UNION ALL SELECT 2 number UNION ALL SELECT 3 number UNION ALL SELECT 4 number UNION ALL SELECT 5 number UNION ALL SELECT 6 number UNION ALL SELECT 7 number UNION ALL SELECT 8 number UNION ALL SELECT 9 number UNION ALL SELECT 10 number UNION ALL SELECT 11 number UNION ALL SELECT 12 number UNION ALL SELECT 13 number UNION ALL SELECT 14 number UNION ALL SELECT 15 number UNION ALL SELECT 16 number UNION ALL SELECT 17 number UNION ALL SELECT 18 number UNION ALL SELECT 19 number UNION ALL SELECT 20 number UNION ALL SELECT 21 number UNION ALL SELECT 22 number UNION ALL SELECT 23 number ) AS d ON a.number = d.number GROUP BY d.number ) AS b INNER JOIN ( SELECT sum( days ) AS num from (SELECT *, HOUR ( start_time ) AS start_hour, HOUR ( end_time ) AS end_hour, DATEDIFF( end_time, start_time ) - 1 AS days FROM share_time WHERE order_status = 1 AND YEAR ( start_time ) = YEAR ( now( ) ))v ) AS c ON 1 = 1
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 32 楼 奇点洋 的回复:
但是你那个里面tab，tab2，tab3，tab4要写呀

沟通咋就这么费劲呢，哎呀
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 32 楼 奇点洋 的回复:
但是你那个里面tab，tab2，tab3，tab4要写呀

create table tab(
number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;


create table tab2(
SELECT *,
hour(start_time) AS start_hour,
hour(end_time) AS end_hour,
DATEDIFF(end_time,start_time) -1 AS days
FROM share_time
where order_status=1 and year(start_time)=year(now())
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23
);

create table tab4(
select * from tab2 as b
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.number,b.num+c.num from (
select d.number,count(a.number) as num from (
select * from tab3
union ALL
select * from tab4
) as a
right join tab as d on a.number=d.number
group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c
on 1=1 ;


drop table tab;
drop table tab2;
drop table tab3;
drop table tab4;

上面那些代码，复制，粘贴，执行，ok？
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 31 楼 听雨停了 的回复：
[quote=引用 30 楼 奇点洋 的回复:]
[quote=引用 26 楼 听雨停了 的回复：]mysql> create table tab(
number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;


create table tab2(
SELECT *,
hour(start_time) AS start_hour,
hour(end_time) AS end_hour,
DATEDIFF(end_time,start_time) -1 AS days
FROM share_time
where order_status=1 and year(start_time)=year(now())
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23
);

create table tab4(
select * from tab2 as b
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.number,b.num+c.num from (
select d.number,count(a.number) as num from (
select * from tab3
union ALL
select * from tab4
) as a
right join tab as d on a.number=d.number
group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c
on 1=1 ;


drop table tab;
drop table tab2;
drop table tab3;
drop table tab4;
Query OK, 0 rows affected

Query OK, 24 rows affected
Records: 24 Duplicates: 0 Warnings: 0

Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

Query OK, 45 rows affected
Records: 45 Duplicates: 0 Warnings: 0

Query OK, 35 rows affected
Records: 35 Duplicates: 0 Warnings: 0

+--------+-------------+
| number | b.num+c.num |
+--------+-------------+
| 0 | 4 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
| 6 | 4 |
| 7 | 4 |
| 8 | 6 |
| 9 | 5 |
| 10 | 5 |
| 11 | 6 |
| 12 | 6 |
| 13 | 4 |
| 14 | 4 |
| 15 | 4 |
| 16 | 4 |
| 17 | 4 |
| 18 | 4 |
| 19 | 4 |
| 20 | 4 |
| 21 | 4 |
| 22 | 4 |
| 23 | 4 |
+--------+-------------+
24 rows in set

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql>
这是我这边的执行结果，你参考下
我按照你写得拼了两百多行sql代码我看到tab3里面有tab2和tab 然后tab4里面也有tab 2和tab[/quote]
你就用上面那一段sql,直接执行就行了（啥也不要改），我这边没有问题的，我用的Navicat for MySQL测试的[/quote]但是你那个里面tab,tab2,tab3，tab4要写呀
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
但是你那个里面tab，tab2，tab3，tab4要写呀
听雨停了 2019-07-11
  • 打赏
  • 举报
 回复
引用 30 楼 奇点洋 的回复:
[quote=引用 26 楼 听雨停了 的回复：]mysql> create table tab(
number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;


create table tab2(
SELECT *,
hour(start_time) AS start_hour,
hour(end_time) AS end_hour,
DATEDIFF(end_time,start_time) -1 AS days
FROM share_time
where order_status=1 and year(start_time)=year(now())
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23
);

create table tab4(
select * from tab2 as b
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.number,b.num+c.num from (
select d.number,count(a.number) as num from (
select * from tab3
union ALL
select * from tab4
) as a
right join tab as d on a.number=d.number
group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c
on 1=1 ;


drop table tab;
drop table tab2;
drop table tab3;
drop table tab4;
Query OK, 0 rows affected

Query OK, 24 rows affected
Records: 24 Duplicates: 0 Warnings: 0

Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

Query OK, 45 rows affected
Records: 45 Duplicates: 0 Warnings: 0

Query OK, 35 rows affected
Records: 35 Duplicates: 0 Warnings: 0

+--------+-------------+
| number | b.num+c.num |
+--------+-------------+
| 0 | 4 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
| 6 | 4 |
| 7 | 4 |
| 8 | 6 |
| 9 | 5 |
| 10 | 5 |
| 11 | 6 |
| 12 | 6 |
| 13 | 4 |
| 14 | 4 |
| 15 | 4 |
| 16 | 4 |
| 17 | 4 |
| 18 | 4 |
| 19 | 4 |
| 20 | 4 |
| 21 | 4 |
| 22 | 4 |
| 23 | 4 |
+--------+-------------+
24 rows in set

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql>
这是我这边的执行结果，你参考下
我按照你写得拼了两百多行sql代码我看到tab3里面有tab2和tab 然后tab4里面也有tab 2和tab[/quote]
你就用上面那一段sql,直接执行就行了（啥也不要改），我这边没有问题的，我用的Navicat for MySQL测试的
奇点洋 2019-07-11
  • 打赏
  • 举报
 回复
引用 26 楼 听雨停了 的回复：
mysql> create table tab(
number int
);
insert into tab
SELECT 0 number UNION ALL
SELECT 1 number UNION ALL
SELECT 2 number UNION ALL
SELECT 3 number UNION ALL
SELECT 4 number UNION ALL
SELECT 5 number UNION ALL
SELECT 6 number UNION ALL
SELECT 7 number UNION ALL
SELECT 8 number UNION ALL
SELECT 9 number UNION ALL
SELECT 10 number UNION ALL
SELECT 11 number UNION ALL
SELECT 12 number UNION ALL
SELECT 13 number UNION ALL
SELECT 14 number UNION ALL
SELECT 15 number UNION ALL
SELECT 16 number UNION ALL
SELECT 17 number UNION ALL
SELECT 18 number UNION ALL
SELECT 19 number UNION ALL
SELECT 20 number UNION ALL
SELECT 21 number UNION ALL
SELECT 22 number UNION ALL
SELECT 23 number;


create table tab2(
SELECT *,
hour(start_time) AS start_hour,
hour(end_time) AS end_hour,
DATEDIFF(end_time,start_time) -1 AS days
FROM share_time
where order_status=1 and year(start_time)=year(now())
);


create table tab3(
select * from tab2 as b
inner join tab as a
on a.number between b.start_hour and 23
);

create table tab4(
select * from tab2 as b
inner join tab as a
on a.number between 0 and b.end_hour
);

select b.number,b.num+c.num from (
select d.number,count(a.number) as num from (
select * from tab3
union ALL
select * from tab4
) as a
right join tab as d on a.number=d.number
group by d.number
)as b
inner join (
select sum(days) as num from tab2
) as c
on 1=1 ;


drop table tab;
drop table tab2;
drop table tab3;
drop table tab4;
Query OK, 0 rows affected

Query OK, 24 rows affected
Records: 24 Duplicates: 0 Warnings: 0

Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

Query OK, 45 rows affected
Records: 45 Duplicates: 0 Warnings: 0

Query OK, 35 rows affected
Records: 35 Duplicates: 0 Warnings: 0

+--------+-------------+
| number | b.num+c.num |
+--------+-------------+
| 0 | 4 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
| 6 | 4 |
| 7 | 4 |
| 8 | 6 |
| 9 | 5 |
| 10 | 5 |
| 11 | 6 |
| 12 | 6 |
| 13 | 4 |
| 14 | 4 |
| 15 | 4 |
| 16 | 4 |
| 17 | 4 |
| 18 | 4 |
| 19 | 4 |
| 20 | 4 |
| 21 | 4 |
| 22 | 4 |
| 23 | 4 |
+--------+-------------+
24 rows in set

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql>
这是我这边的执行结果，你参考下
我按照你写得拼了两百多行sql代码我看到tab3里面有tab2和tab 然后tab4里面也有tab 2和tab
qq_44815533 2019-07-11
  • 打赏
  • 举报
 回复
引用 23 楼 听雨停了 的回复:
[quote=引用 21 楼 奇点洋 的回复:]
[quote=引用 20 楼 听雨停了 的回复：][quote=引用 19 楼 奇点洋 的回复:]
请教下，用inner join的话只会查有的，但是我想那些没有的时间也查出来，就是从0-24，没有数量的就给个默认值0，但是我改为left join之后，那些没有数量的都有个默认值1

步骤4的时候用右连接已经解决了你说的这个问题，你按照上面的步骤，套在你的表中就可以了[/quote]有完整呢一套代码嘛，这个任务一直在催我，私下里我再好好看[/quote]
完整的代码就是上面的mysql代码了，mysql用的少，你要再完整的真写不出来了，你自己捋一捋，不要着急[/quote]
大神，可以加qq986367870不，救急，呜~~~
加载更多回复（19）
R语言实战笔记--第四&五章 数据管理
R语言实战笔记–第四&五章 数据管理标签（空格分隔）： R语言　　第四、第五章都是说的数据管理，合并在一起做个总结，在个人看来，数据管理是一件非常繁琐的事情，但是，每个统计的前提都是一个合适的数据样本，对的，“合适”，做到合适可不是一个简单的事情。 　　本文就不分基本和高级两部分了，合在一起写吧。 　　首先，我们建立一个数据框，这个数据框的数据来源可以随意，我直接使用书上面的例子吧。(PS:ma
Python数据分析及可视化实例目录
等，幸运的是，我们都清楚我使用的方法生成了一个与谷歌很像的图标，相关的趋势都没有被影响，因此对于这个探索，我并不打算尝试去修复。使用一个简单的技巧，创建基于年份的数组，2008 个元素长度意味着每一年的索引等于年份的数字，因此，举个例子，1995 就只是获取 1995 年的元素的问题了。这次探索 确实 展示了，使用 numpy 和 初出茅庐的 pytubes 以及标准的商用硬件和 Python，在合理的时间内从十亿行数据的数据集中加载，处理和提取任意的统计信息是可行的，
python-flask框架-mysql-月新增用户数、当月活跃用户数等的查询及返回数据的逻辑
总用户数、年新增用户、月新增用户、用户活跃度等数据统计实现 用户统计 需求分析 展示当前总人数，月活跃人数，日活跃人数 使用图表的形式展示活跃曲线 实现准备 将 static/admin/user_count.html 拖到 templates/admin/ 目录下 代码实现 实现思路： 月新增数：获取到本月第1天0点0分0秒的时间对象，然后查询最后一次登录比其大的所有数据 日新增数：获取到当日0...
Android自定义view可拖动的柱状图折线图组合可拖动的折线图
公司最近一个需求就是需要每年的每个月的消费概况的统计图 ，因为涉及到一些乱七八糟的需求 。先看图 其中要求本年未到的年份不一样的颜色，且数据不用画出来当然也没有数据。折线下面有个阴影。所以决定自己撸个图。看布局： <com.py.ysl.view.myview.BarGraphView android:id="@+id/bar_view" android...
R语言mad(c(1 2 3 4)),R语言--数据管理-R语言实战笔记-第四&五章
第四、第五章都是说的数据管理，合并在一起做个总结，在个人看来，数据管理是一件非常繁琐的事情，但是，每个统计的前提都是一个合适的数据样本，对的，“合适”，做到合适可不是一个简单的事情。本文就不分基本和高级两部分了，合在一起写吧。首先，我们建立一个数据框，这个数据框的数据来源可以随意，我直接使用书上面的例子吧。(PS:markdown和R的脚本，一大堆的#号~简直想死的心都有了~)首先是数据框建立ma...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章