1.行列转换
--列转行
IF (OBJECT_ID('DEPT') IS NOT NULL) DROP TABLE DEPT
CREATE TABLE DEPT(NAME VARCHAR(5),COL1 INT,COL2 INT,COL3 INT,COL4 INT,COL5 INT,COL6 INT)
INSERT INTO DEPT
SELECT 'A',10,50,20,30,0,80 UNION ALL
SELECT 'B',50,20,10,10,20,40 UNION ALL
SELECT 'C',5,0,0,10,0,80
SELECT * FROM DEPT
SELECT NAME,NEWCOLUMNS,VALUE INTO #TEMP
FROM DEPT
UNPIVOT( VALUE FOR NEWCOLUMNS IN(COL1,COL2,COL3,COL4,COL5,COL6 )) AS t
SELECT * FROM #TEMP
/*
NAME NEWCOLUMNS value
A COL1 10
A COL2 50
A COL3 20
A COL4 30
A COL5 0
A COL6 80
B COL1 50
B COL2 20
B COL3 10
B COL4 10
B COL5 20
B COL6 40
C COL1 5
C COL2 0
C COL3 0
C COL4 10
C COL5 0
C COL6 80
*/
--行转列
SELECT * FROM #TEMP
PIVOT(MAX(VALUE) FOR NEWCOLUMNS IN(COL1,COL2,COL3,COL4,COL5,COL6)) AS t
/*
NAME COL1 COL2 COL3 COL4 COL5 COL6
A 10 50 20 30 0 80
B 50 20 10 10 20 40
C 5 0 0 10 0 80
*/
2.每行中的数值统计：每行中各列数据的最大值、最小值、平均值
SELECT *
,MaxValue=(SELECT MAX(COL) FROM (
SELECT COL1 AS COL UNION ALL
SELECT COL2 UNION ALL
SELECT COL3 UNION ALL
SELECT COL4 UNION ALL
SELECT COL5 UNION ALL
SELECT COL6 ) A)
,MinValue=(SELECT MIN(COL) FROM (
SELECT COL1 AS COL UNION ALL
SELECT COL2 UNION ALL
SELECT COL3 UNION ALL
SELECT COL4 UNION ALL
SELECT COL5 UNION ALL
SELECT COL6 ) B)
,AVGValue=(SELECT AVG(COL) FROM (
SELECT COL1 AS COL UNION ALL
SELECT COL2 UNION ALL
SELECT COL3 UNION ALL
SELECT COL4 UNION ALL
SELECT COL5 UNION ALL
SELECT COL6 ) C)
FROM DEPT
/* 结果:
NAME COL1 COL2 COL3 COL4 COL5 COL6 MaxValue MINValue AVGValue
A 10 50 20 30 0 80 80 0 31
B 50 20 10 10 20 40 50 10 25
C 5 0 0 10 0 80 80 0 15
*/
3.每列中的数值统计
DROP TABLE DEPT
CREATE TABLE DEPT(ID INT IDENTITY (1,1),VALUE INT)
INSERT INTO DEPT(VALUE) VALUES(90),(86),(60),(80),(100),(0),(0),(85),(80),(65)
SELECT * FROM DEPT
SELECT ID,VALUE
,id = DENSE_RANK() OVER(ORDER BY VALUE DESC)
,比例 = VALUE*100.0/SUM(VALUE)OVER()
,最大差值 =MAX(VALUE)OVER() - VALUE
,最小差值 =VALUE - MIN(VALUE)OVER()
FROM DEPT
/*结果:
ID VALUE id 比例 最大差值 最小差值
5 100 1 15.479876160990 0 100
1 90 2 13.931888544891 10 90
2 86 3 13.312693498452 14 86
8 85 4 13.157894736842 15 85
9 80 5 12.383900928792 20 80
4 80 5 12.383900928792 20 80
10 65 6 10.061919504643 35 65
3 60 7 9.287925696594 40 60
6 0 8 0.000000000000 100 0
7 0 8 0.000000000000 100 0
*/
4.某部门的所有上级机构或下级机构
DROP TABLE DEPT
CREATE TABLE DEPT(ID INT,PID INT, NAME VARCHAR(20))
INSERT INTO DEPT VALUES
(1,0,'总公司'),
(2,1,'研发部'),
(3,1,'销售部'),
(4,1,'财务部'),
(5,2,'研发一部'),
(6,2,'研发二部'),
(7,3,'销售一部'),
(8,3,'销售二部'),
(9,3,'销售三部'),
(10,5,'小组A'),
(11,5,'小组B')
SELECT * FROM DEPT
--求一个部门的所有下级，如[研发部] 的所有下级'
--条件:所有部门的父id都等于[研发部]的ID,取到都是下级的
;WITH D(ID,PID,NAME,LVL)
AS(
SELECT ID,PID,NAME,0 LVL FROM DEPT WHERE NAME='研发部' UNION ALL
SELECT DEPT.ID,DEPT.PID,DEPT.NAME,LVL +1
FROM DEPT INNER JOIN D ON DEPT.PID=D.ID
)
SELECT * FROM D
--求一个部门的所有上级，如[研发一部] 的所有上级'
;WITH D(ID,PID,NAME,LVL)
AS(
SELECT ID,PID,NAME,0 LVL FROM DEPT WHERE NAME='研发一部' UNION ALL
SELECT DEPT.ID,DEPT.PID,DEPT.NAME,LVL +1
FROM DEPT INNER JOIN D ON DEPT.ID=D.PID
)
SELECT * FROM D
SELECT * FROM DEPT INNER JOIN (SELECT ID,PID,NAME FROM DEPT WHERE NAME='研发部' ) TAB ON DEPT.ID=TAB.ID
5.添加 删除 更新 时取出数据
DROP TABLE #temp
SELECT * FROM DEPT --继续用上一步的表
SELECT NAME INTO #temp FROM DEPT WHERE 1<>1
SELECT * FROM #temp
INSERT INTO #temp(NAME)
output inserted.NAME--into tableName(colName) 输出可插入到其他表
SELECT NAME FROM DEPT
DELETE DEPT
OUTPUT deleted.NAME
WHERE PID = 3
UPDATE #temp
SET NAME = '集团'
OUTPUT Inserted.NAME Old, Deleted.NAME New
WHERE NAME = '总公司'
6.Merge into
create table #a (aid int null,aname varchar(10) null);
create table #b (bid int null,bname varchar(10) null);
insert into #a values(1,'value1');
insert into #a values(3,'value3');
insert into #a values(4,'value4');
insert into #b values(1,'new value1');
insert into #b values(2,'new value2');
insert into #b values(3,'new value3');
merge into #a using #b
on #a.aid=#b.bid
when matched --and #a.aid = 1 （可增加条件）
then update set #a.aname=#b.bname
when not matched
then insert values(#b.bid,#b.bname)
when not matched by source then
delete; --必须分号结束
select * from #a;
select * from #b;
7.多列查询同一值简化
select * from tablename
where col1=100 or col2=100 or col3=100 or col4=100 or col5=100 or col6=100
--简化操作
select * from tablename where 100 in(col1,col2,col3,col4,col5,col6)
8.同列字符相连
use tempdb
go
--drop table tb
create table tb(id int,value varchar(30))
go
insert into tb
values
(1,'aa'),
(1,'bb'),
(2,'aaa'),
(2,'bbb'),
(2,'ccc')
SELECT * FROM tb
SELECT DISTINCT id,STUFF((SELECT ','+value FROM tb B WHERE A.id=B.id FOR XML PATH('')),1,1,'') AS value
FROM tb A
/*结果:
id value
--- -----------
1 aa,bb
2 aaa,bbb,ccc
*/
--逆转换
use tempdb
go
--drop table tb
create table tb(id int,value varchar(30))
go
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
select * from tb
select a.id, b.value
from(
select id, [value] = convert(xml,' <root> <v>' + replace([value], ',', ' </v> <v>') + ' </v> </root>') from tb
)a
outer apply(
select value = n.v.value('.', 'varchar(100)') from a.[value].nodes('/root/v') n(v)
)b
/*结果:
id value
-------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/
9.同列数值分组累加
drop table #temp
create table #temp(name varchar(1),value int)
insert #temp
select 'a',1 union all
select 'b',5 union all
select 'a',3 union all
select 'a',5 union all
select 'b',9 union all
select 'b',5
select * from #temp
;with taba as(
select row_number() over(partition by name order by name) id,name,value from #temp
)
, tabb as(
select id,name,value,value as total from taba where id = 1 union all
select a.id,a.name,a.value,a.value+b.total
from taba a inner join tabb b on a.name=b.name and a.id=b.id+1
)
select * from tabb order by name,id
/*结果:
id name value total
-- ---- ----- ----
1 a 3 3
2 a 5 8
3 a 1 9
1 b 5 5
2 b 9 14
3 b 5 19
*/
10.一条sql语句执行N次
CREATE TABLE TB(ID INT IDENTITY (1,1),NAME VARCHAR(40))
INSERT INTO TB(NAME) SELECT 'KK'+CONVERT(VARCHAR(5),isnull(@@IDENTITY,0)+1)
GO 10
11.随机取出N条记录
select top 5 * from tablename order by newid()
12.年内按月累计
如：2月累计为前两个月的，3月累计为前三个月的
CREATE TABLE T (tDate DATETIME,tValue INT)
INSERT INTO dbo.T
SELECT '2017-01-08',10 UNION
SELECT '2017-01-25',20 UNION
SELECT '2017-02-11',30 UNION
SELECT '2017-02-28',40 UNION
SELECT '2017-03-17',50 UNION
SELECT '2017-04-03',60 UNION
SELECT '2017-04-20',70 UNION
SELECT '2017-05-07',80 UNION
SELECT '2017-05-24',90
SELECT * FROM dbo.T;
SELECT MONTH(tDate) AS 月份,
(SELECT SUM(tValue) FROM dbo.T T1WHERE MONTH(T1.tDate)<=MONTH(T.tDate)) 累计
FROM dbo.T
GROUP BY MONTH(tDate)
/*
tDate tValue
----------------------- -----------
2017-01-08 00:00:00.000 10
2017-01-25 00:00:00.000 20
2017-02-11 00:00:00.000 30
2017-02-28 00:00:00.000 40
2017-03-17 00:00:00.000 50
2017-04-03 00:00:00.000 60
2017-04-20 00:00:00.000 70
2017-05-07 00:00:00.000 80
2017-05-24 00:00:00.000 90
(9 行受影响)
月份累计
----------- -----------
1 30
2 100
3 150
4 280
5 450
(5 行受影响)
*/
13.求x个月内产品逐月库存
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([年] int,[月] int,[产品] varchar(1),[未出库数量] int)
insert [huang]
select 2013,11,'A',100 union all
select 2014,1,'A',300 union all
select 2013,10,'B',1000 union all
select 2013,11,'B',1500 union all
select 2013,12,'B',3001
--------------开始查询--------------------------
;WITH d AS
(
SELECT CONVERT(VARCHAR(10),DATEADD(mm,number,'2013-11-01'),120)[date],b.产品
FROM master..spt_values cross JOIN (SELECT DISTINCT 产品 FROM [huang]) b
WHERE [type]='p' AND number>0 AND number<7--这个7可以控制月份
),cte AS (
SELECT ISNULL([年],YEAR(d.[date])) [年],ISNULL([月],month(d.[date])) [月],ISNULL(a.[产品],d.产品) 产品,ISNULL([未出库数量],0)[未出库数量],ROW_NUMBER()OVER(PARTITION BY ISNULL(a.[产品],d.产品) ORDER BY ISNULL([年],YEAR(d.[date])),ISNULL([月],month(d.[date]))) id
from [huang] a full JOIN d ON a.[年]=YEAR(d.[date]) AND a.[月]=MONTH(d.[date]) AND a.[产品]=d.产品
)
SELECT [年],[月],[产品],ISNULL((SELECT SUM([未出库数量]) FROM cte b WHERE a.id>b.id AND a.[产品]=b.[产品]),0)[未出库数量]
FROM cte a
ORDER BY [产品],[年],a.月
/*
年 月 产品 未出库数量
----------- ----------- ---- -----------
2013 11 A 0
2013 12 A 100
2014 1 A 100
2014 2 A 400
2014 3 A 400
2014 4 A 400
2014 5 A 400
2013 10 B 0
2013 11 B 1000
2013 12 B 2500
2014 1 B 5501
2014 2 B 5501
2014 3 B 5501
2014 4 B 5501
2014 5 B 5501
*/
