Description: Copy table (copy only structure, source table name: A new table name: b)
SELECT * into B from a where 1<>1
Description: Copy table (copy data, source table name: A target table name: b)
Insert into B (A, B, c) select d,e,f from B;
Description: Displays the article, the author, and the last reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table
where Table.title=a.title) b
Description: Outer join query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from-a left-out JOIN b on a.a = B.C
Description: Schedule five minutes advance reminder
SELECT * FROM Schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
Description: Two related tables, delete the information in the primary table that has not been in the secondary table
Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)
Description:--
Sql:
SELECT A.num, A.name, B.upd_date, b.prev_upd_date
From TABLE1,
(SELECT x.num, x.upd_date, Y.upd_date prev_upd_date
From (SELECT NUM, Upd_date, Inbound_qty, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ')) X,
(SELECT NUM, Upd_date, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') =
To_char (to_date to_char (sysdate, ' yyyy/mm ') | | '/01 ', ' Yyyy/mm/dd ')-1, ' yyyy/mm ') Y,
WHERE X.num = y.num (+)
and X.inbound_qty + NVL (y.stock_onhand,0) <> X.stock_onhand) B
WHERE A.num = B.num
Description:--
SELECT * from Studentinfo where NOT EXISTS (SELECT * from student where
studentinfo.id=student.id) and system name = ' &strdepartmentname& ' and professional name
= ' &strprofessionname& ' ORDER by sex, source of students, college entrance examination total score
To go from the database to the unit telephone fee statistics for one year (telephone number quota, telegram, fertilizer list, two table sources)
SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as FRI,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as the MAR,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as APR,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as May,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as Jue,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' modified ', a.factration)) as June,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as AGU,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as SEP,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a ', a.factration)) as DEC
From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
From Telfeestand A, Telfee b
WHERE A.tel = B.telfax) A
GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')
Note: Four table joint check problem
SELECT * from a left INNER join B on a.a=b.b right inner join C on A.A=C.C inner join D on
A.a=d.d where .....
Description: Get the smallest unused ID number in the table
Select (Case when EXISTS (SELECT * from Handle b WHERE b.handleid = 1) THEN MIN (Handleid) + 1
ELSE 1 end) as Handleid from Handle WHERE isn't Handleid in (SELECT a.handleid-1 from
Handle a)
Problem with an SQL statement: row and column conversions
SELECT * FROM V_temp
The above view results are as follows:
User_name Role_name
-------------------------
System Admin Administrator
Feng Administrator
Feng General user
Test General User
Trying to turn the results into this:
User_name Role_name
---------------------------
System Admin Administrator
Feng Administrator, general user
Test General User
===================
CREATE TABLE A_test (name varchar, role2 varchar (20))
INSERT into a_test values (' Lee ', ' admin ')
INSERT into a_test values (' Zhang ', ' admin ')
INSERT into a_test values (' Zhang ', ' General user ')
INSERT into a_test values (' regular ', ' General user ')
Create function Join_str (@content varchar (100))
Returns varchar (2000)
As
Begin
DECLARE @str varchar (2000)
Set @str = '
Select @str = @str + ', ' +rtrim (role2) from a_test where [name]= @content
Select @str =right (@str, Len (@str)-1)
Return @str
End
Go
--Call:
SELECT [Name],dbo.join_str ([name]) Role2 from A_test Group by [name]
--select distinct name,dbo.uf_test (name) from A_test
Quickly compare two tables with the same structure
Structure of the same two tables, a table with a record of about 30,000, a table with a record of about 20,000, how can I quickly find the different records of two tables?
============================
Give you a test method that takes data from the Orders table in Northwind.
SELECT * into N1 from orders
SELECT * into N2 from orders
SELECT * FROM N1
SELECT * from N2
--Add a primary key and then modify several of the fields in the N1
ALTER TABLE n1 ADD constraint pk_n1_id primary key (OrderID)
ALTER TABLE n2 add constraint pk_n2_id primary key (OrderID)
Select OrderID from (SELECT * to N1 union SELECT * from N2) A group by OrderID has
COUNT (*) > 1
It should be, and the IDs of the different records will be displayed.
The following applies to the same situation as the two records,
SELECT * FROM N1 where OrderID the (select OrderID from N1 UNION SELECT *
N2) A group by OrderID have count (*) > 1)
It's better to have records that don't exist between the two sides.
--delete several records from N1,N2
Delete from N1 where OrderID in (' 10728 ', ' 10730 ')
Delete from N2 where OrderID in (' 11000 ', ' 11001 ')
--*************************************************************
Both have the record but not exactly the same
SELECT * FROM N1 where OrderID the (select OrderID from N1 UNION SELECT *
N2) A group by OrderID have count (*) > 1)
Union
--n2 in the N1, but not in the 10728,10730
SELECT * FROM N1 where OrderID isn't in (select OrderID from N2)
Union
--n1 in the N2, but not in the 11000,11001
SELECT * from N2 where OrderID isn't in (select OrderID from N1)
Four methods to take table N to M Records:
1.
Select Top M * into a temporary table (or table variable) from TableName to ColumnName--inserts a top m pen
SET ROWCOUNT n
SELECT * FROM table variable ORDER BY columnname desc
2.
Select Top N * "from" (select top M * columnname) a TableName
ColumnName desc
3. If there are no other identity columns in the TableName, then:
Select identity (int) id0,* into #temp from TableName
The statements that take the N to M Bar are:
SELECT * from #temp where id0 >=n and id0 <= m
If you do an error while executing the statement of select identity (int) id0,* into #temp TableName, it is because
Your DB middle Select Into/bulkcopy property is not opened to be executed first:
EXEC sp_dboption your DB name, ' Select Into/bulkcopy ', True
4. If the table has the identity attribute, so simple:
SELECT * FROM tablename where Identitycol between N and M
How do I delete a duplicate record in a table?
CREATE TABLE a_dist (ID int,name varchar (20))
INSERT into a_dist values (1, ' abc ')
INSERT into a_dist values (1, ' abc ')
INSERT into a_dist values (1, ' abc ')
INSERT into a_dist values (1, ' abc ')
exec up_distinct ' a_dist ', ' ID '
SELECT * FROM A_dist
CREATE PROCEDURE up_distinct (@t_name varchar), @f_key varchar (30))
--f_key is a grouped field, that is, a primary key field
As
Begin
Declare @max integer, @id varchar, @sql varchar (7999), @type integer
Select @sql = ' Declare cur_rows cursor for select ' + @f_key + ', COUNT (*) from ' + @t_name + '
GROUP BY ' + @f_key + ' has count (*) > 1 '
EXEC (@sql)
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Begin
Select @max = @max-1
SET ROWCOUNT @max
Select @type = Xtype from syscolumns where id=object_id (@t_name) and Name= @f_key
If @type =56
Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + @id
If @type =167
Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + ' + ' + @id + ' '
EXEC (@sql)
Fetch cur_rows into @id, @max
End
Close Cur_rows
Deallocate cur_rows
SET ROWCOUNT 0
End
SELECT * FROM systypes
SELECT * from syscolumns where id = object_id (' a_dist ')
Maximum sorting problem for query data (written in one statement only)
CREATE TABLE Hard (qu Char (one), CO char (one), je Numeric (3, 0))
INSERT into hard values (' A ', ' 1 ', 3)
INSERT into hard values (' A ', ' 2 ', 4)
INSERT into hard values (' A ', ' 4 ', 2)
INSERT into hard values (' A ', ' 6 ', 9)
INSERT into hard values (' B ', ' 1 ', 4)
INSERT into hard values (' B ', ' 2 ', 5)
INSERT into hard values (' B ', ' 3 ', 6)
INSERT into hard values (' C ', ' 3 ', 4)
INSERT into hard values (' C ', ' 6 ', 7)
INSERT into hard values (' C ', ' 2 ', 3)
The results of the request query are as follows:
Qu Co JE
----------- ----------- -----
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4
is to press Qu Group, each group to take JE The largest of the top 2!!
And you can only use one SQL statement!!!
SELECT * from Hard a Where JE to (select top 2 je to hard b where a.qu=b.qu order by JE)
Find an SQL statement that deletes duplicate records?
How to delete a record with the same field, leaving only one.
For example, there are id,name fields in table test
If a record with the same name leaves only one, the rest is deleted.
The contents of name are indefinite, and the same number of records is uncertain.
Is there such a SQL statement?
==============================
A: A complete solution:
To record duplicate records in the Temp1 table:
Select [Flag Field id],count (*) into TEMP1 from [table name]
Group BY [flag field ID]
Having Count (*) >1
2. Record the TEMP1 records into the table:
Insert Temp1 SELECT [Flag Field Id],count (*) from [table name] GROUP by [flag Field ID] having count (*)
=1
3. Make a table containing all the records that are not duplicated:
SELECT * into Temp2 from [table name] where flag field ID in (Select flag field ID from TEMP1)
4, delete the repeating table:
Delete [table name]
5, restore the table:
Insert [table name] SELECT * FROM Temp2
Current 1/2 page
12 Next read the full text
