SQL中对材料报价单的数据权限进行操作 (如列转行 行转列的处理)
--可以直接放到查询分析器执行
--判断测试表TmpTCIQuote是否有存在 不存在则创建
if NOT exists (select * from dbo.sysobjects where id = object_id(N'dbo.TmpTCIQuote') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
create table TmpTCIQuote(
CIQuotesID int not null, --材料报价单ID
cSubInvCode VARCHAR(100) NOT NULL, --材料编码
cVen1 VARCHAR(50), --材料厂编码1
cMVenName1 VARCHAR(200), --材料厂名称1
cVen2 VARCHAR(50), --材料厂编码2
cMVenName2 VARCHAR(200), --材料厂名称2
cVen3 VARCHAR(50), --材料厂编码3
cMVenName3 VARCHAR(200) --材料厂名称3
primary key (CIQuotesID)
);
END
--清空数据
TRUNCATE TABLE TmpTCIQuote
--插入默认数据
INSERT INTO TmpTCIQuote(CIQuotesID,cSubInvCode,cVen1,cMVenName1,cVen2,cMVenName2,cVen3,cMVenName3)
SELECT 1,'39010000098','100595','东莞利丰商标制造有限公司','100823','广州南沙经济技术开发区彩佳印花有限公司','100436','晋江市绿雨彩色印刷有限公司' UNION ALL
SELECT 2,'39080000001','100823','广州南沙经济技术开发区彩佳印花有限公司','100595','东莞利丰商标制造有限公司',NULL,NULL UNION ALL
SELECT 3,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,'100595','东莞利丰商标制造有限公司' UNION ALL
SELECT 4,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,NULL,NULL
SELECT * FROM TmpTCIQuote
/*
原始数据
ID 材料编码 材料厂编码1 材料厂名称1 材料厂编码2 材料厂名称2 材料厂编码3 材料厂名称3
1 39010000098 100595 东莞利丰商标制造有限公司 100823 广州南沙经济技术开发区彩佳印花有限公司 100436 晋江市绿雨彩色印刷有限公司
2 39080000001 100823 广州南沙经济技术开发区彩佳印花有限公司 100595 东莞利丰商标制造有限公司 NULL NULL
3 39150000101 100436 晋江市绿雨彩色印刷有限公司 NULL NULL 100595 东莞利丰商标制造有限公司
4 39150000101 100436 晋江市绿雨彩色印刷有限公司 NULL NULL NULL NULL
描述：只显示对应的材料厂编码 不相关的材料厂编码用Y表示(表示对应的已经有其它材料供应商) 为空的材料厂编码用Null表示
假设只拥有材料厂编码100595的查看数据权限 其它材料厂编码不能查看 有其它材料厂编码用Y表示 空的用Null表示
最终结果如下：
ID 材料编码 材料厂编码1 材料厂名称1 材料厂编码2 材料厂名称2 材料厂编码3 材料厂名称3
1 39010000098 100595 东莞利丰商标制造有限公司 Y Y
2 39080000001 Y 100595 东莞利丰商标制造有限公司 Null
3 39150000101 Y Null 100595 东莞利丰商标制造有限公司
*/
DECLARE @cVenValue VARCHAR(50) --定义材料厂编码
SET @cVenValue = '100595'; --只允许查看100595的材料厂编码
WITH T1 AS --对三个材料厂编码进行列转行
(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex = REPLACE(cVen,'cVen','') FROM TmpTCIQuote
UNPIVOT (cVenValue FOR cVen IN (cVen1,cVen2,cVen3)) cVen),
T2 AS --显示包括的材料厂编码的信息
(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex FROM T1 WHERE cVenValue = @cVenValue),
T3 AS --显示其它材料厂编码的信息 材料厂编码用Y表示
(SELECT CIQuotesID,cSubInvCode,cVenValue = 'Y',VenIndex FROM T1 WHERE cVenValue <> @cVenValue AND ISNULL(cVenValue,'') <> ''),
T4 AS --显示空材料厂编码的信息 材料厂编码用N表示
(SELECT CIQuotesID,cSubInvCode,cVenValue = 'N',VenIndex FROM T1 WHERE ISNULL(cVenValue,'') = ''),
T5 AS --根据主键ID进行关联
(SELECT t2.CIQuotesID, --材料报价单ID
t2.cSubInvCode, --材料编码
t2.cVenValue, --材料厂编码(显示相关的材料厂编码)
t2.VenIndex, --材料厂编码对应的位置(显示相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
cVenValue2 = t3.cVenValue, --材料厂编码(显示不相关的材料厂编码)
VenIndex2 = t3.VenIndex, --材料厂编码对应的位置(显示不相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
cVenValue3 = t4.cVenValue, --材料厂编码(显示空的材料厂编码)
VenIndex3 = t4.VenIndex --材料厂编码对应的位置(显示空的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
FROM T2
LEFT JOIN T3 ON t2.CIQuotesID = t3.CIQuotesID --一定要Left Join 以显示包括的材料厂编码的信息的表为主
LEFT JOIN T4 ON t2.CIQuotesID = t4.CIQuotesID), --一定要Left Join 以显示包括的材料厂编码的信息的表为主
T6 AS --对包括的材料厂编码、其它的材料厂编码和空材料厂编码进行行转列的操作
(SELECT CIQuotesID, --材料报价单ID
cSubInvCode, --材料编码
cVenA1 = MAX(CASE WHEN VenIndex = 1 THEN cVenValue END), --材料厂编码1(显示相关的材料厂编码)
cVenB1 = MAX(CASE WHEN VenIndex2 = 1 THEN cVenValue2 END), --材料厂编码1(显示不相关的材料厂编码)
cVenC1 = MAX(CASE WHEN VenIndex3 = 1 THEN cVenValue3 END), --材料厂编码1(显示空的材料厂编码)
cVenA2 = MAX(CASE WHEN VenIndex = 2 THEN cVenValue END), --材料厂编码2(显示相关的材料厂编码)
cVenB2 = MAX(CASE WHEN VenIndex2 = 2 THEN cVenValue2 END), --材料厂编码2(显示不相关的材料厂编码)
cVenC2 = MAX(CASE WHEN VenIndex3 = 2 THEN cVenValue3 END), --材料厂编码2(显示空的材料厂编码)
cVenA3 = MAX(CASE WHEN VenIndex = 3 THEN cVenValue END), --材料厂编码3(显示相关的材料厂编码)
cVenB3 = MAX(CASE WHEN VenIndex2 = 3 THEN cVenValue2 END), --材料厂编码3(显示不相关的材料厂编码)
cVenC3 = MAX(CASE WHEN VenIndex3 = 3 THEN cVenValue3 END) --材料厂编码3(显示空的材料厂编码)
FROM T5
GROUP BY CIQuotesID,cSubInvCode),
T7 AS
(SELECT CIQuotesID, --材料报价单ID
cSubInvCode, --材料编码
cVen1 = CASE --材料厂编码(对应的材料厂编码、其它的材料厂编码和空材料厂编码 一定会有一个有存在 另外两个相关的都为空)
WHEN LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenB1,'')) AND LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenA1
WHEN LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenB1
WHEN LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenB1,'')) THEN cVenC1
END,
cVen2 = CASE --下面的Case判断就只取有值的赋给它对应的材料厂编码1,2,3本身
WHEN LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenB2,'')) AND LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenA2
WHEN LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenB2
WHEN LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenB2,'')) THEN cVenC2
END,
cVen3 = CASE
WHEN LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenB3,'')) AND LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenA3
WHEN LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenB3
WHEN LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenB3,'')) THEN cVenC3
END
FROM T6)
SELECT * FROM T7
/*
最终结果如下：
ID 材料编码 材料厂编码1 材料厂编码2 材料厂编码3
1 39010000098 100595 Y Y
2 39080000001 Y 100595 NULL
3 39150000101 Y NULL 100595
*/
--可以直接放到查询分析器执行
--判断测试表TmpTCIQuote是否有存在 不存在则创建
if NOT exists (select * from dbo.sysobjects where id = object_id(N'dbo.TmpTCIQuote') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
create table TmpTCIQuote(
CIQuotesID int not null, --材料报价单ID
cSubInvCode VARCHAR(100) NOT NULL, --材料编码
cVen1 VARCHAR(50), --材料厂编码1
cMVenName1 VARCHAR(200), --材料厂名称1
cVen2 VARCHAR(50), --材料厂编码2
cMVenName2 VARCHAR(200), --材料厂名称2
cVen3 VARCHAR(50), --材料厂编码3
cMVenName3 VARCHAR(200) --材料厂名称3
primary key (CIQuotesID)
);
END
--清空数据
TRUNCATE TABLE TmpTCIQuote
--插入默认数据
INSERT INTO TmpTCIQuote(CIQuotesID,cSubInvCode,cVen1,cMVenName1,cVen2,cMVenName2,cVen3,cMVenName3)
SELECT 1,'39010000098','100595','东莞利丰商标制造有限公司','100823','广州南沙经济技术开发区彩佳印花有限公司','100436','晋江市绿雨彩色印刷有限公司' UNION ALL
SELECT 2,'39080000001','100823','广州南沙经济技术开发区彩佳印花有限公司','100595','东莞利丰商标制造有限公司',NULL,NULL UNION ALL
SELECT 3,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,'100595','东莞利丰商标制造有限公司' UNION ALL
SELECT 4,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,NULL,NULL
SELECT * FROM TmpTCIQuote
/*
原始数据
ID 材料编码 材料厂编码1 材料厂名称1 材料厂编码2 材料厂名称2 材料厂编码3 材料厂名称3
1 39010000098 100595 东莞利丰商标制造有限公司 100823 广州南沙经济技术开发区彩佳印花有限公司 100436 晋江市绿雨彩色印刷有限公司
2 39080000001 100823 广州南沙经济技术开发区彩佳印花有限公司 100595 东莞利丰商标制造有限公司 NULL NULL
3 39150000101 100436 晋江市绿雨彩色印刷有限公司 NULL NULL 100595 东莞利丰商标制造有限公司
4 39150000101 100436 晋江市绿雨彩色印刷有限公司 NULL NULL NULL NULL
描述：只显示对应的材料厂编码 不相关的材料厂编码用Y表示(表示对应的已经有其它材料供应商) 为空的材料厂编码用Null表示
假设只拥有材料厂编码100595的查看数据权限 其它材料厂编码不能查看 有其它材料厂编码用Y表示 空的用Null表示
最终结果如下：
ID 材料编码 材料厂编码1 材料厂名称1 材料厂编码2 材料厂名称2 材料厂编码3 材料厂名称3
1 39010000098 100595 东莞利丰商标制造有限公司 Y Y
2 39080000001 Y 100595 东莞利丰商标制造有限公司 Null
3 39150000101 Y Null 100595 东莞利丰商标制造有限公司
*/
DECLARE @cVenValue VARCHAR(50) --定义材料厂编码
SET @cVenValue = '100595'; --只允许查看100595的材料厂编码
WITH T1 AS --对三个材料厂编码进行列转行
(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex = REPLACE(cVen,'cVen','') FROM TmpTCIQuote
UNPIVOT (cVenValue FOR cVen IN (cVen1,cVen2,cVen3)) cVen),
T2 AS --显示包括的材料厂编码的信息
(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex FROM T1 WHERE cVenValue = @cVenValue),
T3 AS --显示其它材料厂编码的信息 材料厂编码用Y表示
(SELECT CIQuotesID,cSubInvCode,cVenValue = 'Y',VenIndex FROM T1 WHERE cVenValue <> @cVenValue AND ISNULL(cVenValue,'') <> ''),
T4 AS --显示空材料厂编码的信息 材料厂编码用N表示
(SELECT CIQuotesID,cSubInvCode,cVenValue = 'N',VenIndex FROM T1 WHERE ISNULL(cVenValue,'') = ''),
T5 AS --根据主键ID进行关联
(SELECT t2.CIQuotesID, --材料报价单ID
t2.cSubInvCode, --材料编码
t2.cVenValue, --材料厂编码(显示相关的材料厂编码)
t2.VenIndex, --材料厂编码对应的位置(显示相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
cVenValue2 = t3.cVenValue, --材料厂编码(显示不相关的材料厂编码)
VenIndex2 = t3.VenIndex, --材料厂编码对应的位置(显示不相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
cVenValue3 = t4.cVenValue, --材料厂编码(显示空的材料厂编码)
VenIndex3 = t4.VenIndex --材料厂编码对应的位置(显示空的材料厂编码 如三个材料供应商 对应的1，2，3的位置)
FROM T2
LEFT JOIN T3 ON t2.CIQuotesID = t3.CIQuotesID --一定要Left Join 以显示包括的材料厂编码的信息的表为主
LEFT JOIN T4 ON t2.CIQuotesID = t4.CIQuotesID), --一定要Left Join 以显示包括的材料厂编码的信息的表为主
T6 AS --对包括的材料厂编码、其它的材料厂编码和空材料厂编码进行行转列的操作
(SELECT CIQuotesID, --材料报价单ID
cSubInvCode, --材料编码
cVenA1 = MAX(CASE WHEN VenIndex = 1 THEN cVenValue END), --材料厂编码1(显示相关的材料厂编码)
cVenB1 = MAX(CASE WHEN VenIndex2 = 1 THEN cVenValue2 END), --材料厂编码1(显示不相关的材料厂编码)
cVenC1 = MAX(CASE WHEN VenIndex3 = 1 THEN cVenValue3 END), --材料厂编码1(显示空的材料厂编码)
cVenA2 = MAX(CASE WHEN VenIndex = 2 THEN cVenValue END), --材料厂编码2(显示相关的材料厂编码)
cVenB2 = MAX(CASE WHEN VenIndex2 = 2 THEN cVenValue2 END), --材料厂编码2(显示不相关的材料厂编码)
cVenC2 = MAX(CASE WHEN VenIndex3 = 2 THEN cVenValue3 END), --材料厂编码2(显示空的材料厂编码)
cVenA3 = MAX(CASE WHEN VenIndex = 3 THEN cVenValue END), --材料厂编码3(显示相关的材料厂编码)
cVenB3 = MAX(CASE WHEN VenIndex2 = 3 THEN cVenValue2 END), --材料厂编码3(显示不相关的材料厂编码)
cVenC3 = MAX(CASE WHEN VenIndex3 = 3 THEN cVenValue3 END) --材料厂编码3(显示空的材料厂编码)
FROM T5
GROUP BY CIQuotesID,cSubInvCode),
T7 AS
(SELECT CIQuotesID, --材料报价单ID
cSubInvCode, --材料编码
cVen1 = CASE --材料厂编码(对应的材料厂编码、其它的材料厂编码和空材料厂编码 一定会有一个有存在 另外两个相关的都为空)
WHEN LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenB1,'')) AND LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenA1
WHEN LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenB1
WHEN LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenB1,'')) THEN cVenC1
END,
cVen2 = CASE --下面的Case判断就只取有值的赋给它对应的材料厂编码1,2,3本身
WHEN LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenB2,'')) AND LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenA2
WHEN LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenB2
WHEN LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenB2,'')) THEN cVenC2
END,
cVen3 = CASE
WHEN LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenB3,'')) AND LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenA3
WHEN LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenB3
WHEN LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenB3,'')) THEN cVenC3
END
FROM T6)
SELECT * FROM T7
/*
最终结果如下：
ID 材料编码 材料厂编码1 材料厂编码2 材料厂编码3
1 39010000098 100595 Y Y
2 39080000001 Y 100595 NULL
3 39150000101 Y NULL 100595
*/