--测试数据
if not object_id(N'Tempdb..#priceInfo') is null
drop table #priceInfo
Go
Create table #priceInfo([dalei] nvarchar(22),[kehu] nvarchar(23),[items] nvarchar(22),[price] int)
Insert #priceInfo
select N'超市',N'统一价',N'可乐',3 union all
select N'超市',N'家乐福',N'可乐',4 union all
select N'超市',N'沃尔玛',N'可乐',5
GO
if not object_id(N'Tempdb..#Customer') is null
drop table #Customer
Go
Create table #Customer([dalei] nvarchar(22),[kehu] nvarchar(23),[items] nvarchar(22))
Insert #Customer
select N'超市',N'华润',N'可乐' union all
select N'超市',N'大方',N'可乐' union all
select N'超市',N'家乐福',N'可乐' union all
select N'超市',N'沃尔玛',N'可乐'
Go
--测试数据结束
SELECT #Customer.*,#priceInfo.price
FROM #Customer
LEFT JOIN #priceInfo ON #priceInfo.dalei = #Customer.dalei
AND #priceInfo.items = #Customer.items
AND ( #priceInfo.kehu = #Customer.kehu
OR ( NOT EXISTS ( SELECT
*
FROM #priceInfo a
WHERE #Customer.kehu = a.kehu )
AND #priceInfo.kehu = '统一价'
)
)
DROP TABLE IF EXISTS `tmpA`;
DROP TABLE IF EXISTS `tmpB`;
CREATE TABLE `tmpA` (dalei VARCHAR(20),kehu VARCHAR(20),items VARCHAR(20), price INT);
INSERT INTO `tmpA` (`dalei`,`kehu`,`items`,`price`)
VALUES ('超市','统一价','可乐',3),('超市','家乐福','可乐',4),('超市','沃尔玛','可乐',5);
CREATE TABLE `tmpB` (`kehu` VARCHAR(20));
INSERT INTO `tmpB` (`kehu`) VALUES ('家乐福'),('沃尔玛'),('A'),('B');
SELECT b.kehu,IFNULL(a.dalei,a2.dalei) AS dalei,IFNULL(a.items,a2.items) AS items
,IFNULL(a.price,a2.price) AS price
FROM tmpB b
LEFT JOIN tmpA a2 ON a2.kehu = '统一价'
LEFT JOIN tmpA a ON a.kehu=b.kehu AND b.kehu IN ('家乐福','沃尔玛');
DROP TABLE IF EXISTS `tmpA`;
DROP TABLE IF EXISTS `tmpB`;
