Shulou(Shulou.com)05/31 Report--

This article focuses on "summing up simple and practical SQL scripts". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "summing up simple and practical SQL scripts"!

1. The usage of row transfer column PIVOT

CREATE table test (id int,name nvarchar (20), quarter int,number int) insert into test values (1Power1000) insert into test values (1menN' Apple, 2D2000) insert into test values (1menN' Apple, 3pyr4000) insert into test values (1Min N' Apple, 4pyr5000) insert into test values (2Ling N' pear', 1MI3000) insert into test values (2Ling N' pear', 2pyr3500) insert into test values (2Ling N' pear', 3Jing 4200) insert into test values (2) N 'pear', 4pm 5500) select * from test

Results:

Select ID,NAME, [1] as' first quarter', [2] as' second quarter', [3] as' third quarter', [4] as' fourth quarter 'from test pivot (sum (number) for quarter in ([1], [2], [3], [4]) as pvt

Results:

2. The use of column row wrapping UNPIOVT

Create table test2 (id int,name varchar (20), Q1 int, Q2 int, Q3 int, Q4 int) insert into test2 values

(hint: you can swipe the code left and right)

Results:

-column to select id,name,quarter,number from test2 unpivot (number for quarter in ([Q1], [Q2], [Q3], [Q4]) as unpvt

Results:

3. Replace SUBSTRING/REPLACE with string

SELECT REPLACE ('abcdefg',SUBSTRING (' abcdefg',2,4),'*')

Results:

SELECT REPLACE ('13512345678)

Results:

SELECT REPLACE ('12345678' qq.compositional', '1234567'')

Results:

4. Query HAVING of the same record in a table

If an ID can be distinguished, it can be written like this.

SELECT * FROM HR.Employees

Results:

Select * from HR.Employees where title in (select title from HR.Employees group by title having count (1) > 1)

Results:

By comparison, it is found that the ID of 1Magol 2 is filtered out because they have only one record.

If several ID can tell the difference, it can be written like this.

Select * from HR.Employees where title+titleofcourtesy in (select title+titleofcourtesy from HR.Employees group by title,titleofcourtesy having count (1) > 1)

Results:

After the splicing of title and titleofcourtesy, the only one who meets the requirements is ID for 6, 7, 8 and 9.

5. Change multi-row SQL data into a multi-column data, that is, add a new column.

SELECT id,name, SUM (CASE WHEN quarter=1 THEN number ELSE 0 END) 'first quarter', SUM (CASE WHEN quarter=2 THEN number ELSE 0 END) 'second quarter', SUM (CASE WHEN quarter=3 THEN number ELSE 0 END) 'third quarter', SUM (CASE WHEN quarter=4 THEN number ELSE 0 END) 'fourth quarter' FROM test GROUP BY id,name

Results:

We increased the original 4 columns to 6 columns. A careful friend may find that this result is exactly the same as the row rotation above. In fact, the row transfer column above is omitted, which is a more common way of writing.

6. Table replication

Syntax 1:Insert INTO table (field1,field2,...) Values (value1,value2,...)

Syntax 2:Insert into Table2 (field1,field2,...) Select value1,value2,... From Table1

(requires that the target table Table2 must exist. Since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1.)

Syntax 3:SELECT vale1, value2 into Table2 from Table1

(requires that the target table Table2 does not exist because the table Table2 is automatically created upon insertion and the field data specified in the Table1 is copied to the Table2.)

Syntax 4: use the import and export function to copy the full table. If you are using [write a query to specify the data to be transferred], then there will be problems with replication in big data tables? Because the copy stops moving to a certain extent, and the memory explodes? It is also not written to the table. And using the above three syntax to execute directly will immediately refresh to the database table, you refresh the mdf file will know.

7. Update data by using Update statement with associated subquery

-- method 1: Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null-- method 2: update A set newqiantity=B.qiantity from A update A set newqiantity=B.qiantity from B where A.bnum=B.bnum-- method 3: update (select A.bnum, A.newqiantitymB.qiantity from A left join B on A.bnum=B.bnum) AS C set C.newqiantity = C.qiantity where C.bnum = '001'

8. Connect to the remote server

Method 1: select * from openrowset ('SQLOLEDB',' server=192.168.0.1;uid=sa;pwd=password', 'SELECT * FROM dbo.test')-method 2: select * from openrowset (' SQLOLEDB', '192.168.0.1;' sa'; 'password',' SELECT * FROM dbo.test')

Of course, you can also refer to the previous example and set up a DBLINK for remote connection.

9. Date and Time style CONVERT

The CONVERT () function is a generic function that converts dates to new data types.

The CONVERT () function can display date / time data in different formats.

Grammar

CONVERT (data_type (length), data_to_be_converted,style)

Data_type (length) specifies the target data type (with optional length). Data_to_be_converted contains values that need to be converted. Style specifies the output format of the date / time.

Style values that can be used:

Style IDStyle format 100or 0mon dd yyyy hh:miAM (or PM) 101mm/dd/yy102yy.mm.dd103dd/mm/yy104dd.mm.yy105dd-mm-yy106dd mon yy107Mon dd Yy108hh:mm:ss109 or 9mon dd yyyy hh:mi:ss:mmmAM (or PM) 110mm-dd-yy111yy/mm/dd112yymmdd113 or 13dd mon yyyy hh:mm:ss:mmm (24 hours) 114hh:mi:ss:mmm (24 hours) 120 or 20yyyy-mm-dd hh:mi:ss (24 hours) 121 or 21yyyy-mm-dd hh:mi:ss.mmm (24 hours) 126yyyy-mm-ddThh:mm:ss.mmm (no spaces) 130dd mon yyyy hh:mi:ss:mmmAM131dd/mm/ Yy hh:mi:ss:mmmAMSELECT CONVERT (varchar) GETDATE (), 0)-- result: 127 2020 9:33PM SELECT CONVERT (varchar, GETDATE (), 1)-- result: 12-07-20 SELECT CONVERT (varchar, GETDATE (), 2)-- result: 20.12.07 SELECT CONVERT (varchar (100), GETDATE (), 3)-- result: 07 SELECT CONVERT 12 SELECT CONVERT (varchar, GETDATE (), 4)-- result: 07.12.20 SELECT CONVERT (varchar, GETDATE ()) 5)-- result: 07-12-20 SELECT CONVERT (varchar, GETDATE (), 6)-- result: 07 12 20 SELECT CONVERT (varchar, GETDATE (), 7)-- result: 1207,20 SELECT CONVERT (varchar (100), GETDATE (), 8)-- result: 21:33:18 SELECT CONVERT (varchar, GETDATE (), 9)-- result: 127 2020 9:33:18:780PM SELECT CONVERT (varchar (100), GETDATE ()) 10)-- result: 12-07-20 SELECT CONVERT (varchar, GETDATE (), 11)-- result: 20-12-07 SELECT CONVERT (varchar, GETDATE (), 12)-- result: 201207 SELECT CONVERT (varchar, GETDATE (), 13)-- result: 07 12 2020 21 33 varchar (varchar), GETDATE (), 14)-- result: 21 varchar 33 varchar (100), GETDATE () Results: 2020-12-07 21:33:18 SELECT CONVERT (varchar, GETDATE (), 21)-- result: 2020-12-07 21 GETDATE 33 SELECT CONVERT (varchar (100), GETDATE (), 22)-- result: 12-07-20 9:33:18 PM SELECT CONVERT (varchar (100), GETDATE (), 23)-- result: 2020-12-07 SELECT CONVERT (varchar, GETDATE) Results: 21:33:18 SELECT CONVERT (varchar, GETDATE (), 25)-- results: 2020-12-07 21 (33 GETDATE) 33 SELECT CONVERT (varchar, GETDATE (), 100)-- results: 127 2020 9:33PM SELECT CONVERT (varchar, GETDATE)-- results: 12: 072020 SELECT CONVERT (varchar, GETDATE (), 102)-- results: 2020.12.07 SELECT CONVERT (varchar (100)) Result: 07.12.2020 SELECT CONVERT (varchar, GETDATE (), GETDATE)-- result: 07-12-2020 SELECT CONVERT (varchar, GETDATE)-- result: 07 12 2020 SELECT CONVERT (varchar, GETDATE)-- result: 1207, 2020 SELECT CONVERT (varchar, GETDATE) 21:33:18 SELECT CONVERT (varchar, GETDATE)-- result: 127 2020 9:33:18:780PM SELECT CONVERT (varchar, GETDATE)-- result: 12-07-2020 SELECT CONVERT (varchar, GETDATE)-- result: 2020-12-07 SELECT CONVERT (varchar, GETDATE)-- result: 20201207 SELECT CONVERT (varchar, GETDATE) Result: 2020-12-07 21:33:18 SELECT CONVERT (varchar, GETDATE, 2020)-- result: 2020-12-07 21:33:18 SELECT CONVERT (varchar, GETDATE)-- result: 2020-12-07 21:33:18 SELECT CONVERT (varchar (100,121)-- result: 2020-12-07 21 SELECT CONVERT. I believe you have a deeper understanding of "summing up simple and practical SQL scripts", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.