❶ sqlserver 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)
1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。
复制代码
代码如下:
CREATE
PROC
[dbo].[Sp_testpagerandsorting]
(@GroupID
INT,
@CurrentId
INT,
@TimeFrom
DATETIME,
@TimeTo
DATETIME,
@OrderBy
CHAR(50),
@PageSize
INT,
@CurrentPage
INT)
AS
SET
nocount
ON
BEGIN
DECLARE
@StartNumber
INT,
@EndNumber
INT,
@CurrentIdRowNumber
INT,
@RecordCount
INT,
@EndPageIndex
INT
DECLARE
@RowNumberTable
TABLE
(
rownumber
INT
IDENTITY
(1,
1),
id
INT
)
--step
1:
Build
sort
id
list
-------------------------------------------------------
INSERT
INTO
@RowNumberTable
(id)
SELECT
sm.id
AS
id
FROM
dbo.test
sm
WITH
(nolock)
WHERE
indate
BETWEEN
Coalesce(@TimeFrom,
indate)
AND
Coalesce(@TimeTo,
indate)
AND
sm.groupid
=
@GroupID
ORDER
BY
CASE
WHEN
@OrderBy
=
'InDate
desc'
THEN
(
Row_number()
OVER
(ORDER
BY
indate
DESC))
WHEN
@OrderBy
=
'InDate
asc'
THEN
(Row_number()
OVER
(ORDER
BY
indate
ASC))
WHEN
@OrderBy
=
'Id
asc'
THEN
(Row_number()
OVER
(ORDER
BY
sm.id
ASC))
WHEN
@OrderBy
=
'Id
desc'
THEN
(Row_number()
OVER
(ORDER
BY
sm.id
DESC))
WHEN
@OrderBy
=
'Name
asc'
THEN
(Row_number()
OVER
(ORDER
BY
sm.name
ASC))
WHEN
@OrderBy
=
'Name
desc'
THEN
(Row_number()
OVER
(ORDER
BY
sm.name
DESC)
)
END
--step
2:
Reset
page
index
with
current
id
-----------------------------------------
IF
@CurrentIdNumber
>
0
BEGIN
SELECT
TOP
1
@CurrentIdRowNumber
=
rownumber
FROM
@RowNumberTable
WHERE
id
=
@CurrentIdNumber
IF
@CurrentIdRowNumber
>
0
BEGIN
IF
@CurrentPage
=
0
BEGIN
SET
@CurrentPage
=
Ceiling(CAST(@CurrentIdRowNumber
AS
DECIMAL)
/
CAST
(@PageSize
AS
DECIMAL))
END
END
END
ELSE
BEGIN
IF
@CurrentPage
=
0
BEGIN
SET
@CurrentPage
=
1
END
END
--step
3:
Set
recordCount
-----------------------------------------
SELECT
@RecordCount
=
COUNT(1)
FROM
@RowNumberTable
--step
4:
Calc
startNumber
&
endNumber
-----------------------------------------
SELECT
@StartNumber
=
@PageSize
*
(
@CurrentPage
-
1
),
@EndNumber
=
@PageSize
*
(
@CurrentPage
-
1
)
+
@pageSize,
@EndPageIndex
=
Ceiling(CAST(@RecordCount
AS
DECIMAL)
/
CAST(@PageSize
AS
DECIMAL))
IF
@CurrentPage
=
@EndPageIndex
BEGIN
SET
@EndNumber
=
@RecordCount
END
--step
5:
Get
sorted
id
of
current
page
-----------------------------------------
;WITH
a
AS
(SELECT
TOP
(@EndNumber
-
@StartNumber)
id,
rownumber
FROM
(SELECT
TOP
(@EndNumber)
id,
rownumber
FROM
@RowNumberTable)
AS
b
ORDER
BY
rownumber
DESC)
--step
6:
Return
current
page
idList
-------------------------------------------------------
SELECT
[ID],
[GroupID]
[Name],
[Address]
FROM
dbo.test
sm
WITH(nolock)
INNER
JOIN
a
ON
a.id
=
sm.id
ORDER
BY
a.rownumber
--
step
7:return
current
page
&
record
count
----------------------------------
SELECT
@CurrentPage
AS
currentpage,
@RecordCount
AS
recordcount
END
2,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做)
复制代码
代码如下:
CREATE
PROC
[dbo].[Getstudentlistbycondition]
@Name
NVARCHAR(20),
@Class
INT
AS
SET
nocount
ON
BEGIN
BEGIN
SELECT
[Name],
[class]
FROM
[testtable]
WHERE
[Class]
=
CASE
WHEN
@Class
>
0
THEN
@Class
ELSE
[Class]
END
AND
[name]
=
CASE
WHEN
@Name
<>
''
THEN
@Name
ELSE
[Name]
END
END
END
❷ 自定义查询多对多sql 如何获取全部的数据
select A.*, b.* from A left join B on B.bj =A.id
就是实现左连接查询
如果 你的数据关系 没有错误的话，以上语句是可以查询出所有符合条件的数据的
❸ SQL有哪几种用户自定义函数,区别是什么
用户定义的函数有标量函数和表值函数两种，其中表值函数又分为内联表值函数和多语句表值函数。
❹ 在sql 中怎么实现自定义完整性
主外键约束
Check 约束 Default约束
触发器
❺ SQL怎么自定义约束
语法： ALTER TABLE 表名 ADD CONSTRAINT约束名约束类型 具体的约束声明
约束名的取名规则推荐采用:约束类型_约束列，当然你也可以不这么命名，这样单纯是比较方便
如：主键(Primary Key)约束：PK_UserId
唯一(Unique key)约束: UQ_UserCardId
默认(Default Key)约束 DF_UserPasswd
检查(Check Key)约束 CK_Gender
外键(Foreign Key)约束: FK_SortId
--以上加棕色段是我定义表中的列名称
打个比方：add constraint PK_UserId PRIMARY KEY (UserId)
--PK_UserId 是自定义约束名 ， PRIMARY KEY 是约束类型， (UserId)是具体的约束声明
❻ 如何用SQL编写复杂的自定义报表
可以看一下这个文库《如何用SQL编写复杂的自定义报表》
http://wenku..com/link?url=-
❼ SQL编写一个自定义函数或者存储过程，能够实现参数化查询的功能。
-- Description: 根据输入的中继及区域,确定查询的数据
-- =============================================
alter PROCEDURE 中继话务量查询
--drop PROCEDURE 中继话务量查询
--CREATE PROCEDURE 中继话务量查询
-- Add the parameters for the stored procere here
@开始日期 datetime,
@结束日期 datetime,
@查询区域 int,
@查询中继 int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procere here
if @查询区域=99
begin /* 查询全部区域 */
if @查询中继=0
begin
SELECT exch_id, trk_in, date, total_r, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (date <= @结束日期)
end
else
begin
SELECT exch_id, trk_in, date, total_r, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (trk_in =@查询中继 ) and
(date <= @结束日期)
end
end
else
begin
if @查询中继=0 /* 查询全部中继*/
begin
SELECT exch_id, trk_in, date, total_r, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (date <= @结束日期) AND (exch_id = @查询区域)
end
else
begin
SELECT exch_id, trk_in, date, total_r, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (trk_in =@查询中继 ) and
(date <= @结束日期) AND (exch_id = @查询区域)
end
end
END
GO
❽ mysql设计教学管理数据库：要求设计数据库记录每个同学在每门课程上的成绩，可以自定义场景
首先定义三个表，学生(id,sno,name)，课程(cid,cname),成绩表(sid,id,cid,score)，这样，数据库表基本定义完成，这个是数据库操作的关键，接下来用sql语句给每张表插入数据:例如学生表，id自增长
insert into student(sno,sname) values(学号，学生姓名)
参考一下。
❾ 急等！请问如何写单纯的SQL语句实现下面的自定义函数功能
GO
DECLARE @i INT
DECLARE @k INT
SET @k = 0
DECLARE @rtStr NVARCHAR(4000)
SET @str = @str + @sep
SET @i = CHARINDEX(@sep, @str)
WHILE @i > 0
BEGIN
--INSERT INTO @tb
--VALUES ( LEFT(@str, @i - 1) )
IF @k = @index
BEGIN
SET @rtStr = LEFT(@str, @i - 1)
BREAK
END
SET @str = SUBSTRING(@str, @i + 1, 4000)
SET @i = CHARINDEX(@sep, @str)
SET @k = @k + 1
END
SELECT RTRIM(LTRIM(@rtStr))
GO
你把这一段当成是一个SQL语句，放到程序里面去执行就是了
@str NVARCHAR(4000) ,
@sep NVARCHAR(4000) ,
@index INT
这几个东西用程序里面的string去代替就可以了
语句块也是一条SQL语句！！谢谢