请写出SQL语句计算今天距离2005元旦的分钟数。答案：
select datediff(minute,'01/01/2005',getdate()) as minutes
1、 请一句SQL写出：如果person(personname,deptname) 表中没有 “财务部”的“张三” ，那
么请增加该人员。
set nocount on
if EXISTS(select * from person where personname='张三'and departname='财务部' ) begin
raiserror('(%s)的记录已经！',16,1,'财务部的张三')
return end
begin transaction insert person(personname,departname) values('张三','财务部')
if @@ROWCOUNT<>1
goto Error commit transaction return --提前返回 --错误处理 Error:
rollback
raiserror('添加失败',16,1)
答案二:if not EXISTS(select * from person where personname='张三' and deptname='财务部')
insert into person(personname,deptname) values('张三','财务部') 2、 请取出表 test (id identity(int,1,1)) 的第三条记录。
Select top 1 * from(select top 3 * from test order by id desc) temp
3、 有表 test Row 1 1 Val 101 102 2 1
请写SQL语句将表test 的内容显示为下边内容
Row 1 1 2 2 Val 101 102 101 102 答案：select a.row,a.val from test as a
left join test as b on b.val = a.row where a.val<>b.val union
select a.row,b.val from test as a
left join test as b on a.val = b.row where b.val<>a.row
4、 计算库存结余 test Inv 001 001 inouttype in out Iquantity 300 200 002 in 50 答案：：select sum(case when inouttype='in' then iquantity else -iquantity end) as 结余金额 from test3
6．请将表 test Cus 北京 北京 上海
转变成 Cus 北京 上海
答案：declare @sql varchar(8000)
set @sql = 'select cus'
select @sql = @sql + ' , sum(case inv when ''' + inv + ''' then money else 0 end) [' + inv +
']'
from (select distinct inv from test2) as a set @sql = @sql + ' from test2 group by cus' exec(@sql)
更好的答案。。。
7． 有表 test Personcode Personname 苹果 100 400 李子 200 0 inv 苹果 李子 苹果 Money 100 200 400 001 002 003
写一个过程
Anny Liszt Devy create procedure getselperson(@s varchar(200)) begin end;
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'getselperson' AND type = 'P') DROP PROCEDURE getselperson GO
CREATE PROCEDURE getselperson( @@Personcode1 varchar(200) = '001', @@Personcode2 varchar(200) = '002') AS
SET NOCOUNT ON begin TRANSACTION SELECT *
FROM test7
WHERE Personcode LIKE @@Personcode1 or
Personcode LIKE @@Personcode2
IF @@ROWCOUNT<>2 GOTO lblError
COMMIT TRANSACTION RETURN
lblError: ROLLBACK RAISERROR('失败！',16,1)
传入参数’001,002’,返回表 Iid 1 2
personcode 001 002 Personname Anny liszt
8． 请写SQL语句更新表test 中issingle person Friend Issingle anny Liszt devy
更新后的结果是： Person Anny Liszt Liszt Anny Keven Null Null Null Friend Liszt Anny Issingle 0 0 1 Devy Keven 答案：UPDATE test SET Issingle = 1 where Person = Devy; UPDATE test SET Issingle = 0 where Person <> Devy;
9． 请写SQL语句将下表test person 001 001 001 001 002 002
转换成表 Wpid 1 2 3 person 001 001 002 ttime 8:00 12:00 13:00 17:00 8:00 12:00 Iotype 1 2 1 2 1 2 in 8:00 13:00 8:00 Out 12:00 17:00 12:00 select identity(int,1,1) as wpid ,person,[in] = max(case when iotype = 1 then ttime else null end), [out]= max(case when iotype = 2 then ttime else null end)
into # from (select *,case when datepart(hh,cast('2001-01-01 '+ttime as datetime)) > 12 then 1 else 0 end as [type]
from test) a group by person,type 10． 有表test Personcode 001 001 ttime 8:00 8:05 Iotype 1 1 001 12:00 2 请写SQL语句将两行IOTYPE相同，时间间隔不足10分钟的第二条记录提取出来。 答案：SELECT * FROM (SELECT * AS a FROM test, SELECT * AS b FROM test) WHERE a.Iotype = b.Iotype AND a.time - b.time < 10
附加题(如有余力，可以做)： 11． 将表TEST SEQ QTY IO 1 2 3 4 5
转换成表 Seq 1 2 3
12． ID 1 2 3 4 5 转成表 COLDE A B A1 A2 B1 B2
有表TEST FATHER A A A B B CLASS 1 2 2 2 3 3 100 120 130 60 90 1 1 1 -1 -1 qty 100 120 130 Left 0 70 CHIELD A1 A2 B B1 B2 ISEND 0 0 1 1 1 1