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  

 

转载于:https://www.cnblogs.com/brave-cz/p/4027727.html