下面的行列转换等各种小算法都可以用代码逻辑实现,姑且把sql当成一种工具,方便了些,思维却也少了些
create table testTable2(
sid int primary key identity(1,1),
name varchar(20) null,
class varchar(20) null,
age varchar(20) null,
)
insert into testTable values('张三', '工学一班', '22');
insert into testTable values('李四', '工学二班', '21');
insert into testTable values('赵五', '工学三班', '23');
insert into testTable values('刘六', '工学四班', '24');
create table [practise1]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段7] image null ,
)
insert into practise values('1','2019-07-18','4','8','9');
insert into practise values('2','2019-07-18','1','2','3');
insert into practise values('3','2019-07-18','5','6','7');
insert into practise values('4','2019-07-18','4','4','4');
insert into practise values('5','2019-07-18','6','7','9');
select * from testTable;
select * from practise;
select s.name,s.class,c.字段2,c.字段3 from testTable s left join practise c on c.自动编号字段 = s.sid+'1';
delete practise where convert(nvarchar(255),[字段2]) ='1'or convert(nvarchar(255),[字段2])='2';
select *from practise;
select * from testTable;
select a.sid,a.name,a.class,a.age,b.字段2,b.字段3 from testTable a left join practise b on 自动编号字段=sid;
create table one1 (
name varchar(20) null,
kecheng varchar(20) null,
fenshu varchar(20) null,
)
insert into one values('张三','语文','81');
insert into one values('张三','数学','75');
insert into one values('李四','语文','76');
insert into one values('李四','数学','90');
insert into one values('王五','语文','81');
insert into one values('王五','数学','100');
insert into one values('王五','英语','90');
UPDATE one SET fenshu = '85' WHERE fenshu = '76';
select *from one ;
SELECT S.name FROM one S GROUP BY S.name Having MIN(S.fenshu)>=80
select *from one where(( name='张三' and kecheng='数学') or (name='王五'and kecheng='语文')) and one.fenshu>76;
select S.name from one S group by S.name having min(S.fenshu)>80;
select S.name,S.fenshu,S.kecheng from one S group by S.name,S.fenshu,S.kecheng having min(S.fenshu)>80;
create table people(
[自动编号] int IDENTITY (1,1) PRIMARY KEY ,
[学号] varchar(20) null ,
[姓名] varchar(20) null,
[课程编号] varchar(20) null ,
[课程名称] varchar(20) null,
[分数] int null ,
)
insert into people values('2005001','张三','0001','数学','69');
insert into people values('2005002','李四','0002','数学','89');
insert into people values('2005001','张三','0001','数学','69');
select *from people;
delete people where 自动编号 not in (select min(自动编号) from people
group by 学号,姓名,课程编号,课程名称,分数);
select *from people;
select *from people where 姓名='李四';
delete from people where 姓名='张三';
insert into people values('','','','');
select S.姓名 from people S group by S.姓名 having min(S.分数)>80;
create table testtable1(
number int identity(1,1) primary key,
fenshu varchar(20) null,
name varchar(20) null,
)
insert into testtable1 values('89','张三');
insert into testtable1 values('91','李四');
select top 1 [number],[fenshu],[name],[number] from [testtable1];
delete from testtable1 where fenshu='89';
select top 1 [number],[fenshu],[name],[number] from [testtable1];
select *from ACC_CODE_LIST;
insert into acc_code_list values('2019-01-01','line ft','2','loaddb','byte','0');
delete from acc_code_list where codeid='2';
select *from ACC_CODE_LIST;
create table testtable3(
year int null,
month varchar(20) null,
amount varchar(20) null,
)
insert testtable3 values('1991','1','1.1');
insert testtable3 values('1991','2','1.2');
insert testtable3 values('1991','3','1.3');
insert testtable3 values('1991','4','1.4');
insert testtable3 values('1992','1','2.1');
insert testtable3 values('1992','2','2.2');
insert testtable3 values('1992','3','2.3');
insert testtable3 values('1992','4','2.4');
select *from testtable3;
select year,
(select amount from testtable3 m where month=1 and m.year=testtable3.year) as m1,
(select amount from testtable3 m where month=2 and m.year=testtable3.year) as m2,
(select amount from testtable3 m where month=3 and m.year=testtable3.year) as m3,
(select amount from testtable3 m where month=4 and m.year=testtable3.year) as m4
from testtable3 group by year
select year,amount from testtable3 group by year;
select * into d from testtable3 where 1<>1;
select *from d;