EXISTS/NOT EXISTS CASE WHEN等使用方法

--简单判断用法
SELECT 1 WHERE EXISTS (SELECT * FROM cpay..System_Setting)
--可以替换count
SELECT COUNT(1) FROM cpay..System_Setting

USE YSB_PayDB
--1 练习NOT EXISTS的用法
--（Exists使用心得：1、先查目标表的全部；2、再用Exists关联目标表过滤掉不要的）
SELECT c.* FROM dbo.YSB_Distributor AS d 
INNER JOIN dbo.YSB_CustomerInfo AS c
ON d.DistributorID = c.DistributorID
WHERE d.DistributorName='tests'
AND NOT EXISTS (
SELECT 1 FROM dbo.YSB_DistributorCustomerServiceFee AS s 
WHERE c.CustomerNo = s.CustomerNo
)--2 练习NOT EXISTS
SELECT * FROM dbo.YSB_PayProductInfo AS p
WHERE NOT EXISTS (
SELECT 1 FROM dbo.YSB_PayProductSupportBusProduct AS s
WHERE p.PayProductCode = s.PayProductCode AND s.BusProductCode = 'UATP'
)--3 练习NOT EXISTS的用法
SELECT * FROM dbo.Sys_Company
SELECT * FROM dbo.YSB_CustomerInfo
SELECT * FROM dbo.YSB_CustomerAndFactoringCompanyRelationSELECT * FROM dbo.YSB_CustomerInfo AS c
WHERE NOT EXISTS  (
SELECT 1 FROM dbo.YSB_CustomerAndFactoringCompanyRelation AS f
INNER JOIN dbo.Sys_Company AS s
ON f.FactoringCompanyID = s.CompanyID
WHERE s.CompanyName = '北京海淀科技企业融资担保有限公司' AND c.CustomerNo = f.CustomerNo
)--4 练习 CASE WHEN的用法
USE MySchool
SELECT * FROM dbo.tblScore
SELECT * FROM dbo.tblCourse
SELECT * FROM dbo.tblStudent
SELECT * FROM dbo.tblTeacherSELECT st.StuId,st.StuName,co.CourseName,sc.Score ,
CASE WHEN sc.Score >= 90 THEN '优秀'WHEN sc.Score >= 80 THEN '良好'WHEN sc.Score >= 60 THEN '及格'WHEN sc.Score < 60 THEN '不及格'ELSE '其他'
END AS '成绩',
CASE st.StuSexWHEN '男' THEN '男生'WHEN '女' THEN '女生'ELSE '其他'
END AS 性别
FROM dbo.tblStudent AS st
INNER JOIN dbo.tblScore AS sc
ON st.StuId = sc.StuId
INNER JOIN dbo.tblCourse AS  co
ON co.CourseId = sc.CourseId--5 练习CASW WHEN GROUP BY的用法
--（此写法类似Switch...Case...）
SELECT b.BusProductCode, b.BusProductName,
CASE b.BusProductTypeWHEN 1 THEN '机票类'WHEN 2 THEN '平台类'WHEN 3 THEN '票务类'
END
FROM YSB_PayDB.dbo.YSB_BusProductInfo AS b
--（此写法类似If...Else...）
SELECT b.BusProductCode, b.BusProductName,
CASE WHEN b.BusProductType = 1 THEN '机票类'WHEN b.BusProductType = 2 THEN '平台类'WHEN b.BusProductType = 3 THEN '票务类'
END
FROM YSB_PayDB.dbo.YSB_BusProductInfo AS bSELECT 
CASE BusProductTypeWHEN 1 THEN '机票类'WHEN 2 THEN '平台类'WHEN 3 THEN '票务类'
END AS 业务类型,
COUNT(1) AS 产品数量
FROM YSB_PayDB.dbo.YSB_BusProductInfo
GROUP BY BusProductType

转载于:https://www.cnblogs.com/taiyonghai/p/5806260.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！