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’