11,849
社区成员
发帖
与我相关
我的任务
分享
--===== 如果该测试表已经存在, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== 建个测试表
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
Value DECIMAL(9,4),
YearValue INT,
MonthValue INT
)
--===== 建立其他特殊要求的条件, 尤其是需要考虑时间类型时
SET DATEFORMAT DMY
SELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(DateValue,'''')+','
+ QUOTENAME(Value,'''')+','
+ QUOTENAME(YearValue,'''')+','
+ QUOTENAME(MonthValue,'''')
+ ' UNION ALL'
FROM yourtable
SELECT '4','Oct 17 2007 12:00AM','5.1709','8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM','5.5319','17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM','5.5793','21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM','5.2471','9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM','5.1177','7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM','5.5510','18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM','5.5128','16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM','5.5758','20','10' UNION ALL
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value, YearValue, Monthvalue)
SELECT '4','Oct 17 2007 12:00AM','5.1709','8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM','5.5319','17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM','5.5793','21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM','5.2471','9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM','5.1177','7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM','5.5510','18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM','5.5128','16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM','5.5758','20','10'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
--===== 如果测试表已经存在, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== 建个测试表
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --在实际的表中是IDENTITY字段
DateValue DATETIME,
Value MONEY,
YearValue INT,
MonthValue INT
)
--===== 建立其他特殊要求的条件, 特别是需要考虑时间类型时
SET DATEFORMAT DMY
SELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(DateValue,'''')+','
+ CAST(Value AS VARCHAR)+','
+ QUOTENAME(YearValue,'''')+','
+ QUOTENAME(MonthValue,'''')
+ ' UNION ALL'
FROM yourtable
SELECT '4','Oct 17 2007 12:00AM',5.1709,'8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM',5.5319,'17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM',5.5793,'21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM',5.2471,'9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM',5.1177,'7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM',5.5510,'18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM',5.5128,'16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM',5.5758,'20','10'
--===== 如果测试表已经存在, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== 建个测试表
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --在实际的表中是IDENTITY字段
DateValue DATETIME,
Value MONEY,
YearValue INT,
MonthValue INT
)
--===== 建立其他特殊要求的条件, 特别是需要考虑时间类型时
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value, YearValue, Monthvalue)
SELECT '4','Oct 17 2007 12:00AM',5.1709,'8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM',5.5319,'17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM',5.5793,'21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM',5.2471,'9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM',5.1177,'7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM',5.5510,'18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM',5.5128,'16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM',5.5758,'20','10'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
select v.* from #mytable v, ( select min(id) as firstidofstagnation from #mytable v, ( select min(id) as lastidbeforestagnation from ( select top 2 value, max(id) as id from #mytable group by value order by max(id) desc )x )ls where v.id > ls.lastidbeforestagnation )fs where v.id = fs.firstidofstagnation
SELECT v.*
FROM #MyTable v,
(--==== Derived table "fs" finds ID of the first row
-- where stanation of VALUE occurs
SELECT MIN(ID) AS FirstIDOfStagnation
FROM #MyTable v,
(--==== Derived table "ls" finds last ID before
--stagnation of VALUE
SELECT MIN(ID) AS LastIDBeforeStagnation
FROM (--==== Derived table "x" finds the ID's of the
-- last value and the value before that
SELECT TOP 2
Value, MAX(ID) AS ID
FROM #MyTable
GROUP BY Value
ORDER BY MAX(ID) DESC
)x
)ls
WHERE v.ID > ls.LastIDBeforeStagnation
)fs
WHERE v.ID = fs.FirstIDOfStagnation