qingwenc的博客

可以根据group by字段，算出其中一个或多个组合的汇总SELECT EmpID,,WorkDateDBNo,GroupName AS ProdLine,COUNT(1) OVER (PARTITION BY DBNo,GroupName,Workdate ) AS CNT, ---CNT表示该生产线每天有多少人参与计件COUNT(1) OVER (PARTITION BY DBNo,GroupName ) AS CNTByProdLine from A GROUP BY EmpID,

--STUFF：从位置1开始，截取1个字符串，替换为'',目的去除第一个逗号--FOR XML PATH('')：行转列拼接字符串STUFF((SELECT distinct ',' + aa.Customer FROM BC_OutQAReport aa FOR XML PATH('')), 1, 1, '') as Customer

Create function [dbo].[f_GetFxNum]()returns varchar(15)asbegindeclare @FxNum varchar(15)declare @time varchar(8)set @time=CONVERT(varchar,YEAR(GETDATE()))+RIGHT('00'+CONVERT(varchar,month(getdate())),2)+RIGHT('00'+CONVERT(varchar,DAY(getdate())),2)--

select * from msdb.dbo.sysjobsselect * from msdb.dbo.sysjobsteps

1.调用的存储过程Create PROCEDURE [db_datareader].[SP_Test]@ActionType NVARCHAR(40) = NULL,@SqlWhere nvarchar(2000)=null,@PageSize int = NULL,@PageIndex int = NULL,@RecordCount bigint=null OUTPUTASBEGINif(@ActionType='Process1') BEGIN exec [spG_Te

print QUOTENAME('A',char(39))--'A'print QUOTENAME('A')--[A]

DECLARE @DATAAREAID AS NVARCHAR(MAX)set @DATAAREAID='001,002,003,004'if object_id('tempdb..##MyTempTable1') is not null Begin DROP TABLE ##MyTempTable1 --如果有存在就删除临时表 End Select * Into ##MyTempTable1 From ( SELECT B.id,B.typeid FROM (

实现动态行转列DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);if object_id('tempdb..##tmp1') is not nullbegin DROP TABLE ##tmp1 endif object_id('tempdb..##tmp2') is not nullbegin DROP TABLE ##tmp2end;with tmpdata as (

给字符串加上单引号set @PartNo2=QUOTENAME(@PartNo,char(39))

CREATE TABLE [dbo].[Calendar] ( [time_id] [int] IDENTITY (1, 1) NOT NULL primary key, [the_date] [datetime] NULL , [the_day] [nvarchar] (15) NULL , [the_month] [nvarchar] (15) NULL , [the_year] [smallint] NULL ,[day_of_month] [smallint] NULL

if object_id(‘tempdb…##MyTemp’) is not nullbeginDROP TABLE ##MyTemp --如果有存在就删除临时表endset @sql_str = ‘SELECT ProdId, ’ + @clos + ’ into ##MyTemp from(select ProdId, Qty, ProdDatefrom A) xpivot(sum(Qty)for ProdDate in (’ + @clos + ')) p ’exec

效果SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome;WITH CET1 AS( SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome),CET2 AS( SELECT ceiling(MAX(idx)/4.0) AS Cnt FROM CET1--如果有28条,这里的值就是7，且只有一行

1.数据的创建create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)--VendorId 供应商ID,--IncomeDay 收入时间--IncomeAmount 收入金额insert into DailyIncome values ('SPIKE', 'FRI', 100)insert into DailyIncome values ('SPIKE', 'MON',