动态生成像柜子管理的柜子号，用于DataGridView 绑定显示

最新推荐文章于 2021-10-18 21:55:13 发布

伦家程序猿

最新推荐文章于 2021-10-18 21:55:13 发布

阅读量235

点赞数

分类专栏： Sql 文章标签： sql server while 遍历

本文链接：https://blog.csdn.net/pljopljo/article/details/88940750

版权

Sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

之前朋友找我，他项目遇到这么一个问题

他系统显示了这样的一个图做箱柜管理，但是他客户要求不是这样，要如图二一般

图一

图二

于是我给他说做个一存储过程然后绑定，显示，至于其他数据可以和其他数据表关联显示

于是我就试一下动手，我发现有点意思。用了一个笨方法，于是记录一下。

建立一个基础表

CREATE TABLE [dbo].[tbl_def](
   [fldrow] [INT] NULL,
   [fldcolum] [INT] NULL,
   [fldBox] [INT] NULL,
   [fldid] [INT] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl_def] PRIMARY KEY CLUSTERED
(
   [fldid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

存储过程：
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @licol INT;
--SET @licol=2--一个柜子有几列
DECLARE @row INT;
DECLARE @col INT;
DECLARE @box INT;
DECLARE @Fcol INT;
SELECT @row = fldrow ,
@col =fldBox*fldcolum，--@licol,--fldcolum ,
@box = fldBox
FROM dbo.tbl_def
WHERE fldid = 1;
SET @Fcol = @col / @box;
DECLARE @i INT;
DECLARE @j INT;
SET @i = 1;
SET @j = 1;
DECLARE @Query NVARCHAR(MAX);
SET @Query = ' DECLARE @temp TABLE ( id [nvarchar](50) ,';

DECLARE @iQuery NVARCHAR(MAX);
SET @iQuery = ' insert into @temp( id, ';
WHILE @i <= @col
BEGIN
IF @i != @col
BEGIN
SET @Query = @Query + 'col' + CONVERT(NVARCHAR(10), @i)
+ ' [nvarchar](50) ,';
SET @iQuery = @iQuery + 'col'
+ CONVERT(NVARCHAR(10), @i) + ' ,';
END;
ELSE
BEGIN

SET @Query = @Query + 'col' + CONVERT(NVARCHAR(10), @i)
+ ' [nvarchar](50) );';
SET @iQuery = @iQuery + 'col'
+ CONVERT(NVARCHAR(10), @i) + ' )';
END;
SET @i = @i + 1;
END;
DECLARE @j1Query NVARCHAR(MAX);
SET @j1Query = ' select 1,';
SET @i = 1;
DECLARE @tempf INT;
SET @tempf = 1;
--PRINT CONVERT(NVARCHAR(10),@row)
WHILE @i <= @row
BEGIN
SET @j = 1;

WHILE @j <= @col
BEGIN
-- PRINT CONVERT(NVARCHAR(10),@i) +'ddd '+CONVERT(NVARCHAR(10),@j)+'dddd '+CONVERT(NVARCHAR(10),@col)
IF @j != @col
BEGIN
PRINT 'i '+ CONVERT(NVARCHAR(10),@i)
+' j '+CONVERT(NVARCHAR(10),@j)
+' Row '+CONVERT(NVARCHAR(10),@row)
+' Column '+CONVERT(NVARCHAR(10),@col)
+' 求余 '+CONVERT(NVARCHAR(10),@Fcol)
+' 求余1 '+CONVERT(NVARCHAR(10),@col % @Fcol)

SET @j1Query = @j1Query + ''''
+ CONVERT(NVARCHAR(10), CEILING( CAST(@j*1.0/@Fcol AS FLOAT) )) + '-'
+ CONVERT(NVARCHAR(10), @i + @row
* (@tempf-1)) + ''' ,';
PRINT @j1Query --test
END;
ELSE
BEGIN
SET @j1Query = @j1Query + ''''
+ CONVERT(NVARCHAR(10), CEILING( CAST(@j*1.0/@Fcol AS FLOAT) )) + '-'
+ CONVERT(NVARCHAR(10), @i + @row
* (@tempf-1)) + ''' ';
PRINT @j1Query --test
IF @i = @row AND @j = @col
BEGIN

SET @j1Query = @j1Query + '; ';

END;
ELSE
BEGIN

SET @j1Query = @j1Query + ' union select '''+CONVERT(NVARCHAR(50),@i+1)+''', ';

END;

END;
IF ( @j % @Fcol = 0)--12%3
BEGIN
SET @tempf =1;
END;
ELSE
BEGIN
SET @tempf = @tempf + 1;
END

SET @j = @j + 1;
END;

SET @i = @i + 1;

END;
DECLARE @FQuery NVARCHAR(MAX);

PRINT @j1Query

SET @FQuery = @Query + @iQuery + @j1Query + ' select * from @temp ';

EXEC sp_executesql @FQuery;

END;