SQL作业

1:写一个包含连接和分组的sql

SELECT dbo.Role.RoleId FROM dbo.Admin
INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId
GROUP BY dbo.Role.RoleId

2:写一个包含连接和case when的sql

SELECT dbo.Role.RoleId,
(CASE Admin.RoleId WHEN 1 THEN '管理员' ELSE '无' END)角色名
FROM dbo.Admin
INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId

3:写一个包含连接和Cast()函数的sql

select CAST(Id AS varchar) from dbo.RoId

4:写一个包含连接和Convert()函数的sql

select *,CONVERT(DECIMAL(9,2),Admin.RoleId) from dbo.RoleId

5:写一个包含子查询的sql

select * from dbo.Admin where RoleId IN(select RoleId from dbo.Role)	

6:写一个视图的sql

CREATE VIEW view_1 AS select * from dbo.Admin where RoleId IN(select RoleId from dbo.Role)

7:创建索引的sql

create unique nonclustered index UQ_NonClu_MId on dbo.Admin(MId) with (pad_index=on,fillfactor=50,statistics_norecompute=off)

8:分页存储过程sql

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TempPage')
 DROP PROC TempPage
GO
CREATE PROCEDURE TempPage 
    @PageSize INT, --每页大小
    @PapeIndex INT, --当前索引
    @PageCount INT OUTPUT, --返回总页数
    @RecordCount INT OUTPUT --返回总记录条数
AS
BEGIN
    --先创建临时表存放表连接查询数据
    SELECT * INTO #NewsTemp FROM 
    (
         SELECT 
         OP.OrderGuid,
         OP.ProductGuid,
         OP.ProductName,
         O.LoginId ,
         ROW_NUMBER() OVER(ORDER BY OP.OrderGuid DESC) AS RowNumber 
         FROM OrderProduct AS OP 
         INNER JOIN OrderInfo AS O ON O.OrderGuid=OP.OrderGuid
     ) T
    --从临时表中查询分页数据
    SELECT * FROM #NewsTemp WHERE RowNumber BETWEEN @PageSize*(@PapeIndex-1)+1 AND (@PageSize*@PapeIndex)
    --查询总记录条数
    SELECT @RecordCount=COUNT(1) FROM #NewsTemp
    --计算总页数
    SET @PAGECOUNT = CEILING(@RecordCount*1.0/@PageSize)
END

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种

方法1：
select TOP 10 * from YieldRole
where id NOT IN(select TOP (10*(1-1))  id from YieldRole order by id)
order by id
方法2：
select TOP 10 * from YieldRole
where id >=(select ISNULL(MAX(id),0) FROM (select TOP (10*(2-1)+1) id FROM YieldRole ORDER BY id) A)
 ORDER BY id
 --降序写法：
 select TOP 10 from YieldRole where id <= (select ISNULL(MIN(id),0) from 
 (select TOP (10*(2-1)+1) id from YieldRole order by id Desc)A)order by id Desc
方法3：
select TOP 10 * from(select ROW_NUMBER() over (order by id) AS RowNumber,* from YieldRole) A where RowNumber > 10*(2-1) 

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) > 2000;

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 '2000-06-06' and '2019-06-06' group by c.ConName having count(a.conID)<2