![](https://img-blog.csdnimg.cn/20201014180756927.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
sql
qingwenc
这个作者很懒,什么都没留下…
展开
-
sql server COUNT(1) OVER (PARTITION BY DBNo,GroupName )计算不同组合字段汇总
可以根据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,原创 2021-09-01 16:24:00 · 1442 阅读 · 0 评论 -
sql server 字符串列多行转换为一行拼接和转换为多行
--STUFF:从位置1开始,截取1个字符串,替换为'',目的去除第一个逗号--FOR XML PATH(''):行转列拼接字符串STUFF((SELECT distinct ',' + aa.Customer FROM BC_OutQAReport aa FOR XML PATH('')), 1, 1, '') as Customer原创 2021-08-26 11:07:39 · 1424 阅读 · 0 评论 -
sql server创建流水号 年月日+流水号
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)--原创 2021-08-23 15:09:48 · 666 阅读 · 0 评论 -
sql查看job的语句
select * from msdb.dbo.sysjobsselect * from msdb.dbo.sysjobsteps原创 2021-07-27 17:14:32 · 561 阅读 · 0 评论 -
sql分页查询语句
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原创 2021-06-02 17:18:25 · 1368 阅读 · 0 评论 -
给传进来的sql变量添加单引号(‘‘)
print QUOTENAME('A',char(39))--'A'print QUOTENAME('A')--[A]原创 2021-04-03 16:55:07 · 904 阅读 · 0 评论 -
拼接字符串转化为table并作为查询条件
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 (原创 2021-04-03 16:48:41 · 160 阅读 · 0 评论 -
pivot
实现动态行转列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 (原创 2021-04-03 16:45:08 · 70 阅读 · 0 评论 -
sql函数
给字符串加上单引号set @PartNo2=QUOTENAME(@PartNo,char(39))原创 2021-01-08 17:56:37 · 46 阅读 · 0 评论 -
日期的生成
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原创 2021-01-08 17:53:22 · 88 阅读 · 0 评论 -
pivot实现动态行转列
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原创 2020-12-26 17:55:49 · 250 阅读 · 1 评论 -
sql中实现数据多列显示,减少显示行
效果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,且只有一行原创 2020-09-03 15:39:21 · 348 阅读 · 0 评论 -
sql中pivot函数的使用
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',原创 2020-09-03 14:56:29 · 9362 阅读 · 0 评论