select t.品牌,sum(a)
from (select distinct day(dt_t3) a , t3.用户浏览ID, t2.品牌
from t3
left join t1
on t3. 商品编码t3 = t1. 商品编码t1 left join t2 on t1. 商品编码t1 = t2. 商品编码t2
where year(dt_t3) = '2019'
) t
group by t.品牌;
select distinct day(dt_t3) , t3.用户浏览ID,t1.品牌
from t3
left join t1
on t3. 商品编码t3 = t1. 商品编码t1
where year(dt_t3);
select* from student;
select * from record_time;
select * from sc;
create table record_time(
sno varchar(10) ,
record_time date);
insert into record_time values ('9512101','2019/1/5');
insert into record_time values ('9512102','2019/6/5');
insert into record_time values ('9512103','2019/1/5');
insert into record_time values ('9512104','2019/10/5');
# 查询近六个月没有违纪的学生
create table b as
select * , sum(grade) 总分
from student left join sc on student.sno1 = sc.sno
where sno1 not in (
select sno from record_time where record_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW())
group by sno1;
select * from b;
# 查询六个月中没有违纪且总分排名在各年级前十的
select sno1,sname,ssex,sdept,总分,rank
from(
select b.*,if(@p = b.sdept,@r := @r+1,@r := 1) rank,@p := b.sdept
from
b,(select @p :=null,@r := 0) r
order by b.sdept,b.总分 desc) m
where rank<=3
;
select sno1,sname,ssex,sdept,总分,rank
from(
select b.*,if(@p = b.sdept,@r := @r+1,@r := 1) rank,@p := b.sdept
from
(select * , sum(grade) 总分
from student left join sc on student.sno1 = sc.sno
where sno1 not in (
select sno from record_time where record_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW())
group by sno1) b,(select @p :=null,@r := 0) r
order by b.sdept,b.总分 desc) m
where rank<=3
;
select m.stu_id,class,总分,rank
from(
select b.*,if(@p = b.class,@r := @r+1,@r := 1) rank,@p := b.class
from
(select * , sum(score) 总分
from stu_table m left join score_table s on m.stu_id = s.stu_id
where m.stu_id not in (
select stu_id from record_time where record_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW())
group by m.stu_id) b,(select @p :=null,@r := 0) r
order by b.calss,b.总分 desc) m
where rank<=10
;
select class , score,rank
from
(
select a.*,if(@p = a.class,@r:=@r+1,@r:=1) rank,@p :=a.class
from student22 a ,(select @p:=null,@r:=0) r
order by a.class asc,a.score desc) z
where rank<=3;
create table student22(
id varchar(20),-- 编号
class varchar(20),-- 年级
score int-- 分数
);
insert student22 values('1','一年级',82);
insert student22 values('2','一年级',95);
insert student22 values('3','一年级',82);
insert student22 values('4','一年级',40);
insert student22 values('5','一年级',20);
insert student22 values('6','二年级',95);
insert student22 values('7','二年级',40);
insert student22 values('8','二年级',3);
insert student22 values('9','二年级',60);
insert student22 values('10','二年级',10);
insert student22 values('11','三年级',70);
insert student22 values('12','三年级',60);
insert student22 values('13','三年级',40);
insert student22 values('14','三年级',90);
insert student22 values('15','三年级',20);
select a.class,a.score from student22 a where (select count(*) from student22 where a.class=class and a.score<score)<4
order by a.class, a.score desc;
# 制造伪列来进行数据的排名
SELECT (@r :=@r + 1) AS rank
FROM (SELECT @r := 0) r
;
# 数据的输入
CREATE TEMPORARY TABLE tm_test (num int , createdat datetime );
INSERT INTO tm_test (num, createdat) VALUES (1, '2018/12/28 21:41:20');
INSERT INTO tm_test (num, createdat) VALUES (1, '2018/12/28 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (1, '2018/12/28 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (1, '2018/12/28 21:43:20');
INSERT INTO tm_test (num, createdat) VALUES (1, '2018/12/28 21:44:20');
INSERT INTO tm_test (num, createdat) VALUES (2, '2018/12/29 21:41:20');
INSERT INTO tm_test (num, createdat) VALUES (2, '2018/12/29 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (2, '2018/12/29 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (2, '2018/12/29 21:43:20');
INSERT INTO tm_test (num, createdat) VALUES (2, '2018/12/29 21:44:20');
INSERT INTO tm_test (num, createdat) VALUES (3, '2018/12/30 21:41:20');
INSERT INTO tm_test (num, createdat) VALUES (3, '2018/12/30 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (3, '2018/12/30 21:42:20');
INSERT INTO tm_test (num, createdat) VALUES (3, '2018/12/30 21:43:20');
INSERT INTO tm_test (num, createdat) VALUES (3, '2018/12/30 21:44:20');
SELECT * FROM tm_test;
# 根据某一列进行排名
SELECT a.*,(@r :=@r + 1) AS rank
FROM tm_test a,(SELECT @r := 0) r
ORDER BY createdat;
SELECT num,createdat,rank
FROM
(
SELECT a.*,IF(@p=a.num,@r:=@r+1,@r:=1) AS rank, @p:= a.num
FROM tm_test a,(SELECT @p:=NULL,@r:=0)r
ORDER BY a.num,a.createdat
)z
where rank<=3;
select * from student22;
select a.*,(select @m := @m +1) rank
from student22 a,(select @m := 0) m
order by score;
select class , score,rank
from
(
select a.*,if(@p = a.class,@r:=@r+1,@r:=1) rank,@p :=a.class
from student22 a ,(select @p:=null,@r:=0) r
order by a.class asc,a.score desc) z
where rank<=3;
create table TEST_ROW_NUMBER_OVER(
id varchar(10) not null,
name varchar(10) null,
age varchar(10) null,
salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);
# 对查询结果进行排序（无分组）
select b.*,(select @r :=@r +1) rank
from TEST_ROW_NUMBER_OVER b,(select @r := 0 ) r
order by salary desc;
# 根据id分组排序
select id,name,age,salary,rank
from (
select b.* , if(@p = id,@r := @r+1,@r :=1) rank ,@p:=b.id
from TEST_ROW_NUMBER_OVER b,(select @r := 0,@p:=null) r
order by id,salary) z;
# 找出每一组中序号为一的数据
select id,name ,age ,salary,rank
from(
select b.*,if(@p=id,@r:=@r+1,@r:=1) rank,@p:=id
from TEST_ROW_NUMBER_OVER b,(select @p := null,@r := 0) r
order by id,salary) z
where rank <= 1;
# 排序找出年龄在13岁到16岁数据，按salary排序
select *
from TEST_ROW_NUMBER_OVER b
where age between 13 and 16
order by salary desc;
SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m');
select date_format(now(),'%Y-%m ');
