Sql查询技巧实例

Sql查询技巧

 

1、  表A04中的含有A、B、C、D四个字段，当按A字段分组后，如果D等1，则只统计B的值，如果D等0,则只统计C的值。

CREATE TABLE A04(A VARCHAR(20),B INT,C INT,D INT)

INSERT INTO A04 VALUES('01',20,7,'0')

INSERT INTO A04 VALUES('01',10,8,'1')

INSERT INTO A04 VALUES('02',20,7,'1')

INSERT INTO A04 VALUES('02',10,8,'0')

 

 

SELECT A,SUM(CASE D WHEN 1 THEN B WHEN 0 THEN C END) FROM A04 GROUP BY A

 

2、  如何实现有一组有规则的编号（如200305310001…200305310999）

 

DECLARE @I INT,@C VARCHAR(20)

SELECT @I=1

WHILE @I<1000 BEGIN

  SELECT @C=CASE WHEN @I<10 THEN '000'+CAST(@I AS CHAR(1))

                 WHEN @I BETWEEN 10 AND 99 THEN '00'+CAST(@I AS CHAR(2))

                 WHEN @I BETWEEN 100 AND 999 THEN '0'+CAST(@I AS CHAR(3))

            END

  SELECT @C=CONVERT(VARCHAR(20),GETDATE(),112)+@C

  SELECT @C     --在查询分析器中输出

  SELECT @I=@I+1

END

 

3、现在有三个表，结构如下

Score(FScoreId  成绩记录号,FSubID 课程号,FStdID 学生号,FScore    成绩)

student:（FID  学生号，FName  姓名）

subject:（FSubID   课程号，FSubName 课程名）,  

怎么能实现这个表：

姓名   英语   数学   语文  历史

张萨   78     67     89    76

王强   89     67     84    96  

李三   70     87     92    56

李四   80     78     97    66

SELECT A.FNAME AS 姓名,

      英语 = SUM(CASE B.FSUBNAME WHEN '英语' THEN C.FSCORE END),

      数学 = SUM(CASE B.FSUBNAME WHEN '数学' THEN C.FSCORE END),

      语文 = SUM(CASE B.FSUBNAME WHEN '语文' THEN C.FSCORE END),

      历史 = SUM(CASE B.FSUBNAME WHEN '历史' THEN C.FSCORE END)

FROM STUDENT A, SUBJECT B, SCORE C

WHERE A.FID = C.FSTDID AND B.FSUBID = C.FSUBID GROUP BY A.FNAME

 

4、有两个表，用一条SQL语句算出商品A,B目前还剩多少？表结构如下：

商品名称mc  商品总量sl  表一(AAA)

  A         100

  B         120

商品名称mc  出库数量sl 表二(BBB)

   A         10

   A         20

   B         10

   B         20

SELECT TA.商品名称,A-B AS 剩余数量 FROM

(SELECT 商品名称,SUM(商品总量) AS A FROM AAA GROUP BY 商品名称)TA,

(SELECT 商品名称,SUM(出库数量) AS B FROM BBB GROUP BY 商品名称)TB

WHERE TA.商品名称=TB.商品名称

 

6、有一个商品销售表，记载了某月商品的销售数量，现在要为所有商品排名次，放到一个单独的字段中，就是说达到右边显示的效果，如何作？

 

BU1032        5        NULL      BU1032 5  2

PS2091        3        NULL      PS2092 3  3

PC8888        50        NULL      PC8888 50 1

 

UPDATE X SET ORD=(SELECT COUNT(*)+1 FROM X B WHERE B.QTY>X.QTY)

 

7、本人有一张表单, 要求统计COL1~COL6中不等于2的列的个数，数据如下：

————————————————————————————————

ROW_ID |  COL1  |  COL2  |  COL3  |  COL4  |  COL5  |  COL6  |

  1    |    2   |    1   |    1   |    2   |    3   |    2   |

  2    |    1   |    1   |    2   |    2   |    2   |    2   |

  3    |    2   |    3   |    2   |    2   |    1   |    2   |

  4    |    2   |    2   |    2   |    2   |    1   |    2   |

  5    |    1   |    2   |    2   |    2   |    2   |    2   |

  6    |    2   |    2   |    2   |    2   |    2   |    1   |

————————————————————————————————

        要求结果如下：

        —————————

ROW_ID | COUNT  |

  1    |    3   | 

  2    |    2   |

  3    |    2   |

  4    |    1   |

  5    |    1   |

  6    |    1   |

SELECT ROW_ID,(6-(CASE WHEN COL1=2 THEN COL1 / 2 ELSE 0 END)

-(CASE WHEN COL2=2 THEN COL2 / 2 ELSE 0 END)

-(CASE WHEN COL3=2 THEN COL3 / 2 ELSE 0 END)

-(CASE WHEN COL4=2 THEN COL4 / 2 ELSE 0 END)

-(CASE WHEN COL5=2 THEN COL5 / 2 ELSE 0 END)

-(CASE WHEN COL6=2 THEN COL6 / 2 ELSE 0 END))AS COUNT FROM TABLE_A 

 

8、有一客户表，数据如下：

客户     日期          资金

F001    2003-03-05     123.00

F002    2003-03-04     1223.00

F002    2003-03-02     1123.00

F003    2003-03-05     1231.00

F003    2003-03-04     1232.00

要求选出每个客户最新的哪条记录 组成一个结果集，结果如下：

F001    2003-03-05     123.00

F002    2003-03-04     1223.00

F003    2003-03-05     1231.00

 

SELECT A.客户, B.日期, A.资金 FROM  客户资金表 A,

    (SELECT 客户, MAX(日期) 日期  FROM 客户资金表  GROUP BY 客户 ) B

WHERE A.客户 = B.客户 AND A.日期 = B.日期

 

9、用游标将根据sys_orgmenber表中的member字段为依据将org字段更新到sys_user 表中username对应的reg_num字段信息 ？

 

 

10、下表，记录数据较多，要求对同一分类的数据进行排序？

CREATE TABLE A_TEST(工號VARCHAR(10), 姓名 varchar(10), 時間VARCHAR(20), 序號INT)

INSERT INTO A_TEST VALUES('001',' abc ',’17:40’,0)

INSERT INTO A_TEST VALUES('001','abc',’08:00’,0)

INSERT INTO A_TEST VALUES('001',' abc ',’13:28’,0)

INSERT INTO A_TEST VALUES('001',' abc ',’12:01’,0)

INSERT INTO A_TEST VALUES('002','def',’07:30’,0)

INSERT INTO A_TEST VALUES('002','def',’22:59’,0)

 

结果

工號  姓名  時間     序號

001   abc   08:00     1

001   abc   12:01     2

001   abc   13:28     3

001   abc   17:40     4

002   def    07:30    1

002   def    22:59    2

 

UPDATE A_TEST SET ORD=(

SELECT COUNT(*)+1 FROM A_TEST B WHERE B.SJ<A_TEST.SJ AND B.ID=A_TEST.ID)

 

11、学生关系：S（Sno，Sname，Ssex，Sage，class）  

  Sno－学号，class－班级，Sname－姓名，Ssex－性别，Sage－年龄  

  问题：列出至少有12名男生的班号。

Select   Class    

  FROM   S  

  GROUP   BY   class,Ssex  

  HAVING   (COUNT(Class)>=12)   AND   (Ssex='男')

 

12、计算一个月第一天的SQL 脚本，周(wk)的时间间隔来计算哪一天是本周的星期一，用年(yy)的时间间隔来显示这一年的第一天，季度的第一天

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

 

13、原纪录：

    11       AA,BB,CC,DD

想变成

    11     AA

    11     BB

    11     CC

    11     DD

怎么写语句？

 

create table tb其不意(id int,name varchar(500))

insert tb其不意 values(11,'AA,BB,CC,DD')

 

select top 500 id=identity(int,1,1) into # from syscolumns a,syscolumns b

 

select *

from

(

    select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)

    from tb其不意 a,# b

    where b.id<=len(a.name) and substring(','+a.name,b.id,1)=','

) x

 

drop table tb其不意,#