The ranks of each other turn
Copy Code code as follows:
CREATE TABLE Test (ID int,name varchar (), Quarter int,profile Int)
INSERT into test values (1, ' a ', 1,1000)
INSERT into test values (1, ' a ', 2,2000)
INSERT into test values (1, ' a ', 3,4000)
INSERT into test values (1, ' a ', 4,5000)
INSERT into test values (2, ' B ', 1,3000)
INSERT into test values (2, ' B ', 2,3500)
INSERT into test values (2, ' B ', 3,4200)
INSERT into test values (2, ' B ', 4,5500)
SELECT * FROM Test
--row column
Select Id,name,
[1] as "first quarter",
[2] as "two quarters",
[3] as "three quarters",
[4] as "four quarters",
[5] as "5"
From
Test
Pivot
(
SUM (Profile)
For quarter in
([1],[2],[3],[4],[5])
)
As Pvt
CREATE TABLE test2 (ID int,name varchar (), Q1 int, Q2 int, Q3 int, Q4 int)
INSERT into test2 values (1, ' a ', 1000,2000,4000,5000)
INSERT into test2 values (2, ' B ', 3000,3500,4200,5500)
SELECT * FROM Test2
--Column Careers
Select Id,name,quarter,profile
From
Test2
Unpivot
(
Profile
For quarter in
([Q1],[q2],[q3],[q4])
)
As Unpvt
SQL replacement string substring replace
Copy Code code as follows:
--Example 1:
Update Tbpersonalinfo Set truename = replace (truename,substring (truename,2,4), ' * * ') where ID = 1
--Example 2:
Update Tbpersonalinfo Set Mobile = replace (mobile,substring (mobile,4,11), ' ******** ') where ID = 1
--Example 3:
Update Tbpersonalinfo Set Email = replace (email, ' chinamobile ', ' Hu Jintao ') Where ID = 1
SQL query the same record in a table having
If an ID can be distinguished, you can write that.
Copy Code code as follows:
SELECT * FROM table where ID in (
Select ID from table group by ID has sum (1) >1))
If a few IDs can be distinguished, that's what you write.
Copy Code code as follows:
SELECT * FROM table where ID1+ID2+ID3 in
(Select Id1+id2+id3 from table group by ID1,ID2,ID3 has sum (1) >1))
Other answer: Datasheet is zy_bho, want to find Zyh field name the same record
Copy Code code as follows:
--Method 1:
SELECT *from zy_bho a WHERE EXISTS
(SELECT 1 from zy_bho WHERE [PK] <> a.[pk] and ZYH = a.zyh)
--Method 2:
Select A.* from Zy_bho a join zy_bho b
On (A.[PK]<>B.[PK] and A.zyh=b.zyh)
--Method 3:
SELECT * FROM Zy_bbo where zyh
(select Zyh from Zy_bbo GROUP by Zyh having Count (zyh) >1)
-where PK is a primary key or a unique field.
To turn multiple rows of SQL data into a multiple-column data, a new column
Copy Code code as follows:
Select
Deptname=o.ouname,
' 9G ' =sum (case when Personalgrade=9 Then 1 Else 0 end),
' 8G ' =sum (case when personalgrade=8 Then 1 Else 0-end),
' 7g4 ' =sum (case when personalgrade=7 and Jobgrade =4 Then 1 Else 0 end),
' 7g3 ' =sum (case when personalgrade=7 and Jobgrade =3 Then 1 Else 0-end),
' 6G ' =s Um (case then personalgrade=6 Then 1 Else 0 end),
' 5g3 ' =sum (case when personalgrade=5 and Jobgrade =3 Then 1 Else 0 E nd),
' 5g2 ' =sum (case when personalgrade=5 and Jobgrade =2 Then 1 Else 0 "),
' 4G ' =sum (case when personalgrade= 4 Then 1 Else 0 end),
' 3g2 ' =sum (case when personalgrade=3 and Jobgrade =2 Then 1 Else 0 end),
' 3g1 ' =sum (case W Hen personalgrade=3 and Jobgrade =1 Then 1 Else 0 end),
' 2G ' =sum (case when personalgrade=2 Then 1 Else 0 end),
' 1G ' =sum (case when personalgrade=1 Then 1 else 0),
--' undetermined ' =sum (case when Personalgrade=null Then 1 Else 0 end)
Table replication
Copy Code code as follows:
INSERT into Phonechange_num ([imsi],num)
SELECT [IMSI]
, count ([IMEI]) as Num
from [Test]. [dbo]. [Phonechange] GROUP by [IMSI] ORDER by num DESC
Syntax 1:insert into table (field1,field2,...) VALUES (value1,value2,...)
Syntax 2:insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1 (requires target table Table2 must exist, because the destination table Table2 already exists, So we can insert constants in addition to the fields that are inserted into the source table Table1. ）
Syntax 3:select vale1, value2 into Table2 from Table1 (requires target table Table2 does not exist because the table Table2 is created automatically at insert time, and the specified field data in the Table1 is copied to the Table2. ）
Syntax 4: Use the Import Export feature for full table replication. If you are using "write a query to specify the data to be transferred," is there a problem with copying in large data tables? Because the copy to a certain extent will not move, memory burst? It is also not written to the table. The use of the above 3 kinds of syntax direct execution is immediately refreshed into the database table, you refresh the MDF file will know.
Update data with an associated subquery UPDATE statement
Copy Code code as follows:
--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,b
where A.bnum=b.bnum
--Method 3:
Update
(select A.bnum, a.newqiantity,b.qiantity from the left of join B on A.bnum=b.bnum) As C
Set c.newqiantity = c.qiantity
where C.bnum =xx
Connecting to a remote server
Copy Code code as follows:
--Method 1:
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' Server=192.168.0.67;uid=sa;pwd=password ', ' select * from BCM2.dbo.tbAppl ')
--Method 2:
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' 192.168.0.67 '; Sa '; ' Password ', ' SELECT * from BCM2.dbo.tbAppl ')
TRUNCATE table [table Name]
The following is a description of the usage and principle of the TRUNCATE statement in MSSQLServer2000:
Truncate is a statement in SQL that deletes the contents of a datasheet, using the following:
Truncate table name is faster and more efficient because:
TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data pages used to store the table data, and only records the release of the page in the transaction log.
TRUNCATE table deletes all rows in the tables, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used for the new line identification is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.
For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, but you should use a DELETE statement without a WHERE clause. Because the TRUNCATE TABLE is not logged in the log, it cannot activate the trigger.
TRUNCATE table cannot be used for tables that participate in indexed views.
Reference documents
database table row column, column change Ultimate scheme
Row to Peer (dynamic script)
Select INTO and INSERT into select two table copy statements
A very useful SQL script
Author: Listen to the wind blowing rain
