一些优秀的查询语句

1

现有一sql server表A(id , year int(4) not null , month int(4) not null , money int(4) ) , year和month代表日期的年和月 , 现要按月汇总,按季汇总
请问怎样写sql语句？

按月份
select sum(money)
from A
group by Year,month
按季度
select ceil(month/3),sum(money)
from A
group by Year,ceil(month/3)

比较巧妙的是这个ceil。随便某个月份经过ceil(month/3)的处理就编程季度了。ceil的功能是返回大于等于N的最小整数值。

参考：http://zhidao.baidu.com/question/122030109.html?fr=cfnw&fs=up

 

2

There are 2 tables as follows:
dmSQL> select * from t1;
    C1          C2     
=========== ===========
          1           1
          2           2
          3           3
3 rows selected
dmSQL> select * from t2;
    C1          C2     
=========== ===========
          1          11
          3          33
2 rows selected
Please submit a SQL command to update t1. This new updated t1 will look as follows:
dmSQL> select * from t1;
    C1          C2     
=========== ===========
          1          11
          2           2
          3          33
3 rows selected

update t1 set c2=(select t2.c2 from t2 where t1.c1=t2.c1) where c1 in
(selct c1 from t2);

呵呵，网上回答别人的问题。从没写过类似的查询语句。今天试一试居然能用。呵呵，帮助别人自己也能学到一些东西，何乐不为？

参考：http://zhidao.baidu.com/question/122988552.html?fr=im2

 

3

表1,A字段包含B字段
A        B
111     123
111     235

表2,C字段包含D字段

C      D
111    123
如何用SQL来比对,两表之间的差异数据?

 

Oracle里边可以这样：
select * from 表1 minus select * from 表2

SqlServer2005中可以这样：
select * from 表1 except select * from 表2

sqlserver2000中可以这样：
select * from 表1 where not exists (select 1 from 表2 where C=表1.A and D=表1.B)

sqlserver2000里边的这种方式在Oracle和sqlserver2005中也是可行的

参考资料：http://zhidao.baidu.com/question/115568263.html

 

4 mysql分段统计

表结构：
+------------+----------+
| time       | num      |
+------------+----------+
| 1256278360 | 10000000 |
| 1256278370 | 10000000 |
| 1256278380 | 10000000 |
| 1256278390 | 10000000 |
| 1256278480 | 10000000 |
| 1256278490 | 10000000 |
| 1256278580 | 10000000 |
| 1256278590 | 10000000 |
| 1256278600 | 10000000 |
| 1256278670 | 10000000 |
| 1256278680 | 10000000 |
| 1256278690 | 10000000 |
| 1256279810 | 10000000 |
| 1256279820 | 10000000 |
| 1256279830 | 10000000 |
| 1256279840 | 10000000 |

如何查询time字段每隔30(s)段的sum值，也就是汇总30s内多条数据为一条？

select sum(`num`) from table_name group by floor(`time`/30);

 

5.

有表student(id,name,score)根据分数列（score）每10分为一段，查询每段分数的人数。

select substr(score,1,1)*10 as score,count(score) from student group by score;

 

6.

一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

select t1.name,t2.name from department t1,department t2 where t1.name<t2.name;

来源：http://zhidao.baidu.com/question/108512613.html?fr=cfnw&fs=up

 

7 一个面试题：20100126补充

一个表有2个字段 一个是 姓名 另一个是 成绩
NAME                                          SCORE
---------------------------------------- ----------
甲                                               80
乙                                               79
丙                                               79
丁                                               78

最后查询出 2列 一个是姓名 另一个是 排名
NAME                                          SCORE        NUM
---------------------------------------- ---------- ----------
甲                                               80          1
乙                                               79          2
丙                                               79          2
丁                                               78          4

[TEST1@orcl] SQL>select name,score,(select count(*)+1 from t5 where score>a.score) num from t5 a order by score desc;

查询当前表内分数大于所查询的那行的分数的总数+1；

NAME                                          SCORE        NUM
---------------------------------------- ---------- ----------
甲                                               80          1
乙                                               79          2
丙                                               79          2
丁                                               78          4
来源：http://zhidao.baidu.com/question/134837752.html#here

 

6 (100301更新)

如表T1，有个字段为 WH 值为
1
2
3
4
如何能排序使输出的数据为
2
1
3
4

[TEST1@orcl] SQL>select * from c;

        ID
----------
         1
         2
         3
         4

[TEST1@orcl] SQL>select * from c order by case when id=2 then 0 else 1 end,id;

        ID
----------
         2
         1
         3
         4

方法很巧妙。又学到了一招。呵呵

来源:http://zhidao.baidu.com/question/139711406.html

7(100302补充）

s_type (业务类型)       money

9009                                1.1
1001                                100
1002                                100
1003                                100

用语句求出在包含9009 和不包含9009的时候的总钱数

得出的结果应该为: 1    301.1
                                 0     300      注意: 1   代表包含9009的标识 ,0 代表不包含9009的标识

[TEST1@orcl] SQL>select instr(s_type,9009,1) r,sum(money) m from t group by instr(s_type,9009,1);

         R          M
---------- ----------
         1        1.1
         0        300

[TEST1@orcl] SQL>select r,m,lag(m,1)over(order by r) t from
2 (select instr(s_type,9009,1) r,sum(money) m from t group by instr(s_type,9009,1));

         R          M          T
---------- ---------- ----------
         0        300
         1        1.1        300

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值，lag(m,1) 用于取得M的上一行数据当然也可以lag(m,1,0),这样的话如果没取得上一行，就会用0补充
[TEST1@orcl] SQL>select r type,m+nvl(t,0) sum_money from(
2 select r,m,lag(m,1)over(order by r) t from
3   (select instr(s_type,9009,1) r,sum(money) m from t group by instr(s_type,9009,1)));

      TYPE SUM_MONEY
---------- ----------
         0        300
         1      301.1

来源：http://www.itpub.net/viewthread.php?tid=1273995&extra=&page=1

8 (20100304补充)

求SQL：返回每个部门薪水最高的员工
emp(empno,emp_name,salalry）

select   empno,   emp_name,   salary from emp where (empno,salary) in
(select empno,max(salary) ms   from emp group by empno);

 

9 （20100408补充）

[TEST1@orcl#08-4月 -10] SQL>select * from t9;

      YEAR        SAL
---------- ----------
      2000         10
      2001         20
      2002         30
      2003         40

[TEST1@orcl#08-4月 -10] SQL>select a.year,sum(b.sal) from t9 a,t9 b where a.year>=b.year group by a.year order by a.year;

      YEAR SUM(B.SAL)
---------- ----------
      2000         10
      2001         30
      2002         60
      2003        100