❶ 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語句！！謝謝