您的位置: 首页  >  IT文章  >  SQLSERVER两表联合查询，难！该怎么处理

SQLSERVER两表联合查询，难！该怎么处理

分类: IT文章 • 2024-11-22 09:02:49
SQLSERVER两表联合查询，难！
表1：10列(F1-F10，数据都是3,1,0)，随机数据如下：
0 3 1 0 3 3 3 3 0 0
3 3 0 1 0 1 3 3 3 1
1 3 3 1 1 3 0 1 3 3
1 0 3 3 1 3 0 1 3 3
1 3 3 3 3 3 3 3 1 1
1 0 1 3 0 1 3 1 3 0
3 3 3 1 3 0 0 1 3 3
3 3 0 3 3 0 1 3 3 1
0 3 3 3 1 0 3 3 0 0
1 1 1 3 3 1 3 0 1 3
0 0 0 1 3 0 1 3 1 3
3 3 1 1 0 1 1 3 3 0

表2：10列(F1-F10，数据都是3,1,0)，随机数据如下：
3 1 0 3 3 1 3 1 1 1
1 3 3 3 3 3 3 0 3 3
1 3 3 1 3 3 0 3 0 0
3 0 1 0 0 0 1 3 1 0
1 1 0 3 0 3 0 0 3 3
1 3 1 3 0 0 3 0 3 1


问1：如何求表1、表2有相同对应9列的数据？
问2：如何求表1、表2至少有相同对应7列的数据？
问3：如何求表1中有5个3，3个1，2个0的结果集合？
问4：如何统计表1中没行含有几个3，几个1，几个0？
------解决思路----------------------
问题1和2没看懂什么意思，3、4的解决方法如下
create table #A

(

	F1 INT,

	F2 INT,

	F3 INT,

	F4 INT,

	F5 INT,

	F6 INT,

	F7 INT,

	F8 INT,

	F9 INT,

	F10 INT

)

INSERT INTO #A Values

(0,3,	1,	0,	3,	3,	3,	3,	0,	0),

(3,	3,	0,	1,	0,	1,	3,	3,	3,	1),

(1,	3,	3,	1,	1,	3,	0,	1,	3,	3),

(1,	0,	3,	3,	1,	3,	0,	1,	3,	3),

(1,	3,	3,	3,	3,	3,	3,	3,	1,	1),

(1,	0,	1,	3,	0,	1,	3,	1,	3,	0),

(3,	3,	3,	1,	3,	0,	0,	1,	3,	3),

(3,	3,	0,	3,	3,	0,	1,	3,	3,	1),

(0,	3,	3,	3,	1,	0,	3,	3,	0,	0),

(1,	1,	1,	3,	3,	1,	3,	0,	1,	3),

(0,	0,	0,	1,	3,	0,	1,	3,	1,	3),

(3,	3,	1,	1,	0,	1,	1,	3,	3,	0)


select *

from #A

where len(ltrim(F1)+ltrim(F2)+ltrim(F3)+ltrim(F4)+ltrim(F5)+ltrim(F6)+ltrim(F7)+ltrim(F8)+ltrim(F9)+ltrim(F10))-len(replace((ltrim(F1)+ltrim(F2)+ltrim(F3)+ltrim(F4)+ltrim(F5)+ltrim(F6)+ltrim(F7)+ltrim(F8)+ltrim(F9)+ltrim(F10)),'3',''))=5


F1          F2          F3          F4          F5          F6          F7          F8          F9          F10

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

0           3           1           0           3           3           3           3           0           0

3           3           0           1           0           1           3           3           3           1

1           3           3           1           1           3           0           1           3           3

1           0           3           3           1           3           0           1           3           3

0           3           3           3           1           0           3           3           0           0


(5 行受影响)



思路如下
select len('0310333300')-len(replace('0310333300','3',''))

------解决思路----------------------
最近好像有几个这样问题，难道是哪个公司面试题目吗？
参考一下我的这个方案。



DECLARE @SameNums  INT

DECLARE @SameValue INT


SET @SameNums  = 7  --设置相同列的数量

SET @SameValue = 3  --设置相同的数字，取值为（0，1，3）

-- 给每条记录编号增加一个RowID字段，记录行号 

;WITH Tab1(RowID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) AS 

(

	SELECT 1,0,3,1,0,3,3,3,3,0,0 UNION ALL

    SELECT 2,3,3,0,1,0,1,3,3,3,1 UNION ALL

	SELECT 3,1,3,3,1,1,3,0,1,3,3 UNION ALL

	SELECT 4,1,0,3,3,1,3,0,1,3,3 UNION ALL

	SELECT 5,1,3,3,3,3,3,3,3,1,1 UNION ALL

	SELECT 6,1,0,1,3,0,1,3,1,3,0 UNION ALL

	SELECT 7,3,3,3,1,3,0,0,1,3,3 UNION ALL

	SELECT 8,3,3,0,3,3,0,1,3,3,1 UNION ALL

	SELECT 9,0,3,3,3,1,0,3,3,0,0 UNION ALL

	SELECT 10,1,1,1,3,3,1,3,0,1,3 UNION ALL

	SELECT 11,0,0,0,1,3,0,1,3,1,3 UNION ALL

	SELECT 12,3,3,1,1,0,1,1,3,3,0

),

Tab2(RowID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) AS 

(

	SELECT 1,3,1,0,3,3,1,3,1,1,1 UNION ALL

    SELECT 2,1,3,3,3,3,3,3,0,3,3 UNION ALL

	SELECT 3,1,3,3,1,3,3,0,3,0,0 UNION ALL

	SELECT 4,3,0,1,0,0,0,1,3,1,0 UNION ALL

	SELECT 5,1,1,0,3,0,3,0,0,3,3 UNION ALL

	SELECT 6,1,3,1,3,0,0,3,0,3,1 UNION ALL

	SELECT 7,3,3,3,1,3,0,0,1,1,3 UNION ALL

	SELECT 8,3,3,1,1,0,1,1,3,3,0

),

Tab11 AS (

	SELECT

		RowID,

		b.ColName,

		b.nValue 

	FROM Tab1

	UNPIVOT( nValue FOR ColName IN (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) ) b

),

Tab22 AS (

	SELECT

		RowID,

		b.ColName,

		b.nValue 

	FROM Tab2

	UNPIVOT( nValue FOR ColName IN (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) ) b

),

SameCols AS (

	SELECT 

		a.RowID AS TabA,

		b.RowID AS TabB,

		COUNT(*) AS SameNums

	FROM 

		Tab11 a,Tab22 b

	WHERE 

		a.ColName = b.ColName AND a.nValue = b.nValue

	GROUP BY

		b.RowID,a.RowID

),

SameNums AS(

	SELECT 

		a.RowID, 

		a.nValue,

		COUNT(*) AS SameNums

	FROM Tab11 a

	GROUP BY a.RowID,a.nValue

)

-- 问题1和2的解决方案

--问1：如何求表1、表2有相同对应9列的数据？

--问2：如何求表1、表2至少有相同对应7列的数据？

/**/

SELECT 

	LTRIM(SameNums)+'列相同' AS '说明', 

	'Tab1' AS TableName,a.*,

	'Tab2' AS TableName,b.*

FROM 

	Tab1 a 

	INNER JOIN SameCols c ON a.RowID = c.TabA

	INNER JOIN Tab2 b ON b.RowID = c.TabB

WHERE 

	c.SameNums >= @SameNums

ORDER BY

	c.SameNums DESC


/*

--问题3的解决方案

--问3：如何求表1中有5个3，3个1，2个0的结果集合？

SELECT 

	'本行至少包含'+LTRIM(@SameNums)+'个'+LTRIM(@SameValue) AS '说明',

	* 

FROM 

	Tab1 a 

WHERE a.RowID IN (

		SELECT

			b.RowID 

		FROM

			SameNums b 

		WHERE 

			b.nValue= @SameValue AND  b.SameNums >= @SameNums

		)

*/

/*

--问题4的解决方案

--问4：如何统计表1中每行含有几个3，几个1，几个0？

SELECT a.*,b.说明 

FROM Tab1 a 

INNER JOIN (	

		SELECT

			a.RowID,

			'本行有' 

			+ LTRIM(ISNULL(SUM(CASE WHEN nValue = 3 THEN SameNums ELSE 0 END),0)) +'个3，'

			+ LTRIM(ISNULL(SUM(CASE WHEN nValue = 1 THEN SameNums ELSE 0 END),0)) +'个1，'

			+ LTRIM(ISNULL(SUM(CASE WHEN nValue = 0 THEN SameNums ELSE 0 END),0)) +'个0。'AS '说明'

		FROM 

			SameNums a

		GROUP BY 

			a.RowID) b ON a.RowID = b.RowID

ORDER BY

	a.RowID

*/


说明	TableName	RowID	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	TableName	RowID	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10

10列相同	Tab1	12	3	3	1	1	0	1	1	3	3	0	Tab2	8	3	3	1	1	0	1	1	3	3	0

9列相同	Tab1	7	3	3	3	1	3	0	0	1	3	3	Tab2	7	3	3	3	1	3	0	0	1	1	3

7列相同	Tab1	5	1	3	3	3	3	3	3	3	1	1	Tab2	2	1	3	3	3	3	3	3	0	3	3

7列相同	Tab1	2	3	3	0	1	0	1	3	3	3	1	Tab2	8	3	3	1	1	0	1	1	3	3	0



说明	RowID	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10

本行至少包含7个3	5	1	3	3	3	3	3	3	3	1	1



RowID	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	说明

1	0	3	1	0	3	3	3	3	0	0	本行有5个3，1个1，4个0。

2	3	3	0	1	0	1	3	3	3	1	本行有5个3，3个1，2个0。

3	1	3	3	1	1	3	0	1	3	3	本行有5个3，4个1，1个0。



相关推荐