有没有写SQL语句的大神过来看个问题

Hd_vip 2016-05-12 11:36:32
话不多说直接上图

如图所示，要求：
1.查询出每个用户的当天的最新两条记录
2.如果有个用户的当天记录中status_other = 1的数量为1，则显示这个用户的最新一条记录
3.如果有个用户的当天记录中status_other = 1的数量大于等于2，则不显示这个用户


SET FOREIGN_KEY_CHECKS=0;



-- ----------------------------

-- Table structure for record

-- ----------------------------

DROP TABLE IF EXISTS `record`;

CREATE TABLE `record` (

  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` varchar(30) NOT NULL,

  `status` int(1) unsigned NOT NULL,

  `status_other` int(1) unsigned NOT NULL,

  `message` varchar(255) DEFAULT NULL,

  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of record

-- ----------------------------

INSERT INTO `record` VALUES ('1', 'user1', '1', '0', '用户1失败了，而且没有人管', '2016-05-10 15:40:28');

INSERT INTO `record` VALUES ('2', 'user1', '1', '0', '用户1失败了，而且没有人管', '2016-05-10 16:22:34');

INSERT INTO `record` VALUES ('3', 'user1', '1', '0', '用户1失败了，而且没有人管', '2016-05-10 16:40:41');

INSERT INTO `record` VALUES ('4', 'user2', '1', '0', '用户2失败了，而且没有人管', '2016-05-10 16:42:45');

INSERT INTO `record` VALUES ('5', 'user2', '1', '0', '用户2失败了，而且没有人管', '2016-05-10 16:43:07');

INSERT INTO `record` VALUES ('6', 'user2', '1', '1', '用户2失败了，但是有人管', '2016-05-10 16:43:33');

INSERT INTO `record` VALUES ('7', 'user3', '1', '0', '用户3失败了，而且没有人管', '2016-05-10 16:44:06');

INSERT INTO `record` VALUES ('8', 'user3', '1', '1', '用户3失败了，但是有人管', '2016-05-10 16:44:15');

INSERT INTO `record` VALUES ('9', 'user3', '1', '1', '用户3失败了，但是有人管', '2016-05-10 16:44:24');

INSERT INTO `record` VALUES ('10', 'user3', '1', '0', '用户3失败了，而且没有人管', '2016-05-10 17:57:02');



按照上面要求，应该显示的结果就是蓝色框出来的记录，目前我只完成了1、3两个要求


SELECT

	a.user_id,

	a.message,

	a.status_other,

	a.create_time

FROM

	record a

WHERE

	-- 排除status_other=1大于等于两条记录

	a.user_id NOT IN (

		SELECT

			c.user_id

		FROM

			(

				SELECT

					b.user_id,

					count(*) AS num

				FROM

					record b

				WHERE

					b.status_other = 1

				GROUP BY

					b.user_id

				HAVING

					num >= 2

			) c

	) 

-- 查询最新的两条记录

AND 2 > (

	SELECT

		count(*)

	FROM

		record b

	WHERE

		a.user_id = b.user_id

	AND a.create_time < b.create_time

)


怎么限制第二步要求啊，求大神帮忙
...全文
230 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
Hd_vip 2016-05-12
  • 打赏
  • 举报
 回复
哇，居然不知不觉中写出来了，就是感觉好啰嗦，有没有大神帮助优化一下 

SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time
FROM
	record a
WHERE
	-- 排除status_other=1大于等于两条记录
	a.user_id NOT IN (
		SELECT
			c.user_id
		FROM
			(
				SELECT
					b.user_id,
					count(*) AS num
				FROM
					record b
				WHERE
					b.status_other = 1
				GROUP BY
					b.user_id
				HAVING
					num >= 2
			) c
	) -- 查询status_other=1等于一条记录
AND 1 > (
	SELECT
		count(*)
	FROM
		record b
	WHERE
		a.user_id = b.user_id
	AND b.user_id IN (
		SELECT
			c.user_id
		FROM
			(
				SELECT
					b.user_id,
					count(*) AS num
				FROM
					record b
				WHERE
					b.status_other = 1
				GROUP BY
					b.user_id
				HAVING
					num = 1
			) c
	)
	AND b.status_other = 0
	AND a.create_time < b.create_time
) -- 查询最新的两条记录
AND 2 > (
	SELECT
		count(*)
	FROM
		record b
	WHERE
		a.user_id = b.user_id
	AND b.user_id NOT IN (
		SELECT
			c.user_id
		FROM
			(
				SELECT
					b.user_id,
					count(*) AS num
				FROM
					record b
				WHERE
					b.status_other = 1
				GROUP BY
					b.user_id
				HAVING
					num >= 1
			) c
	)
	AND a.create_time < b.create_time
)
AND a.status_other = 0

order by user_id,create_time desc
Hd_vip 2016-05-12
  • 打赏
  • 举报
 回复
引用 8 楼 yupeigu 的回复:
[quote=引用 7 楼 Hd_vip 的回复:] [quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 Hd_vip 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 我写了一个完整过滤的，你试试，应该可以了： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2
这个结果不对吧！应该用户1显示出来2条最新的，用户2显示出来1条最新的[/quote] 那就改一下呗： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2-aa.c1
[/quote] 我理解了你sql语句的思路了，但是你这sql存在个问题——总是展现rn=1的数据，而忽略了rn=1时status_other的状态，比如我插入一条user_id = user1并且status_other=1的数据，那么会把这条数据展现出来 应该展现的是绿色框中的user1数据，我想在你的sql基础上改进一下，但是尝试了半天都没成功，你再看一下[/quote] 这个要看你的实际需求，如果你根本不想要显示出 status_other=1的数据，那就直接过滤吧，类似这样; 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time and aa.status_other= '0') as rn
FROM record a
where status_other= '0'
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2-aa.c1
[/quote] 哇，大神！收下我的膝盖，实在是太厉害了
LongRui888 2016-05-12
  • 打赏
  • 举报
 回复
引用 7 楼 Hd_vip 的回复:
[quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 Hd_vip 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 我写了一个完整过滤的，你试试，应该可以了： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2
这个结果不对吧！应该用户1显示出来2条最新的，用户2显示出来1条最新的[/quote] 那就改一下呗： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2-aa.c1
[/quote] 我理解了你sql语句的思路了，但是你这sql存在个问题——总是展现rn=1的数据，而忽略了rn=1时status_other的状态，比如我插入一条user_id = user1并且status_other=1的数据，那么会把这条数据展现出来 应该展现的是绿色框中的user1数据，我想在你的sql基础上改进一下，但是尝试了半天都没成功，你再看一下[/quote] 这个要看你的实际需求，如果你根本不想要显示出 status_other=1的数据，那就直接过滤吧，类似这样; 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time and aa.status_other= '0') as rn
FROM record a
where status_other= '0'
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2-aa.c1
Hd_vip 2016-05-12
  • 打赏
  • 举报
 回复
引用 6 楼 yupeigu 的回复:
[quote=引用 4 楼 Hd_vip 的回复:]
[quote=引用 3 楼 yupeigu 的回复:]

我写了一个完整过滤的，你试试，应该可以了：

select *

from 

(

SELECT

	a.user_id,

	a.message,

	a.status_other,

	a.create_time,

	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn

FROM record a

)a

inner join

(

select user_id,count(case when status_other = '1' then 1 end) c1

from record a

group by user_id

having c1 <=1 

)aa

on a.user_id = aa.user_id

where a.rn <=2




这个结果不对吧！应该用户1显示出来2条最新的，用户2显示出来1条最新的[/quote]

那就改一下呗：

select *

from 

(

SELECT

	a.user_id,

	a.message,

	a.status_other,

	a.create_time,

	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn

FROM record a

)a

inner join

(

select user_id,count(case when status_other = '1' then 1 end) c1

from record a

group by user_id

having c1 <=1 

)aa

on a.user_id = aa.user_id

where a.rn <=2-aa.c1
[/quote]

我理解了你sql语句的思路了，但是你这sql存在个问题——总是展现rn=1的数据，而忽略了rn=1时status_other的状态，比如我插入一条user_id = user1并且status_other=1的数据，那么会把这条数据展现出来

应该展现的是绿色框中的user1数据，我想在你的sql基础上改进一下，但是尝试了半天都没成功，你再看一下
LongRui888 2016-05-12
  • 打赏
  • 举报
 回复
引用 4 楼 Hd_vip 的回复:
[quote=引用 3 楼 yupeigu 的回复:] 我写了一个完整过滤的，你试试，应该可以了： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2
这个结果不对吧！应该用户1显示出来2条最新的，用户2显示出来1条最新的[/quote] 那就改一下呗： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2-aa.c1
wujie408961011 2016-05-12
  • 打赏
  • 举报
 回复
select * from ( SELECT a.user_id, a.message, a.status_other, a.create_time, (select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn, IF(IFNULL((select count(*) from record aa where aa.user_id = a.user_id and aa.status_other = 1 group by aa.user_id),0) >= 2,'1','0') as s,-- 是否显示 IF(IFNULL((select count(*) from record aa where aa.user_id = a.user_id and aa.status_other = 1 group by aa.user_id),0) = 1,1,2) as num -- 显示几条 FROM record a )a where rn <= num and s = '0';
Hd_vip 2016-05-12
  • 打赏
  • 举报
 回复
引用 3 楼 yupeigu 的回复:
我写了一个完整过滤的，你试试，应该可以了： 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
inner join
(
select user_id,count(case when status_other = '1' then 1 end) c1
from record a
group by user_id
having c1 <=1 
)aa
on a.user_id = aa.user_id
where a.rn <=2
这个结果不对吧！应该用户1显示出来2条最新的，用户2显示出来1条最新的
LongRui888 2016-05-12
  • 打赏
  • 举报
 回复

我写了一个完整过滤的，你试试，应该可以了：

select *

from 

(

SELECT

	a.user_id,

	a.message,

	a.status_other,

	a.create_time,

	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn

FROM record a

)a

inner join

(

select user_id,count(case when status_other = '1' then 1 end) c1

from record a

group by user_id

having c1 <=1 

)aa

on a.user_id = aa.user_id

where a.rn <=2


LongRui888 2016-05-12
  • 打赏
  • 举报
 回复
哇塞，好复杂 这个代码是取最新的2条数据，其他的限制条件，你再看着网上加吧 
select *
from 
(
SELECT
	a.user_id,
	a.message,
	a.status_other,
	a.create_time,
	(select count(*) from record aa where aa.user_id = a.user_id and aa.create_time >=a.create_time) as rn
FROM record a
)a
where rn <=2
【机房重构】SQL语句已终止
在下机将消费时间写入数据库时，出现了一个没有遇到过的错误，用了将近一下午的时间才改正过来，其实出错的原因也很简单。                                                 “语句已终止”，首先想到的是自己没有那个能力将更新语句写成终止语句吧！思维往这个方向偏，就忽略了本身导致问题的原因。思维越来越偏，甚至怀疑是自己写的SQL语句导致数据库死循环了，真
通过Excel生成批量SQL语句
项目中有时会遇到这样的要求：用户给发过来一些数据，要我们直接给存放到数据库里面，有的是Insert，有的是Update等等，少量的数据我们可以采取最原始的办法，也就是在SQL里面用Insert into来实现，但是如果有几十条几百条甚至上千条数据的时候继续写单独的SQL语句的话那就惨了，其实有两种简单的方法； 第一，将Excel数据整理好了之后，通过SQL的导入功能直接导进数据...
mysql sql语句优化面试_面试中被问到SQL优化，看这篇就对了！
原标题：面试中被问到SQL优化，看这篇就对了！前言本文主要针对的是关系型数据数据库MySql。键值类数据库可以参考：https://www.jianshu.com/p/098a870d83e4先简单梳理下Mysql的基本概念，然后分创建时和查询时这两个阶段的优化展开。1 基本概念简述1.1 逻辑架构 第一层：客户端通过连接服务，将要执行的sql指令传输过来第二层：服务器解析并优化sql，生成最终的...
sql语句递归查询（start with）
在做项目中遇到一个问题，就是同一个表中的数据存在级联关系，但是只要查出来的末级数据，纠结了好久，好不容易找到了一个博主的分享，在这里做个记录，也是和大家一起分享学习一下这位大神的技术，共勉 写代码时碰到要弄清楚Oracle的role之间的传递关系,就是有role A的话,可以通过grant A t...
oracle取下个月数据,大神看过来，Oracle数据库用sql取下月第一天怎么解？
最近小编在学习oracle数据库，今天在学习sql语句时突然想用刚学习的oracle数据库函数来取出下个月的第一天是具体多少号。小编首先想到的是用oracle数据库自带的函数NEXT_DAY(date,char) 其中date参数为日期型， char:为1~7或Monday/Mon~Sunday/。小编写的sql语句时这样的：select next_day(last_day(sysdate),1)...
MySQL

56,679

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章