SqlServer中很少被用到的一些计算技巧！

--1.【行列转换】
--列转行
USE tempdb
GO
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
/*
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 

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.【每行中的数值统计：每行中各列数据的最大值、最小值、平均值】
USE tempdb
GO
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 *
,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.【每列中的数值统计】
USE tempdb
GO
IF (OBJECT_ID('DEPT') IS NOT NULL)  
    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.【某部门的所有上级机构或下级机构】
IF (OBJECT_ID('DEPT') IS NOT NULL)  
    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】

/*  
drop table #a;  
drop table #b;  
*/  
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 
*/

--8.【同列数值分组累加】

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
*/




--【一条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


--【随机取出N条记录】
select top 5 * from tableName order by newid()