SQLSERVER2008数据查询语句集分享

1.===========================================类

USE E_TestData

select * from TDATA WHERE Model='MS-B85-BTC' and Date_Time ? LIKE '2018/02/04 19%' --order by Date_Time DESC

select count(Date_Time) from TDATA WHERE Model='MS-B85-BTC' AND JobNumber='237470'?

GROUP BY Date_Time ?ORDER BY Date_Time DESC

ORDER BY Date_Time DESC?

USE E_TestData

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='Employee')

drop table Employee

GO

CREATE TABLE Employee

(

? ? NO INT IDENTITY(1,1) NOT NULL,

Job_Number Varchar(6) NOT NULL,

EmployeeName Varchar(12) NOT NULL

)

GO

ALTER TABLE Employee

ADD CONSTRAINT PK_Job_Number PRIMARY KEY(Job_Number),

? ? CONSTRAINT UK_Job_Number UNIQUE(Job_Number),

CONSTRAINT UK_EmployeeName UNIQUE(EmployeeName)

GO

select * from Employee

Insert INTO Employee Values('240210','廖灵鑫')

Insert INTO Employee Values('239154','陈永兰')

Insert INTO Employee Values('238871','尹美宁')

Insert INTO Employee Values('236445','农月青')

Insert INTO Employee Values('237470','黄燕玲')

Insert INTO Employee Values('239543','黄彩芳')

Insert INTO Employee Values('240786','张希发')

Insert INTO Employee Values('238855','肖兴敏')

Insert INTO Employee Values('240215','杨明春')

Insert INTO Employee Values('240192','黄永辉')

Insert INTO Employee Values('239040','李帮琴')

Insert INTO Employee Values('240551','王良宇')

Insert INTO Employee Values('240883','潘春想')

Insert INTO Employee Values('237150','邓海龙')

select JobNumber,count(JobNumber) AS '测试数量' from TDATA AS T WHERE Date_Time like '2018/02/05%' And Model='MS-B85-BTC'

INNER JOIN Employee?

select COUNT(T.JobNumber) as '总数'from TDATA AS T

INNER JOIN Employee AS E ON E.Job_Number=T.JobNumber

WHERE T.Model='MS-B85-BTC' AND T.Date_Time LIKE '2018/02/05%'

GROUP BY T.JobNumber ORDER BY COUNT(T.JobNumber) DESC

SELECT E.EmployeeName,T.JobNumber,T.Line,T.Date_Time FROM TDATA AS T

INNER JOIN Employee AS E ON E.Job_Number=T.JobNumber

WHERE T.Model='MS-B85-BTC' AND T.Date_Time LIKE '2018/02/05%'

--GROUP BY E.EmployeeName AND ORDER BY COUNT(E.Job_Number) AND COUNT(T.)DESC

2.===========================================================类

use E_TestData

select count(*) from TDATA WHERE Date_Time like '2018/03/03%' or ?Date_Time like '2018-03-03%'

select E.EmployeeName AS 姓名,count(JobNumber) as 总数 from TDATA AS T

INNER JOIN Employee as E ON E.Job_Number=T.JobNumber

WHERE Date_Time like '2018/03/03%'OR Date_Time like '2018-03-03%'

GROUP BY E.EmployeeName Order by count(JobNumber) DESC

select E.EmployeeName AS 姓名,count(JobNumber) as 总数 from TDATA as T

INNER JOIN Employee as E ON E.Job_Number=T.JobNumber

WHERE T.Date_Time BETWEEN '2017/10/01' AND '2018/02/14'

GROUP BY E.EmployeeName

ORDER BY count(T.JobNumber)DESC

select * from TDATA

3.===========================================================类

USE E_TestData

SELECT E.EmployeeName AS 姓名,COUNT(*) AS 总数 FROM TDATA AS T

INNER JOIN Employee AS E ON E.Job_Number=T.JobNumber

WHERE Date_Time LIKE '2018/01%' AND Line='2F-TEST-L01'--/02/05%' AND Model='MS-B85-BTC'

GROUP BY E.EmployeeName ORDER BY count(*)DESC

select * from TDATA WHERE Date_Time LIKE '2018/02/06%' AND Model='MS-B85-BTC' ORDER BY Date_Time DESC

USE E_TestData

SELECT E.EmployeeName AS 姓名,COUNT(*) AS 总数 FROM TDATA AS T

INNER JOIN Employee AS E ON E.Job_Number=T.JobNumber

WHERE Date_Time LIKE '2018/02/07%' AND Model='MS-B85-BTC'

GROUP BY E.EmployeeName ORDER BY count(*)DESC

USE E_TestData

SELECT COUNT(*) FROM TDATA WHERE Date_Time LIKE '2018/02%' AND Model='MS-B85-BTC'