sql

最新推荐文章于 2024-07-24 12:06:09 发布

oYuLian

最新推荐文章于 2024-07-24 12:06:09 发布

阅读量313

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/oYuLian/article/details/61194155

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

SELECT
carcard.*, car.Car,
car.`user`,
Card.Card
FROM
carcard
inner JOIN car ON (
carcard.CarId = Car.CarID
AND car.Car_Status > 0
)
inner JOIN card ON (
carcard.CardId = card.CardID
AND card.D_Status > 0
)
WHERE

carcard.C_Status = 1;

类似这种on后面有多种条件的且需要全部要满足的时候可以用 inner join

这个相当于以下的内容：

SELECT
carcard.*, car.Car,
car.`user`,
Card.Card
FROM
carcard,car,card
where (
carcard.CarId = Car.CarID
AND car.Car_Status > 0)

and (
carcard.CardId = card.CardID
AND card.D_Status > 0
)
and
carcard.C_Status = 1;

左连接 left join

会将坐标的数据都返回，注意这个返回是建立在on的基础上即右表如果有多条数据满足左表,则会将左表的记录复制几遍。且on 后面跟and 不会同时筛选，这里的and类似||。

SELECT p.Price, p.SupplierId,p.CreatedTime FROM ProcurementSubs as p where p.LiaoHaoSysId='68f51079-3438-453b-a1fd-155c25d1114b' and p.SupplierId='5fe72c08-183b-4e94-be57-164c2c0f5f9f' ORDER BY p.CreatedTime DESC ;
SELECT TOP 1 p.Price, p.SupplierId,p.CreatedTime FROM ProcurementSubs as p where p.LiaoHaoSysId='68f51079-3438-453b-a1fd-155c25d1114b' and p.SupplierId='5fe72c08-183b-4e94-be57-164c2c0f5f9f' ORDER BY p.CreatedTime DESC ;
SELECT p.SupplierId FROM ProcurementSubs as p where p.LiaoHaoSysId='68f51079-3438-453b-a1fd-155c25d1114b' GROUP BY p.SupplierId;
SELECT p.Price, p.SupplierId,p.CreatedTime from ProcurementSubs as p where p.LiaoHaoSysId='68f51079-3438-453b-a1fd-155c25d1114b' ORDER BY p.CreatedTime DESC;
SELECT p.SupplierId ,CHECKSUM_AGG (DISTINCT p.Price) from ProcurementSubs as p where p.LiaoHaoSysId='68f51079-3438-453b-a1fd-155c25d1114b' GROUP BY p.SupplierId ORDER BY MAX(p.CreatedTime);

//sql sever 触发器

DECLARE @t1 decimal DECLARE @t2 decimal DECLARE @Id int //声明变量
select @t1=TaskSubNumber,@t2=TaskSubPlanNumber, @Id = Id from Inserted //给变量赋值
if(@t1=@t2) 条件
BEGIN
update SubTasks set SubTasks.Note='触发' where id = @Id;

rollback// 回滚
End

declare @icount int
select @icount = COUNT(*)
from Drug_Info,Stock
where Stock.drug_ID=Drug_Info.drug_ID and Stock.stock_qty<Drug_Info.lower_limit

if @icount ..

sql 视图

SELECT
************

FROM
dbo.TaskMains ,dbo.OldBoms
where dbo.TaskMains.BomSysId=dbo.OldBoms.BomSysId and dbo.OldBoms.VersionId=(SELECT MAX(dbo.OldBoms.VersionId) FROM dbo.OldBoms);