34,587
社区成员
发帖
与我相关
我的任务
分享
SELECT
b.申请ID,*
FROM table2 AS t2
INNER JOIN table1 AS b ON t2.类型A值=b.值 AND b.类型='A'
INNER JOIN table1 AS c ON t2.类型B值=c.值 AND b.申请ID=c.申请ID AND b.类型='B'
INNER JOIN table1 AS d ON t2.类型C值=d.值 AND b.申请ID=d.申请ID AND b.类型='C'
create TABLE #T1 (申请ID INT,类型 VARCHAR(10),值 VARCHAR(10))
INSERT INTO #T1
SELECT 1,'A','A001'
UNION ALL SELECT 1,'A','A003'
UNION ALL SELECT 1,'B','B002'
UNION ALL SELECT 2,'A','A003'
UNION ALL SELECT 3,'A','A002'
UNION ALL SELECT 3,'B','B002'
UNION ALL SELECT 3,'B','B003'
UNION ALL SELECT 3,'C','C002'
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+类型 from #T1 group by 类型
print @sql
set @sql='select * from #T1 pivot (max(值) for 类型 in ('+@sql+')) a where 申请ID=3'
print @sql
exec(@sql)
select (select a.值 from t1 a where a.id=b.id and a.类型='A') as "类型A值",
(select a.t a.值 from t1 a where a.id=b.id and a.类型='B') as "类型B值",
(select a.t a.值 from t1 a where a.id=b.id and a.类型='C') as "类型C值" FROM T1 B where B.ID=3
create TABLE #T1 (申请ID INT,类型 VARCHAR(10),值 VARCHAR(10))
INSERT INTO #T1
SELECT 1,'A','A001'
UNION ALL SELECT 1,'A','A003'
UNION ALL SELECT 1,'B','B002'
UNION ALL SELECT 2,'A','A003'
UNION ALL SELECT 3,'A','A002'
UNION ALL SELECT 3,'B','B002'
UNION ALL SELECT 3,'B','B003'
UNION ALL SELECT 3,'C','C002'
CREATE TABLE #T2 (类型A值 VARCHAR(10),类型B值 VARCHAR(10),类型C值 VARCHAR(10))
INSERT INTO #T2
SELECT 'A002','B002','C002'
WITH CTE_1
AS
(SELECT 类型A值 AS VALUE,'A' AS VALUE_TYPE FROM #T2
UNION ALL
SELECT 类型B值,'B' FROM #T2
UNION ALL
SELECT 类型C值,'C' FROM #T2),
CTE_2
AS
(SELECT *,COUNT(*) OVER (PARTITION BY 1) AS QTY FROM CTE_1)
SELECT *
FROM
(SELECT 申请ID,COUNT(DISTINCT 值) AS QTY_MATCH,MAX(QTY) AS QTY
FROM CTE_2 A
JOIN #T1 B ON A.VALUE_TYPE=B.类型 AND A.VALUE=B.值
GROUP BY 申请ID) AS A
WHERE QTY=QTY_MATCH
--1,2楼的代码不严谨,请版主帮忙删除, 谢谢!
--按下面的代码
DECLARE @t1 TABLE (applyId INT,[type] VARCHAR(10),[v] VARCHAR(10))
DECLARE @t2 TABLE (typeA VARCHAR(10),[typeB] VARCHAR(10),[typeC] VARCHAR(10))
INSERT INTO @t1
SELECT 1,'A','A001'
UNION ALL SELECT 1,'A','A003'
UNION ALL SELECT 1,'B','B002'
UNION ALL SELECT 2,'A','A003'
UNION ALL SELECT 3,'A','A002'
UNION ALL SELECT 3,'B','B002'
UNION ALL SELECT 3,'B','B003'
UNION ALL SELECT 3,'C','C002'
INSERT INTO @t2
SELECT 'A002','B002','C002'
;WITH cte AS (
SELECT a.* FROM @t1 AS a INNER JOIN @t2 AS b ON a.v=b.typeA
UNION ALL
SELECT a.* FROM @t1 AS a INNER JOIN @t2 AS b ON a.v=b.typeB
UNION ALL
SELECT a.* FROM @t1 AS a INNER JOIN @t2 AS b ON a.v=b.typeC
)
SELECT * FROM cte WHERE applyId IN (
SELECT a.applyId FROM cte AS a
WHERE [type]='A'
AND EXISTS(
SELECT 1 FROM cte AS b
WHERE a.applyId=b.applyId AND b.[type] IN ('B','C')
)
)
/*
applyId type v
3 A A002
3 B B002
3 C C002
*/