SQL Serverのクエリーテクニック
11428 ワード
--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,' ' + replace([value], ',', ' ') + ' ') 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() 年内月次累計(例:2月累計が前の2ヶ月の場合、3月累計が前の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 T1 WHERE 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 )
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
*/