当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQLSERVER2008数据查询语句集分享

SQLSERVER2008数据查询语句集分享

2018年03月07日  | 移动技术网IT编程  | 我要评论

江陵肃是谁的孙子,黑鳞莫贝尼在哪,女行长郭佳

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'

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网