有用的sql语句

 

--sql 1 将一张表的字段通过另一张表之间的对应关系进行更新。
update WarrantySaleItem set applicableName = a.applicableName
from Applicable a where a.ID = WarrantySaleItem.applicableName

 

 

--sql 2 通过一张表更新另一张表的数据。属于实用性的sql。

INSERT INTO dbo.Vendor(VendorCode,VendorNameCh,VendorNameEn,TaxRate)
SELECT VendorCode,VendorNameCh,VendorNameEn,TaxRate
FROM dbo.Vendor_Temp a
WHERE a.VendorCode not in
(SELECT VendorCode FROM dbo.Vendor)

UPDATE dbo.Vendor
SET VendorNameCh=a.VendorNameCh,
        VendorNameEn=a.VendorNameEn,
        TaxRate=a.TaxRate
FROM dbo.Vendor_Temp a
WHERE dbo.Vendor.VendorCode=a.VendorCode AND (
              dbo.Vendor.VendorNameCh<>a.VendorNameCh OR
              dbo.Vendor.VendorNameEn<>a.VendorNameEn OR
              dbo.Vendor.TaxRate<>a.TaxRate)

 

--sql 3row_number 函数的使用Oracle中有rownumber，sqlservicer有这个函数，功能都差不多，使用方法如下：

select a.*,row_number() over(order by id)  from Customer as a

 

 

--sql 3 查询某个数据库链接的信息。

SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID]
IN
(
  SELECT
   [DBID]
  FROM
   [Master].[dbo].[SYSDATABASES]
  WHERE
   NAME='CERS' --数据库名称
)

 

--sql4 查询各个科目的前三名的学生

use znj
go

create table student
(
 sid int identity(1,1) primary key,
 sname varchar(20) not null
)

create table coures
(
  cid int identity(1,1) primary key,
  cname varchar(20)
)

create table score
(
   scid int identity(1,1) primary key,
   sid int not null,
   cid int not null,
   result float
)

insert into student values('张学友')
insert into student values('吴孟达')
insert into student values('张柏芝')
insert into student values('曹阿瞒')
insert into student values('张牛俊')

insert into coures values('计算机科学与应用')
insert into coures values('高等数学')
insert into coures values('线性代数')

insert into score values(1,1,80)
insert into score values(1,2,78)
insert into score values(1,3,59)
insert into score values(2,1,84)
insert into score values(2,2,90)
insert into score values(2,3,45)
insert into score values(3,1,23)
insert into score values(3,2,45)
insert into score values(3,3,87)
insert into score values(4,1,100)

select * from student
select * from coures
select * from score

--查询出 各个科目的前三名(圆满解决)
select cid,result from score s group by cid,result  having result
in
(
   select top 3 result  from score where cid = s.cid order by result desc
)
order by cid,result desc

 

--sql5删除数据库多余的归档日志

EXEC sp_cycle_errorlog ;

 

 

--sql6 统计

select prd.id, prd.sale_price, styles.tags as style_tags, colors.tags as color_tags,prd.added_time
from product as prd, product_tag as styles, product_tag as colors
where styles.product_id = colors.product_id
and prd.id = styles.product_id
and styles.dimension_id = 4
and colors.dimension_id = 5