自己写的一个多表查寻
SELECT t1.*,
t2.ESSerialNumber [Serial_Number]
FROM
(
SELECT pb.JobId,
pb.FullPalletID,
(
SELECT sl.BoxSeq
FROM dbo.SsccLog sl
WHERE sl.PositionName = 'Cartoon'
AND sl.Jobid = pb.JobId
AND sl.FullSn = pb.ESSerialNumberB
) BoxSeq,
(
SELECT sl.BoxNumber
FROM dbo.SsccLog sl
WHERE sl.PositionName = 'Cartoon'
AND sl.Jobid = pb.JobId
AND sl.FullSn = pb.ESSerialNumberB
) BoxNum,
pb.ESSerialNumberA [Carton_SSCC],
pb.ESSerialNumberB [CartonID],
sscc.sscc [Pallet_SSCC],
sl.PalletSscc [PalletID]
FROM dbo.PBSPalletBoxInfo pb, --表A
dbo.SsccLog sl, --表B
DGOV1.PhilipsShippingMatch.dbo.si_qr_realate_sscc sscc --表C
WHERE sl.Jobid = pb.JobId
AND pb.FullPalletID = sl.BoxSeq
AND pb.JobId IN
(
SELECT JobId FROM dbo.PBSJob WHERE JobNumber = 'C04579-01'
)
AND sl.PositionName = 'Pallet'
AND sl.PalletSscc = SUBSTRING(sscc.sscc, 11, 9)
AND sl.DisableDate IS NULL
AND pb.DisableTime IS NULL
) AS t1 ,--表1
dbo.PBSEScanLog AS t2--表2
WHERE t1.JobId = t2.ESJobId
AND t1.BoxSeq = t2.ESBoxSeq
AND t2.ESDisabledDate IS NULL
ORDER BY t1.FullPalletID,
t1.BoxSeq;
这个是插入不重复的数据到PBSReprint
insert into PBSReprint (SerialNumber,MAC,MAC1,MAC2,MAC3,SecurityId,WPAPSK)
SELECT et.SerialNumber,et.MAC,et.MAC1,et.MAC2,et.MAC3,et.SecurityId,et.WPAPSK from @ExeclTb as et
WHERE et.SerialNumber not in( select SerialNumber from PBSReprint)
INSERT INTO dbo.TestResult
(
Mac
)
SELECT Mac FROM dbo.Temp AS t1 WHERE t1.mac NOT IN(SELECT Mac FROM dbo.TestResult )
创建索引
CREATE [UNIQUE(唯一索引)] INDEX [索引名] ON [表名](列名 ASC )
CREATE INDEX create_date_Index ON PackLog(create_date ASC)
索引名 表名 列名 ASC
//链接数据库
osql -SIP -U用户名 -P密码
//创建数据库
//那数据库要创建mdf和ldf两个文件
Create database MySchool
on primary
(
name ='MySchool', ---这里是区分大小写的,这里的名字必须要与上面Create database ‘MySchool’一致.
filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MySchool.mdf',
size=5MB, --默认大小
filegrowth=2MB, --增长方式
maxsize=100MB --最大容量
)
log on
(
name= 'Myschool_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Myschool_log.ldf'
size=1MB,
filegrowth=10%
)
//建表
create table score
(
studentID int identity(1,1) primary key,
english int not null
)
//删除语句
drop table student 删除这张表,下面两个是删除表中的数据.
truncate table student 删除student表中的数据,但会还原自动增长列.列又从1开始计数.
truncate非常高效,数百万条数据只需几秒,而使用Delete则需要几个小时.
delete from student 删除student表中的数据,不会还原自动增长列.自动增长列还会继续上次继续计数.
//更新语句
update student set age=50 //将age字段所有数据更新为50
update student set age=50 where Name='刘备' //将Name='刘备'的这条记录中的age字段更新为50.
------给studentID是1的英语成绩加10分
update score set english+=10 where studentID=1
//多条件固定值查寻,结果不改变数据库内容.
select
case P_TypeID
when 1 then '朋友' //value为1显示为朋友……
when 2 then '同学' //其实是在对一列的所有内容进行操作.
when 3 then '同事' //then 最后的value必须一致
else '陌生人' //else 和C#语法一样可以没有
end as '关系', //将P_TypeID字段改为‘关系’
P_Name,P_CellPhone,P_HomePhone from Phone_Num
//多条件区间范围查寻,结果不改变数据库内容.
select ID,Name,Sex,
case
when Age <15 then '未成年'
when Age >15 and Age<20 then '青春年华'
when Age >21 and Age <50 then '成年'
when Age >100 then '老妖精'
else '没出生'
end as '年龄'
,Address,Birthday from MyClass