year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000

year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000

select b.year,sum(a.salary)
from salary a,salary b
where a.year<=b.year group by b.year
order by b.year;

select s1.year "year",(select sum(s2.salary) from salary s2 where s2.year<=s1.year) "salary" from salary s1;

 SELECT *FROM page where url like '%baidu%' or title like '%baidu%' or like ''ORDER BY CHARINDEX('baidu', url) DESC, CHARINDEX('baidu', title) DESC,       CHARINDEX('baidu', body) DESC

1.一道SQL语句面试题，关于group by

2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')

1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001

2.请教一个面试中遇到的SQL语句的查询问题

------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name

3.面试题：一个日期判断的sql语句？

------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0

4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文              数学                英语
及格              优秀                不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table

5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------

6.sqlserver2000是一种大型 数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。 ------------------------------------------ 它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.

SQL Server 2000 数据库有三种类型的文件：

7.请用一个sql语句得出结果

table1

-------------------------------

table2

--------------------------------
01      国内业务一部
02      国内业务二部
03      国内业务三部
04      国际业务部

table3 （result）

--------------------------------------
01      10        null      null
02      10         8        null
03      null       5        8
04      null      null      9

------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep

8.华为一道面试题

------------------------------------------
select id, Count（*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1

9。表结构以及数据如下：

CREATE TABLE 表
(ID int, 日期 varchar(11), 单据 char(3))

INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );

ID 日期 单据
---------- ----------- ---
1 2004-08-02 001
4 2004-09-02 002

--解答：
--相关子查询
select a.* from 表 a
where 日期=
(select min(日期) from 表 where 单据=a.单据)

--用JOIN的连接
select a.* from 表 a,
(select min(日期) 日期,单据 from 表 group by 单据) b
where a.单据=b.单据 and a.日期=b.日期

--不用JOIN的连接
select a.* from 表 a JOIN
(select min(日期) 日期,单据 from 表 group by 单据) b
ON a.单据=b.单据 and a.日期=b.日期

--用谓词Exists
select * from 表 a
where not exists(select 1 from 表 where 单据=a.单据 and 日期<a.日期)

 select a.[id],a.mark from(select [page].[id],100 as mark from [page] where [page].[url] like '%baidu%'unionselect [page].[id],50 as mark from [page] where [page].[title] like '%baidu%'unionselect [page].[id],10 as mark from [page] where [page].[body] like '%baidu%') as a  order by mark desc

id  strvalue type
1    how      1
2    are      1
3    you      1
4    fine     2
5    thank    2
6    you      2

#how are you#fine thank you#

select
(select '#'+replace(
replace((SELECT strvalue FROM tb_test t where type = 1 FOR XML AUTO),'<t strvalue="',' ')
,'"/>', ' ')
+'#'
)
+
(select replace(replace((SELECT strvalue FROM tb_test t where type = 2 FOR XML AUTO),'<t strvalue="',' '),'"/>', ' ')+'#')