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