USE [irepodb]
GO
/****** Object: StoredProcedure [dbo].[P_12#GG] Script Date: 2025/9/17
10:15:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery24.sql|7|0|C:\Users\jinfeng_xiang\AppData\Local\Temp\~vs3A56.sql
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P_12#GG]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @val VARCHAR(
10),@displayValue VARCHAR(
10),@Date VARCHAR(
10),@celladdress VARCHAR(15),@rep_
top_id VARCHAR(
10),@def_
top_id VARCHAR(
10),
@error int ,@differ VARCHAR(
10),@workpiece VARCHAR(
10),@specs VARCHAR(
10),@time VARCHAR(
10),@remarks nvarchar(50) ,@sheetNo VARCHAR(
10) ,@def_
top_org VARCHAR(
10)
set @error=0
INSERT INTO T_IREPORT_TREND SELECT * FROM T_12#_TREND where DEF_
TOP_ID IN (
SELECT [T_DEF_
TOP_ID] FROM [irepodb].[dbo].[T_
LIMIT]
WHERE T_LINETYPE IN ('1#_GG','2#_GG','12#_GG')
GROUP BY T_DEF_
TOP_ID)
DELETE FROM T_12#_TREND WHERE DEF_
TOP_ID IN (
SELECT [T_DEF_
TOP_ID] FROM [irepodb].[dbo].[T_
LIMIT]
WHERE T_LINETYPE IN ('1#_GG','2#_GG','12#_GG')
GROUP BY T_DEF_
TOP_ID)
-- begin tran
--申明事务
declare row_cursor_col cursor
FOR (SELECT displayValue ,celladdress,rep_
top_id,def_
top_id,sheetNo,def_
top_org from [irepodb].[dbo].[T_12#]
WHERE (type='InputNumeric' or type='Calculate')
AND CONVERT(int ,REPLACE( RIGHT(rtrim(CELLADDRESS), 2),'$','')) >= '7'
and def_
top_org in ( SELECT [T_DEF_
TOP_ID] FROM [irepodb].[dbo].[T_
LIMIT]
WHERE T_LINETYPE IN ('1#_GG','2#_GG','12#_GG')
GROUP BY T_DEF_
TOP_ID
)
and displayValue is not null
)
open row_cursor_col
--开始循环游标变量
--
fetch next from row_cursor_col into @displayValue,@celladdress,@rep_
top_Id,@def_
top_id,@sheetNo,@def_
top_org
while @@FETCH_STATUS = 0
--返回被 FETCH语句执行的最后游标的状态
--
begin
SELECT @Date=ISNULL(displayValue,0) from [irepodb].[dbo].[T_12#] WHERE
REPLACE(SUBSTRING(CELLADDRESS,2,2),'$','')=REPLACE(SUBSTRING(@celladdress,2,2),'$','')
and type='Date'
AND REPLACE( RIGHT(rtrim(CELLADDRESS), 2),'$','') = '2'
and @rep_
top_id = rep_
top_id
and @def_
top_id=def_
top_id
and @sheetNo=sheetNo
SELECT @time=ISNULL(displayValue,0) from [irepodb].[dbo].[T_12#] WHERE
REPLACE(SUBSTRING(CELLADDRESS,2,2),'$','')=REPLACE(SUBSTRING(@celladdress,2,2),'$','')
and type='Time'
-- and name='时间'
AND REPLACE( RIGHT(rtrim(CELLADDRESS), 2),'$','') = '3'
and @rep_
top_id = rep_
top_id
and @def_
top_id=def_
top_id
and @sheetNo=sheetNo
SELECT @specs=ISNULL(displayValue,0) from [irepodb].[dbo].[T_12#] WHERE
REPLACE(SUBSTRING(CELLADDRESS,2,2),'$','')=REPLACE(SUBSTRING(@celladdress,2,2),'$','')
and type='KeyboardText'
-- and name='工件号'
AND REPLACE( RIGHT(rtrim(CELLADDRESS), 2),'$','') = '5'
and @rep_
top_id = rep_
top_id
and @def_
top_id=def_
top_id
and @sheetNo=sheetNo
SELECT @workpiece=ISNULL(displayValue,0) from [irepodb].[dbo].[T_12#] WHERE
REPLACE(SUBSTRING(CELLADDRESS,2,2),'$','')=REPLACE(SUBSTRING(@celladdress,2,2),'$','')
and type='Select'
-- and name='线别'
AND REPLACE( RIGHT(rtrim(CELLADDRESS), 2),'$','') = '6'
and @rep_
top_id = rep_
top_id
and @def_
top_id=def_
top_id
and @sheetNo=sheetNo
-- print @time
-- print @Date
-- print @workpiece
-- print @displayValue
-- print @specs
-- print @celladdress
-- print @rep_
top_id
insert into T_12#_TREND (ID,Date,TIME,Workpiece,DisplayValue,COL,Specs,updateTime,rep_
top_id,def_
top_id,sheetNo,Remarks) values(NEWID(),
@Date,@time,@workpiece,@displayValue,REPLACE(@celladdress,' ',''),@specs,GETDATE(),@rep_
top_id,@def_
top_org,@sheetNo,'
103')
set @error= @error + @@ERROR
--记录每次运行sql后是否正确，0正确
fetch next from row_cursor_col into @displayValue,@celladdress,@rep_
top_Id,@def_
top_id,@sheetNo,@def_
top_org
--转到下一个游标
end
INSERT INTO T_12#KANBAN
SELECT C.rep_
top_id AS 报表编码, C.def_
top_id AS 定义编码, AA.T_LINETYPE AS 线别类型, AA.T_PROJECTID AS 项目序号,
AA.T_OP AS 工序, AA.T_PROJECT AS 测定项目, CONVERT(varchar(
100), C.DATE, 23) AS YMD, RTRIM(C.TIME) AS 时间, RTRIM(C.Workpiece) AS 线别,
AA.T_RTYPE AS 部位, C.COL AS 单元格, C.Specs AS 工件号, CONVERT(decimal(18, 2), C.DisplayValue) AS 值, C.sheetNo, AA.T_UP_
LIMIT,
AA.T_LOWER_
LIMIT,GETDATE(),
CASE WHEN AA.T_LOWER_
LIMIT IS NULL THEN
(CONVERT(decimal(18, 2), C.DisplayValue))/AA.T_UP_
LIMIT
ELSE (CONVERT(decimal(18, 2), C.DisplayValue)
-AA.T_LOWER_
LIMIT)/(AA.T_UP_
LIMIT-AA.T_LOWER_
LIMIT) end as offset
FROM dbo.T_12#_TREND AS C LEFT OUTER JOIN
dbo.T_
LIMIT AS AA ON AA.T_DEF_
TOP_ID = C.def_
top_id
AND AA.T_ROW = REPLACE(RIGHT(RTRIM(C.COL), 2), '$', '')
AND AA.T_SHEETNO = C.sheetNo
WHERE (REPLACE(SUBSTRING(RTRIM(C.COL), 2, 2), '$', '') IN ('J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH',
'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO'))
AND (AA.T_OP IS NOT NULL)
AND (T_HD != '7' OR T_HD IS NULL)
AND def_
top_id in (SELECT [T_DEF_
TOP_ID] FROM [irepodb].[dbo].[T_
LIMIT]
WHERE T_LINETYPE IN ('1#_GG','2#_GG','12#_GG')
GROUP BY T_DEF_
TOP_ID)
GROUP BY C.rep_
top_id, AA.T_LINETYPE, AA.T_PROJECT, AA.T_PROJECTID, AA.T_OP, AA.T_RTYPE, C.DATE, C.TIME, RTRIM(C.Workpiece), C.COL, C.Specs,
C.DisplayValue, C.def_
top_id, AA.T_UP_
LIMIT, AA.T_LOWER_
LIMIT, C.sheetNo
-- if @error=0
--begin
-- commit tran
--提交事务
--end
-- else
--begin
-- rollback tran
--回滚事务
--end
close row_cursor_col
--关闭游标
deallocate row_cursor_col
--释放游标
END
本文介绍了在MySQL中如何使用LIMIT关键字来实现TOP N查询及指定范围内的记录查询。LIMIT不仅可以用于获取表中的前几条记录，还可以灵活地选取任意区间的记录。
扫一扫
2239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
