1:写一个包含连接和分组的sql
select COUNT(1) from UserInfo as u,Sex as s where u.Sex_Id=s.Id GROUP BY Sex_Id;
2:写一个包含连接和case when的sql
SELECT Id,
SUM( CASE WHEN Sex_Id = '1' THEN Sex_Id ELSE 0 END) as 男,
SUM( CASE WHEN Sex_Id = '2' THEN Sex_Id ELSE 0 END) as 女
FROM UserInfo GROUP BY Id;
3:写一个包含连接和Cast()函数的sql
SELECT Id,CAST([Money] AS decimal) from UserInfo
4:写一个包含连接和Convert()函数的sql
select u.Id,convert(Varchar(10),[Money])+'元' from UserInfo as u,Sex as s where u.Sex_Id=s.Id GROUP BY u.Id,[Money];
5:写一个包含子查询的sql
select *from UserInfo where Sex_ID=(select Id from Sex where Id=1)
6:写一个视图的sql
create view a as select *from UserInfo
7:创建索引的sql
CREATE INDEX mycolumn_index ON UserInfo (Id)
8:分页存储过程sql
create proc [dbo].[pagelistproc] (@pageIndex int,
@pagesize int,@tableName varchar(200),@columnName varchar(500),@orderby varchar(50),@sort varchar(50)
)as
declare @sql nvarchar(2000);
set @sql='select '+@columnName+' from(select '+@columnName+' ,ROW_NUMBER()over(order by '+@orderby+' )
as number from '+@tableName+' )t where t.number between '+cast(((@pageIndex-1)*@pagesize)as varchar(200))
+' and '+ cast((@pagesize*@pageIndex) as varchar(200))
exec(@sql)
exec pagelistproc 1,2,'UserInfo','*','Id','desc'
9:写一个触发器sql
CREATE TRRIGER MYTR1
ON A
FOR INSERT
AS
DECLARE @UserName varchar(20)
SELECT @UserName=UserName from CurrentUser
INSERT INTO C (TableName,Type,dDate,UserName) VALUES ('A','Insert',getdate(),@UserName)
10:写一个存储过程包含事务的sql
CREATE PROC [dbo].[notice_Delete] --- 同时删除该通知书和对应的节点
@tbl VARCHAR(30),
@pid INT
AS
BEGIN
DECLARE @tblname VARCHAR(30) ;
DECLARE @sql VARCHAR(1000) ;
SET @tblname = @tbl
SET @sql = 'delete ' + @tblname + ' where id ='
+ CONVERT(VARCHAR(10), @pid)
BEGIN TRAN --开始事务
EXEC ( @sql
)
IF ( @@rowcount = 0 ) --执行结果影响行数为0
BEGIN
ROLLBACK TRAN --回滚
END
ELSE
BEGIN
DELETE FROM tbl_treenotice
WHERE purposeid = @pid
IF ( @@rowcount = 0 ) --执行结果影响行数为0
BEGIN
ROLLBACK TRAN --回滚
END
ELSE
BEGIN
COMMIT TRAN --提交事务
END
END
END
11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。
CREATE PROCEDURE PROC10281009
AS
BEGIN
UPDATE A
SET b=B.b
FROM table1 A
INNER JOIN table2 B ON A.c=B.c
INSERT INTO table1
SELECT A.a,A.b,A.c
FROM table2 A
LEFT JOIN table1 B ON A.c=b.c
WHERE B.c IS NULL
END
12:分页的实现方式?至少写3种
<!--第一种 top-->
select top(10)* from AdminInfo where Id not in(select top(10) Id from AdminInfo)
<!--第二种 MAX()-->
select top(10) * from AdminInfo where Id>(select MAX(Id)from AdminInfo where Id in (select top(10)Id from AdminInfo))
<!--第三种 between ... and ...-->
select * from AdminInfo where Id between 1 and 20
<!--第四种 ROW_NUMBER()-->
select *from(select *,ROW_NUMBER()over(order by id)as number from AdminInfo)t where t.number between 11 and 20
13:写一个包含连接和分组,并且根据某个字段拼接的sql
select RoleId,
(select COUNT(1) from dbo.Admin where dbo.Admin.RoleId=Role.RoleId) as Count,
stuff((select ',' + MName from dbo.Admin,dbo.Role where Role.RoleId=dbo.Admin.RoleId for xml path('')),1,1,'') as UserName from Role group by RoleId
14:写一个包含having写法的sql
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name
HAVING SUM(sales) > 1500;
15:写一个包含连接和分组,排序的sql。
select a.ConID,c.ConName,count(a.conID) as num from InGoods a inner join City b on a.CityID=b.CityID left join Contract c on a.ConID=c.ConID where b.CItyName=‘上海’ and a.InTime between '2009-01-01' and '2010-06-31' group by c.ConName having count(a.conID)<3