SQL 查询学习
1、表数据如下
ID stuid status
1 100 1
3 200 1
4 2343 1
5 52 3
6 42 5
7 333 1
想得到下面结果
stuid 总数
100,200,2343,333 4
首先创建试验表
1 with tablea as 2 ( 3 select 1 as id,100 as stuid,1 as status union all 4 select 3 ,200,1 union all 5 select 4 ,2343,1 union all 6 select 5 ,52,3 union all 7 select 6 ,42,5 union all 8 select 7 ,333,1 )
SQL 语句实现
SELECT stuff(Select ',' +convert(nvarchar(100),stuid) from tablea where status=1 order by stuid FOR XML PATH('')),1,1,'')
AS stuid,count(*) as 总数 from tablea as a where status=1
2、
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句
解决如下 :
1 with tablea as 2 ( 3 select 1 as courseid ,'java' as coursename , 70 as score union all 4 select 2,'oracle',90 union all 5 select 3,'xml',40 union all 6 select 4,'jsp',30 union all 7 select 5,'servlet',80 8 ) 9 10 select courseid,coursename,score, 'mark'= CASE 11 WHEN score >=60 THEN 'pass' 12 WHEN score < 60 THEN 'fail' 13 14 END 15 16 from tablea
开始看到题目,有人解答时用decode()函数,跟着操作了一遍,发现行不通,因为SQL SERVER 没有decode函数。decode是Oracle函数。
查找case函数,发现也能解决。
3、
如何取sta列连续中的一行
SQL实现如下
1 IF OBJECT_ID('test', 'U') IS NOT NULL 2 DROP TABLE test 3 GO 4 CREATE TABLE test 5 ( 6 id INT IDENTITY(1,1), 7 sta INT, 8 [Time] VARCHAR(10) 9 ) 10 INSERT INTO test 11 SELECT 1, '1:00:01' UNION ALL 12 SELECT 1, '1:00:02' UNION ALL 13 SELECT 2, '1:00:03' UNION ALL 14 SELECT 2, '1:00:04' UNION ALL 15 SELECT 1, '1:00:05' UNION ALL 16 SELECT 2, '1:00:06' UNION ALL 17 SELECT 1, '1:00:06' UNION ALL 18 SELECT 1, '1:00:06' UNION ALL 19 SELECT 2, '1:00:06' UNION ALL 20 SELECT 1, '1:00:06' UNION ALL 21 SELECT 1, '1:00:06' UNION ALL 22 SELECT 2, '1:00:06' 23 GO 24 SELECT * FROM test 25 26 27 GO 28 SELECT MIN(t.id) id, t.sta, MIN(t.[Time]) AS [Time] 29 FROM 30 ( 31 SELECT a.id, a.sta, a.[Time], (a.id - a.row) col 32 FROM 33 ( 34 select id, sta, [Time], Row_Number() OVER (ORDER BY sta) as row 35 from test 36 ) a 37 )t 38 GROUP BY t.col, t.sta 39 ORDER BY id
4、
原表:
id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表:
id pro1 pro2
1 M F
2 N G
3 B A
SQL实现如下
with tablea as ( select 1 as id,1 as proid , 'M' as proname union all select 1,2,'F' union all select 2,1,'N' union all select 2,2,'G' union all select 3,1,'B' union all select 3,2,'A' ) select distinct id , (select proname from tablea a where proid = 1 and a.id=tablea.id) as pro1, (select proname from tablea a where proid = 2 and a.id=tablea.id) as pro2 from tablea
5、/**********
sql求解
表a
列 a1 a2
记录 1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗?
1 ab
2 xyz
***************/
SQL 用FOR XML PATH()实现
1 with a as 2 ( 3 select 1 as a1 ,'a' as a2 union all 4 select 1,'b' union all 5 select 2,'x' union all 6 select 2,'y' union all 7 select 2,'z' 8 ) 9 select distinct 10 (select ''+a2 from a where a1=1 FOR XML PATH('')) as '1', 11 (select ''+a2 from a where a1=2 FOR XML PATH('')) as '2' 12 from a
可以参考:http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html
6、行专列与列转行 详解
http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html
7/3,获取不同name的最小的year和最小的month的其中的id字段
比如说:
year month id name
2014 10 1 a
2014 9 2 a
2013 12 3 a
2013 1 4 a
2013 2 5 a
2014 10 6 b
2015 9 7 b
2012 12 8 b
2012 1 9 b
2012 2 10 b
2014 10 11 b
2015 11 12 b
2013 12 13 c
2013 1 14 c
2013 2 15 c
好了, 我要查出每个人的最小year和最小month的id, 像下面一样
year month id name
2013 1 4 a
2012 1 9 b
2013 1 14 c
with tablesa as ( select 2014 as years,10 as months,1 as id , 'a' as name union all select 2014,9,2,'a' union all select 2013,12,3,'a' union all select 2013,1,4,'a' union all select 2013,2,5,'a' union all select 2014,10,6,'b' union all select 2015,9,7,'b' union all select 2012,12,8,'b' union all select 2012,1,9,'b' union all select 2012,2,10,'b' union all select 2014,10,11,'b' union all select 2015,11,12,'b' union all select 2013,12,13,'c' union all select 2013,1,14,'c' union all select 2013,2,15,'c' ) --select * from tablesa select * from (select *,row_number()over(partition by name order by years ) as n from tablesa) t where t.n=1
8、如图,如何将表A和表B合并成表C。记得sql有一个关键字可以实现,可怎么就是想不起来了。
(不使用ISNULL()来设定固定值)
实现如下:
1 create table 表A(Size_no varchar(5),Size_name int) 2 create table 表B(Cate_no varchar(10),Size_no varchar(5),Size_name int,Qty int) 3 insert into 表A 4 select 'S1',1 union all 5 select 'S2',2 union all 6 select 'S3',3 union all 7 select 'S4',4 union all 8 select 'S5',5 union all 9 select 'S6',6 union all 10 select 'S7',7 union all 11 select 'S8',8 union all 12 select 'S9',9 union all 13 select 'S10',10 14 insert into 表B 15 select 'CL2-L1','S2',2,500 union all 16 select 'CL2-L1','S5',5,300 union all 17 select 'CL2-L1','S6',6,400 union all 18 select 'CL2-L1','S8',8,345 19 20 21 select b.Cate_no, a.Size_no, a.Size_name, 22 isnull(c.Qty,0) 'Qty' from (select distinct Cate_no from 表B) b 23 cross join 表A a left join 表B c on b.Cate_no=c.Cate_no and a.Size_no=c.Size_no