SELECT * FROM table Awhere Date = (Select Top 1 Date from Table WHERE Number = a. Number ORDER BY Date DESC) - or: SELECT A. * FROM Table A, (SELECT number, date = max) from table Group By number bwhere a. Number = b. No. And a. Date = b. Date
- or: select * from the table Awhere NOT EXISTS (SELECT * FROM table where number = a. Number AND Date> a. Date)
How to add a virtual serial number field when querying
TXLICENHE (Horse): 1: Self-amplified type is: int identity (1, 1) Of course, Bigint, Smallint Eg: Create Table TBName (ID ID IDETENTITY (1, 1), Description varchar (20)) Or when you design the table field with the Enterprise Manager, set the field to int, set the identity to yes, others can be used by default
2: Add sequence number when query: A: There is no primary key: Select Id, 1, 1) AS IID, * INTO #TMP from TableName Select * from #tmp Drop Table #tmp B: Situation with the primary key: SELECT Select Sum (1) from tablename where keyfield <= a.keyfield) AS IID, * from Tablename A3: Generates a list of self-amplified serial numbers EG: Generate a number of 0-30 SELECT TOP 30 (Select Sum (1) from sysobjects Where name <= a.name) -1 AS ID from Sysobjects A
Of course, there may be no such records in sysObjects, such as only 100, I need to generate 1-800 serial number as follows: SELECT (SELECT SUM (1) from (SELECT TOP 800 A.NAME AS Name1, B.Name As Name2 from sysobjects a, sysobjects b) cc where name1 <= dd.name1 and name2 <= dd.name2) from (SELECT TOP 800 A.NAME AS Name1, B.Name As Name2 from sysobjects a, sysobjects b) DD
Application Example EG1: CREATE TABLE T (Date Char (8) To list the number of fake people in October 2003, if not, it is represented by 0.
SELECT Convert (25), Dateadd (DAY, ID, '20031001'), 112), ISNULL (T. Request Dakes, 0) from (SELECT SUM (1) from sysobjects where name <= a. Name) -1 as id from sysobjects a) Bbleft Join T on Convert (Char (8), DateAdd (DAY, ID, '20031001'), 112) = T. Date EG2: Generate Random Attendance Card Information:
Declare @r Int - get the number of records to be processed SET @ r = 900
- Create a temporary table Create Table #tb (ID Int Id IDTETIME, DT4 DT1 DT3 DATETIME, DT4 DT1 DT3 DATIME, DT6 DT1 DT3 DATIME, DT6 DT1 DT3 DATETIME, DT6 DT1 DT5 DATIME
- Generate random time declare @SQL varchar (8000) set @ SQL = 'INSERT INTO #TB (DT1, DT2, DT3, DT4, DT5, DT6) SELECT TOP' CAST (@R as varchar) 'Datead (SS , Rand (A.ID) * 1800, '' 07:30 '), DateAdd (SS, RAND (A.ID 1) * 400,' '11:30'), Dateadd (SS, RAND (A .id 2) * 1600, '' 13:00 '), dateadd (SS, Rand (A.ID 3) * 300,' '17:30'), Dateadd (SS, Rand (A.ID 4) * 800, '' 17:45 '), DateAdd (SS, RAND (A.ID 5) * 250,' '20:00') from (SELECT TOP 100 ID from sysobjects) A, SELECT TOP 9 id from sysobjects) Border by newid () exec (@sql) Of course, if you will be 07:30 11:30 ... these time it is changed to the class.
