随笔分类 -  SQL Server

摘要：IF OBJECT_ID('tempdb..#Purchase', 'U') IS NOT NULL DROP TABLE #Purchase; CREATE TABLE #Purchase ( PurchaseID INT IDENTITY(1, 1) , CustomerID INT , Pro 阅读全文

摘要：CREATE TABLE #tb ( id INT IDENTITY , LocationName VARCHAR(24) , LocationIndex INT NULL )INSERT INTO #tb ( LocationName ) SELECT '1015-02A01' UNION SELECT '1015-01A01' UNION SELECT '1016-04A01' UNION SELECT '1016-05A01' UNION SELECT '1017-01A01' UNION SELECT &# 阅读全文

摘要：C:\Users\{用户名}\AppData\Roaming\Microsoft\MicrosoftSQLServer\100\Tools\Shell\ 阅读全文

摘要：数据源NameAreaNameqtySpecific叶玲1601叶玲211叶玲610叶玲750叶玲811显示效果： Name128其它总数叶玲6011668规则：Specific=1的要单独统计，Specific=0的合并统计--> 测试数据：#tb IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([Name] VARCHAR(4),[AreaName] INT,[qty] INT,[Specific] INT) INSERT #tb SELECT '叶玲& 阅读全文

摘要：USE wms;--> 测试数据：#taIF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #taGO CREATE TABLE #ta([name] VARCHAR(10))INSERT #taSELECT 'a' UNION ALLSELECT 'b' UNION ALLSELECT 'd'--> 测试数据：#tbIF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tbGO CREATE 阅读全文

摘要：以下是带批注的 PIVOT 语法。SELECT ,[第一个透视的列] AS ,[第二个透视的列] AS ,...[最后一个透视的列] AS ,FROM()AS PIVOT(()FOR[]IN ( [第一个透视的列], [第二个透视的列],... [最后一个透视的列])) AS ;示例1：CREATE... 阅读全文

摘要：CREATETABLE[ptable]([id][int]NULL,[pid][int]NULL,[name][nchar](10))GOINSERTINTOptableVALUES(1,0,'a')INSERTINTOptableVALUES(2,0,'b')INSERTINTOptableVALUES(3,1,'c')INSERTINTOptableVALUES(4,1,'d')INSERTINTOptableVALUES(5,2,'e')INSERTINTOptableVALUES(6,3,'f 阅读全文

摘要：select*fromcdselect*fromcd_2updatecdsettitle=cd_2.title,price=cd_2.priceoutputdeleted.*,inserted.*fromcd_2wherecd.id=cd_2.id 阅读全文

摘要：--临时表，tempdb，手工删除SELECTv.SONo,v.OrderNo,v.LogisticsCompanyCode,so.OrderIdINTO#aFROMView_CourierReceiptvWITH(NOLOCK)LEFTJOINShippingOrdersoWITH(NOLOCK)ONso.OrderNo=v.OrderNoWHEREv.CourierReceiptNo='EMS000000002'select*from#aselect*from#adroptable#a--CTE，内存，自动删除withaas(SELECTv.SONo,v.OrderNo,v 阅读全文

摘要：SQL 将一列数据转化为字符串SELECTDISTINCTu1.[ItemCode],REPLACE((SELECTu2.[DetailDescription]+','AS'data()'FROM[YinTaiContent].[dbo].ItemProfileDetailsu2WHEREu2.[ItemCode]=u1.[ItemCode]FORXMLPATH(''))+'$',',$','')ASRolesFROM[dbo].ItemProfileDetailsu1WHEREu1.ItemCod 阅读全文