跪求一SQL 实现, 不胜感激.

「已注销」 2018-01-26 08:34:36
有两张结构如下的表, 需求是表2中的 类型A值 +类型B值 或者 类型A值 +类型C值 在表A中 查找,
表2中字段"类型A“的值对应 表1中字段"类型"是A 的对应的字段”值“ 值,
表2中字段"类型B“的值对应 表1中字段"类型"是B 的对应的字段”值“值,
表2中字段"类型B“的值对应 表1中字段"类型"是B 的对应的字段”值“值,

想通过SQL 找出符合的ID, 下面例子应该查出 ID:3

表1

申请ID,类型,值
1,A,A001
1,A,A003
1,B,B002
2,A,A003
3,A,A002
3,B,B002
3,B,B003
3,C,C002

表2
类型A值,类型B值,类型A值
A002,B002,C002

...全文
864 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
Secends_ 2018-02-09
  • 打赏
  • 举报
回复
[yabao=][/yabao]
中国风 2018-02-02
  • 打赏
  • 举报
回复
e.g.
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'
t101lian 2018-02-02
  • 打赏
  • 举报
回复
表的设计最好修改下, 数量一大查询速度就会很慢
jaki-egg 2018-02-01
  • 打赏
  • 举报
回复
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
RINK_1 2018-01-27
  • 打赏
  • 举报
回复

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
吉普赛的歌 版主 2018-01-26
  • 打赏
  • 举报
回复
--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
*/

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧