行转列问题,写不清楚,求助

2019-01-25 03:07:46 471 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

当勒索软件盯上虚拟化层：从CVE-2025-22225漏洞看ESXi安全加固要点本文深度剖析了针对VMware ESXi虚拟化层的CVE-2025-22225等漏洞链的攻击原理，揭示了攻击者如何利用VMCI、HGFS组件从虚拟机逃逸至宿主机。文章从实战角度出发，系统性地提出了ESXi安全加固的核心策略，包括禁用非必要服务、强化主机配置、实施网络分段、建立实时监控与自动化补丁管理体系，为企业构建纵深防御体系提供了具体可行的操作指南。阅读详情

原始sql如下:



SELECT C.ADMIN_UNIT_CODE,C.ADMIN_UNIT_NAME AS [部门],CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) AS [记账日期],

CONVERT(decimal(18,4),SUM(B.FUNCTION_CURRENCY_AMT)) AS [本币金额]

FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID

	JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID

WHERE A.BOOKKEEPING_DATE BETWEEN '2018-12-01' AND '2018-12-31'

GROUP BY C.ADMIN_UNIT_NAME, A.BOOKKEEPING_DATE, C.ADMIN_UNIT_CODE

ORDER BY C.ADMIN_UNIT_CODE

我写成如下内容:





DECLARE @JZRQ	VARCHAR(4000),

		@SQL1	VARCHAR(4000)



SELECT @JZRQ = ISNULL(@JZRQ+',','')+ QUOTENAME(CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120)) FROM dbo.VOUCHER A  GROUP BY A.BOOKKEEPING_DATE

SET @SQL1 = 'SELECT * 

FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID

	JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID 

    PIVOT(SUM(SUM(B.FUNCTION_CURRENCY_AMT)) FOR CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) IN( '+ @JZRQ + '))X'

EXEC(@SQL1)

提示语法错误?

...全文

FPGA实现高效数字下变频：CORDIC与CIC滤波器的Verilog优化设计本文深入探讨了FPGA实现高效数字下变频的技术方案，重点介绍了CORDIC算法与CIC滤波器的Verilog优化设计。通过详细的算法原理分析、FPGA实现技巧和实战经验分享，展示了如何利用CORDIC的旋转运算替代乘法操作，以及CIC滤波器在降采样中的高效性能。文章还提供了完整的系统集成方案和资源优化策略，为无线通信和雷达信号处理领域的工程师提供了实用参考。阅读详情

钛元素

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

唐诗三百首 2019-01-28

打赏
举报

Try this,



declare @JZRQ nvarchar(max),

        @SQL1 nvarchar(max)

 

select @JZRQ=isnull(@JZRQ+N',',N'')

             +quotename(convert(nvarchar,dateadd(d,number,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER)),23)) 

 from master.dbo.spt_values

 where type=N'P'

 and number<=datediff(d,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER),

                        (select max(BOOKKEEPING_DATE) from dbo.VOUCHER))

 

set @SQL1=N'select admin_unit_name,VOUCHER_ID,'+@JZRQ

         +N'from (select C.admin_unit_name,A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT

                  from dbo.VOUCHER A 

                  inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID

                  inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t

            pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'

 

exec(@SQL1)

钛元素 2019-01-28

打赏
举报

可是,已经里面已经关联了啊,ADMIN_UNIT 取 ADMIN_UNIT.admin_unit_name
我还是改不了
有点不懂,请教

唐诗三百首 2019-01-28

打赏
举报

需在动态SQL里关联部门表.

钛元素 2019-01-28

打赏
举报

版主高手!版主大大!
可是这样,一来无法筛选日期,二来无法列出部门名称,是需要再次关联吗?

另外如何学习,才能到你这种程度啊?

钛元素 2019-01-28

打赏
举报

非常感谢唐诗版主的帮助,表结构很复杂,虽然我也可以生成sql脚本出来,但是关联太多了,数据insert的方式更是麻烦,还是不麻烦了.谢谢.
我决定生成表后,再处理.

唐诗三百首 2019-01-28

打赏
举报

请提供表结构, 测试数据, 处理逻辑和希望结果.

钛元素 2019-01-28

打赏
举报

引用 10 楼唐诗三百首的回复:

Try this,



declare @JZRQ nvarchar(max),

        @SQL1 nvarchar(max)

 

select @JZRQ=isnull(@JZRQ+N',',N'')

             +quotename(convert(nvarchar,dateadd(d,number,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER)),23)) 

 from master.dbo.spt_values

 where type=N'P'

 and number<=datediff(d,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER),

                        (select max(BOOKKEEPING_DATE) from dbo.VOUCHER))

 

set @SQL1=N'select admin_unit_name,VOUCHER_ID,'+@JZRQ

         +N'from (select C.admin_unit_name,A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT

                  from dbo.VOUCHER A 

                  inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID

                  inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t

            pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'

 

exec(@SQL1)

这样的话,部门会重复很多, 记录也多条了无法group by了吗?
或者我们再做一个sql 再关联?

唐诗三百首 2019-01-26

打赏
举报

每个自然日做列的写法,



declare @JZRQ nvarchar(max),

        @SQL1 nvarchar(max)



select @JZRQ=isnull(@JZRQ+N',',N'')

             +quotename(convert(nvarchar,dateadd(d,number,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER)),23)) 

 from master.dbo.spt_values

 where type=N'P'

 and number<=datediff(d,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER),

                        (select max(BOOKKEEPING_DATE) from dbo.VOUCHER))



set @SQL1=N'select VOUCHER_ID,'+@JZRQ

         +N'from (select A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT

	              from dbo.VOUCHER A 

	              inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID

                  inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t

            pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'



exec(@SQL1)

唐诗三百首 2019-01-26

打赏
举报



declare @JZRQ nvarchar(max),

        @SQL1 nvarchar(max)

 

select @JZRQ=isnull(@JZRQ+N',',N'')

             +quotename(convert(nvarchar,BOOKKEEPING_DATE,23)) 

 from dbo.VOUCHER

 group BY BOOKKEEPING_DATE



set @SQL1=N'select VOUCHER_ID,'+@JZRQ

         +N'from (select A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT

	              from dbo.VOUCHER A 

	              inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID

                  inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t

            pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'



exec(@SQL1)

钛元素 2019-01-25

打赏
举报

引用 3 楼吉普赛的歌的回复:

在微软官方文档中， ntext 属于淘汰类型，建议改成 nvarchar(max)
https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017
你改类型了再试呢？

改过了,一样的,我现在这样:





DECLARE @SQL3	nVARCHAR(4000)

SELECT @sql3 = ISNULL(@sql3 + '],[' , '') + CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) FROM VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID GROUP BY A.BOOKKEEPING_DATE

SET @sql3 = '[' + @sql3 + ']'

EXEC ('select * from 

SELECT * FROM (

SELECT C.ADMIN_UNIT_CODE,C.ADMIN_UNIT_NAME AS [部门],CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) AS [记账日期],

CONVERT(decimal(18,4),SUM(B.FUNCTION_CURRENCY_AMT)) AS [本币金额]

FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID

JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID

WHERE A.BOOKKEEPING_DATE BETWEEN ''2018-12-01'' AND ''2018-12-31''

GROUP BY C.ADMIN_UNIT_NAME, A.BOOKKEEPING_DATE, C.ADMIN_UNIT_CODE

--ORDER BY C.ADMIN_UNIT_CODE

) X 

pivot (SUM(B.FUNCTION_CURRENCY_AMT) for A.BOOKKEEPING_DATE in (' + @sql3 + ')) b')

提示语法错误,如果我去除convert,又提示我:
数据类型 nvarchar 和 datetime2 在 add 运算符中不兼容。

吉普赛的歌版主 2019-01-25

打赏
举报

在微软官方文档中， ntext 属于淘汰类型，建议改成 nvarchar(max) https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017 你改类型了再试呢？

钛元素 2019-01-25

打赏
举报

不过我@JZRQ的内容是这样:[2018-08-31],[2018-09-30],[2018-10-01],[2018-10-19],[2018-10-22],[2018-10-23],[2018-10-24],[2018-10-29],[2018-10-30],[2018-10-31]
要怎么办?我就是想要日期列做列啊

另外,最好每个自然日做列,值为空没关系,这是最好的了.怎么做啊?

钛元素 2019-01-25

打赏
举报

改成这样



DECLARE @JZRQ	VARCHAR(4000),

		@SQL1	VARCHAR(4000)



SELECT @JZRQ = ISNULL(@JZRQ+',','')+ QUOTENAME(CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120)) FROM dbo.VOUCHER A  GROUP BY A.BOOKKEEPING_DATE

SET @SQL1 = 'SELECT * 

FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID

	JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID 

    PIVOT(SUM(B.FUNCTION_CURRENCY_AMT) FOR A.BOOKKEEPING_DATE IN( '+ @JZRQ + '))X'

EXEC(@SQL1)

提示:

Pivot grouping 列必须是可比的。列 "Attachments" 的类型是 "ntext"，该类型不可比。

行转列问题,写不清楚,求助

相关推荐