1.工作中要求将原本mysql的数据库变成sqlServer，整理一些mysql和sqlServer的区别
ifnull --> isnull
now-->GETDATE
DATE_FORMAT( createdDate, '%H:%i' ) -->CONVERT(CHAR(5), createdDate, 108)
DATE_FORMAT( createdDate, '%Y-%m-%d' ) -- >CONVERT(CHAR(10), createdDate, 23)
DATE_FORMAT(auditTime,'%Y-%m-%d %H:%i:%s') --> CONVERT(CHAR(20),auditTime,120)
date_sub(now(), interval 30 MINUTE) -->DATEADD(day,-2,GETDATE())
date_add(now(), interval 30 MINUTE) -->DATEADD(day,2,GETDATE())
select .... limit 1 => select top 1.....
2.初次接触存储过程sqlserver，记录一些
ALTER PROCEDURE [dbo].[Stat_JudgeDataCompress] @StartTime DATETIME , --开始时间
@EndTime DATETIME , --结束时间
@DataType VARCHAR(10) , --数据类型（售票、退票）
@StatisticType VARCHAR(10) --统计类型(按售票时间、按游玩日期)
AS
BEGIN
------------------------------------如果查询时间为当前时间,则不压缩数据-----------------------------------------
IF DATEDIFF(DAY, @StartTime, GETDATE()) <= 0
BEGIN
RETURN;
END;
------------------------如果查询结束日期为当前时间,则压缩结束时间至前一天的23:59:59------------------------------
IF DATEDIFF(DAY, @EndTime, GETDATE()) = 0
BEGIN
SET @EndTime = CONVERT(VARCHAR(10), DATEADD(DAY, -1, @EndTime), 120)
+ ' 23:59:59';
END;
-------------------如果开始结束时间同一天，并且不是00:00:00-23:59:59，则不压缩数据------------------------------
DECLARE @DayCount INT;
SELECT @DayCount = DATEDIFF(DAY, @StartTime, @EndTime);
IF @DayCount = 0
AND ( CONVERT(VARCHAR(8), @StartTime, 8) <> '00:00:00'
OR CONVERT(VARCHAR(8), @EndTime, 8) <> '23:59:59'
)
BEGIN
RETURN;
END;
-------------------如果开始结束时间间隔一天，并且不是00:00:00-23:59:59，则不压缩数据----------------------------
IF @DayCount = 1
AND CONVERT(VARCHAR(8), @StartTime, 8) <> '00:00:00'
AND CONVERT(VARCHAR(8), @EndTime, 8) <> '23:59:59'
BEGIN
RETURN;
END;
------------------------------------创建临时表------------------------------------------------------------------
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#SaleInfo')
AND type = 'U' )
DROP TABLE #SaleInfo;
CREATE TABLE #SaleInfo
(
Sale_Date DATETIME ,
Ticket_ID BIGINT ,
PaymentType_ID BIGINT ,
Sale_TicketCount INT ,
Sale_PersonCount INT ,
Sale_TotalMoney DECIMAL ,
Sale_TicketSum DECIMAL
)
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#ReturnInfo')
AND type = 'U' )
DROP TABLE #ReturnInfo;
CREATE TABLE #ReturnInfo
(
Cancel_Date DATETIME ,
Ticket_ID BIGINT ,
PaymentType_ID BIGINT ,
Cancel_TicketCount INT ,
Cancel_PersonCount INT ,
Cancel_TotalMoney DECIMAL ,
Cancel_TicketSum DECIMAL
)
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#SaleCacheInfo')
AND type = 'U' )
DROP TABLE #SaleCacheInfo;
CREATE TABLE #SaleCacheInfo
(
Sale_Date DATETIME ,
Ticket_ID BIGINT ,
PaymentType_ID BIGINT ,
Sale_TicketCount INT ,
Sale_PersonCount INT ,
Sale_TotalMoney DECIMAL ,
Sale_TicketSum DECIMAL
)
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#ReturnCacheInfo')
AND type = 'U' )
DROP TABLE #ReturnCacheInfo;
CREATE TABLE #ReturnCacheInfo
(
Cancel_Date DATETIME ,
Ticket_ID BIGINT ,
PaymentType_ID BIGINT ,
Cancel_TicketCount INT ,
Cancel_PersonCount INT ,
Cancel_TotalMoney DECIMAL ,
Cancel_TicketSum DECIMAL
)
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#Stat_CompressDate')
AND type = 'U' )
DROP TABLE #Stat_CompressDate;
CREATE TABLE #Stat_CompressDate
(
OperateDate DATE ,
DataType VARCHAR(10) ,
StatisticType VARCHAR(19)
);
-------------------------如果查询开始时间不是完整的一天,则压缩开始时间为后一天的00:00:00------------------------
IF CONVERT(VARCHAR(8), @StartTime, 8) <> '00:00:00'
BEGIN
SET @StartTime = CONVERT(VARCHAR(10), DATEADD(DAY, 1,
@StartTime), 120)
+ ' 00:00:00';
END;
------------------------如果查询结束日期为不是完整的一天,则压缩结束时间为前一天的23:59:59--------------
IF CONVERT(VARCHAR(8), @EndTime, 8) <> '23:59:59'
BEGIN
SET @EndTime = CONVERT(VARCHAR(10), DATEADD(DAY, -1, @EndTime), 120)
+ ' 23:59:59';
END;
---------------------------------------------比较售票数据---------------------------------------------------
IF @DataType = 'Sale'
BEGIN
IF @StatisticType = 'SaleDate'
BEGIN
--------------------------------------原始数据和压缩表数据存入临时表-----------------------------------------
INSERT INTO #SaleInfo
SELECT CONVERT(VARCHAR(10), Sale_Date, 120) AS Sale_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Sale_TicketCount), 0) AS Sale_TicketCount ,
ISNULL(SUM(Sale_PersonCount), 0) AS Sale_PersonCount ,
ISNULL(SUM(Sale_TotalMoney), 0) AS Sale_TotalMoney ,
ISNULL(SUM(Sale_TicketSum), 0) AS Sale_TicketSum
FROM dbo.V_StatisticsSaleInfo_All
WHERE Sale_Date >= @StartTime
AND Sale_Date <= @EndTime
GROUP BY CONVERT(VARCHAR(10), Sale_Date, 120) ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #SaleCacheInfo
SELECT Sale_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Sale_TicketCount), 0) AS Sale_TicketCount ,
ISNULL(SUM(Sale_PersonCount), 0) AS Sale_PersonCount ,
ISNULL(SUM(Sale_TotalMoney), 0) AS Sale_TotalMoney ,
ISNULL(SUM(Sale_TicketSum), 0) AS Sale_TicketSum
FROM dbo.Stat_Sale_Cache_T
WHERE Sale_Date >= CONVERT(VARCHAR(10), @StartTime, 120)
AND Sale_Date <= CONVERT(VARCHAR(10), @EndTime, 120)
GROUP BY Sale_Date ,
Ticket_ID ,
PaymentType_ID
------------------------------------------将有差异的数据存入临时表-------------------------------------
INSERT INTO #Stat_CompressDate
( OperateDate ,
DataType ,
StatisticType
)
SELECT t1.Sale_Date AS OperateDate ,
'Sale' AS DataType ,
'SaleDate' AS StatisticType
FROM #SaleInfo t1
LEFT JOIN #SaleCacheInfo t2 ON t1.Sale_Date = t2.Sale_Date
AND t1.Ticket_ID = t2.Ticket_ID
AND t1.PaymentType_ID = t2.PaymentType_ID
WHERE ( t1.Sale_TicketCount <> t2.Sale_TicketCount
OR t1.Sale_PersonCount <> t2.Sale_PersonCount
OR t1.Sale_TotalMoney <> t2.Sale_TotalMoney
OR t1.Sale_TicketSum <> t2.Sale_TicketSum
)
OR t2.Sale_Date IS NULL;
END;
ELSE
IF @StatisticType = 'PlayDate'
BEGIN
INSERT INTO #SaleInfo
SELECT CONVERT(VARCHAR(10), Play_Date, 120) AS Sale_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Sale_TicketCount), 0) AS Sale_TicketCount ,
ISNULL(SUM(Sale_PersonCount), 0) AS Sale_PersonCount ,
ISNULL(SUM(Sale_TotalMoney), 0) AS Sale_TotalMoney ,
ISNULL(SUM(Sale_TicketSum), 0) AS Sale_TicketSum
FROM dbo.V_StatisticsSaleInfo_All
WHERE Play_Date >= @StartTime
AND Play_Date <= @EndTime
GROUP BY CONVERT(VARCHAR(10), Play_Date, 120) ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #SaleCacheInfo
SELECT Play_Date AS Sale_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Sale_TicketCount), 0) AS Sale_TicketCount ,
ISNULL(SUM(Sale_PersonCount), 0) AS Sale_PersonCount ,
ISNULL(SUM(Sale_TotalMoney), 0) AS Sale_TotalMoney ,
ISNULL(SUM(Sale_TicketSum), 0) AS Sale_TicketSum
FROM dbo.Stat_Sale_Cache_T
WHERE Play_Date >= CONVERT(VARCHAR(10), @StartTime, 120)
AND Play_Date <= CONVERT(VARCHAR(10), @EndTime, 120)
GROUP BY Play_Date ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #Stat_CompressDate
( OperateDate ,
DataType ,
StatisticType
)
SELECT t1.Sale_Date AS OperateDate ,
'Sale' AS DataType ,
'PlayDate' AS StatisticType
FROM #SaleInfo t1
LEFT JOIN #SaleCacheInfo t2 ON t1.Sale_Date = t2.Sale_Date
AND t1.Ticket_ID = t2.Ticket_ID
AND t1.PaymentType_ID = t2.PaymentType_ID
WHERE ( t1.Sale_TicketCount <> t2.Sale_TicketCount
OR t1.Sale_PersonCount <> t2.Sale_PersonCount
OR t1.Sale_TotalMoney <> t2.Sale_TotalMoney
OR t1.Sale_TicketSum <> t2.Sale_TicketSum
)
OR t2.Sale_Date IS NULL;
END
END
-------------------------------------------比较退票数据--------------------------------------------
ELSE
IF @DataType = 'Return'
BEGIN
IF @StatisticType = 'SaleDate'
BEGIN
----------------------------原始数据和压缩表数据存入临时表------------------------------------------
INSERT INTO #ReturnInfo
SELECT CONVERT(VARCHAR(10), Cancel_Date, 120) AS Cancel_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Cancel_TicketCount), 0) AS Cancel_TicketCount ,
ISNULL(SUM(Cancel_PersonCount), 0) AS Cancel_PersonCount ,
ISNULL(SUM(Cancel_TotalMoney), 0) AS Cancel_TotalMoney ,
ISNULL(SUM(Cancel_TicketSum), 0) AS Cancel_TicketSum
FROM dbo.V_StatisticsReturnInfo_All
WHERE Cancel_Date >= @StartTime
AND Cancel_Date <= @EndTime
GROUP BY CONVERT(VARCHAR(10), Cancel_Date, 120) ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #ReturnCacheInfo
SELECT Cancel_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Cancel_TicketCount), 0) AS Cancel_TicketCount ,
ISNULL(SUM(Cancel_PersonCount), 0) AS Cancel_PersonCount ,
ISNULL(SUM(Cancel_TotalMoney), 0) AS Cancel_TotalMoney ,
ISNULL(SUM(Cancel_TicketSum), 0) AS Cancel_TicketSum
FROM dbo.Stat_Cancel_Cache_T
WHERE Cancel_Date >= CONVERT(VARCHAR(10), @StartTime, 120)
AND Cancel_Date <= CONVERT(VARCHAR(10), @EndTime, 120)
GROUP BY Cancel_Date ,
Ticket_ID ,
PaymentType_ID
------------------------将有差异的数据存入临时表---------------------------------------------------
INSERT INTO #Stat_CompressDate
( OperateDate ,
DataType ,
StatisticType
)
SELECT t1.Cancel_Date AS OperateDate ,
'Return' AS DataType ,
'SaleDate' AS StatisticType
FROM #ReturnInfo t1
LEFT JOIN #ReturnCacheInfo t2 ON t1.Cancel_Date = t2.Cancel_Date
AND t1.Ticket_ID = t2.Ticket_ID
AND t1.PaymentType_ID = t2.PaymentType_ID
WHERE ( t1.Cancel_TotalMoney <> t2.Cancel_TotalMoney
OR t1.Cancel_PersonCount <> t2.Cancel_PersonCount
OR t2.Cancel_TicketCount <> t1.Cancel_TicketCount
OR t1.Cancel_TicketSum <> t2.Cancel_TicketSum
)
OR t2.Cancel_Date IS NULL;
END
ELSE
IF @StatisticType = 'PlayDate'
BEGIN
INSERT INTO #ReturnInfo
SELECT CONVERT(VARCHAR(10), Play_Date, 120) AS Cancel_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Cancel_TicketCount), 0) AS Cancel_TicketCount ,
ISNULL(SUM(Cancel_PersonCount), 0) AS Cancel_PersonCount ,
ISNULL(SUM(Cancel_TotalMoney), 0) AS Cancel_TotalMoney ,
ISNULL(SUM(Cancel_TicketSum), 0) AS Cancel_TicketSum
FROM dbo.V_StatisticsReturnInfo_All
WHERE Play_Date >= @StartTime
AND Play_Date <= @EndTime
GROUP BY CONVERT(VARCHAR(10), Play_Date, 120) ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #ReturnCacheInfo
SELECT Play_Date AS Cancel_Date ,
Ticket_ID ,
PaymentType_ID ,
ISNULL(SUM(Cancel_TicketCount),
0) AS Cancel_TicketCount ,
ISNULL(SUM(Cancel_PersonCount),
0) AS Cancel_PersonCount ,
ISNULL(SUM(Cancel_TotalMoney),
0) AS Cancel_TotalMoney ,
ISNULL(SUM(Cancel_TicketSum),
0) AS Cancel_TicketSum
FROM dbo.Stat_Cancel_Cache_T
WHERE Play_Date >= CONVERT(VARCHAR(10), @StartTime, 120)
AND Play_Date <= CONVERT(VARCHAR(10), @EndTime, 120)
GROUP BY Play_Date ,
Ticket_ID ,
PaymentType_ID
INSERT INTO #Stat_CompressDate
( OperateDate ,
DataType ,
StatisticType
)
SELECT t1.Cancel_Date AS OperateDate ,
'Return' AS DataType ,
'SaleDate' AS StatisticType
FROM #ReturnInfo t1
LEFT JOIN #ReturnCacheInfo t2 ON t1.Cancel_Date = t2.Cancel_Date
AND t1.Ticket_ID = t2.Ticket_ID
AND t1.PaymentType_ID = t2.PaymentType_ID
WHERE ( t1.Cancel_TotalMoney <> t2.Cancel_TotalMoney
OR t1.Cancel_PersonCount <> t2.Cancel_PersonCount
OR t2.Cancel_TicketCount <> t1.Cancel_TicketCount
OR t1.Cancel_TicketSum <> t2.Cancel_TicketSum
)
OR t2.Cancel_Date IS NULL;
END
END
DECLARE @Stat_CompressDate AS Stat_CompressDate;
INSERT INTO @Stat_CompressDate
SELECT *
FROM #Stat_CompressDate;
EXEC dbo.Stat_CreateStatisticsData @Stat_CompressDate;
DROP TABLE #Stat_CompressDate;
END;（1）对于需要传入的参数，定义在开头@StartTime DATETIME
（2）sqlserver比较日期：DATEDIFF(DAY, @StartTime, GETDATE())
（3）条件判断不符合，退出该存储过程
BEGIN RETURN; END;
（4）日期设为前一天
SET @EndTime = CONVERT(VARCHAR(10), DATEADD(DAY, -1, @EndTime), 120)+ ' 23:59:59';
（5）定义一个存储过程内需要用到的变量，并给变量赋值
DECLARE @DayCount INT;
SELECT @DayCount = DATEDIFF(DAY, @StartTime, @EndTime);
（6）判断日期的时分秒
CONVERT(VARCHAR(8), @StartTime, 8) <> '00:00:00'
（7）创建临时表
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb..#SaleInfo')
AND type = 'U' )
DROP TABLE #SaleInfo;
CREATE TABLE #SaleInfo
(
Sale_Date DATETIME ,
Ticket_ID BIGINT ,
PaymentType_ID BIGINT ,
Sale_TicketCount INT ,
Sale_PersonCount INT ,
Sale_TotalMoney DECIMAL ,
Sale_TicketSum DECIMAL
)
（8）向临时表插入数据
INSERT INTO #SaleInfo + 查询语句
（9）表和视图的区别：表是实际存在的，视图是抽象的
（10）调用别的存储过程
EXEC dbo.Stat_CreateStatisticsData @Stat_CompressDate;
扫一扫
1410
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
