SQL语句

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