mysql优化_mysql sql优化_mysql优化面试题(8)

(null,'105','1988-1-1',100),

(null,'105','1988-2-1',80),

(null,'105','1988-3-1',120),

(null,'105','1988-4-1',100),

(null,'105','1988-5-1',100),

(null,'105','1988-6-1',100),

(null,'105','1988-7-1',100),

(null,'105','1988-8-1',100);

答案：

select distinct AccID from TestDB

where AccID not in

(select TestDB.AccIDfrom TestDB,

(select * from TestDB where AccID='101') as db101

where TestDB.Occmonth=db101.Occmonth andTestDB.DebitOccur<=db101.DebitOccur

);

4.统计每年每月的信息

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

查成这样一个结果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

提示：这个与工资条非常类似，与学生的科目成绩也很相似。

准备sql语句：

drop table if exists sales;

create table sales(id int auto_increment primary key,yearvarchar(10), month varchar(10), amount float(2,1));

insert into sales values

(null,'1991','1',1.1),

(null,'1991','2',1.2),

(null,'1991','3',1.3),

(null,'1991','4',1.4),

(null,'1992','1',2.1),

(null,'1992','2',2.2),

(null,'1992','3',2.3),

(null,'1992','4',2.4);

答案一、

select sales.year ,

(select t.amount from sales t where t.month='1' and t.year=sales.year) '1',

(select t.amount from sales t where t.month='1' and t.year=sales.year) '2',

(select t.amount from sales t where t.month='1' and t.year=sales.year) '3',

(select t.amount from sales t where t.month='1' and t.year=sales.year) as '4'

from sales group by year;

表：id,title,postuser,postdate,parentid

准备sql语句：

drop table if exists articles;

create table articles(id int auto_increment primary key,titlevarchar(50), postuser varchar(10), postdate datetime,parentid intreferences articles(id));

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-34607-8.html