比较两个SELECT TOP 2查询 [英] Comparing two SELECT TOP 2 queries
本文介绍了比较两个SELECT TOP 2查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想知道前2个代码与其他行的前2个代码相同的项目。 (应该是灵活的,也可以是TOP 5或TOP 25)
例如类似
SELECT itemNo FROM < span class =code-keyword>表 WHERE
( SELECT < span class =code-keyword> TOP 2 代码 FROM 表 WHERE item = 1 ORDER BY 代码)
=
( SELECT TOP 2 代码 FROM 表 WHERE 项<> 1 ORDER BY 代码)
例如
使用itemNo = 1:
{aaa,bbb)= itemNo2 {aaa,bbb} = itemNo3 {bbb,xxx }
表:
itemNo;代码
1; aaa
1; bbb
1; ccc
1; ddd
2; aaa
2; bbb
3; bbb
3; xxx
3; yyy
我希望有人能理解这个问题...... :-)
Andi
解决方案
请阅读我对这个问题的评论,因为我不确定我是否理解你;(
< br $> b $ b
<前lang =sql> DECLARE @tmp 表(文章 VARCHAR ( 30 ),keywrd VARCHAR ( 30 ))
< span class =code-keyword> INSERT INTO @ tmp (article,keywrd)
VALUES (< span class =code-string>' artNo 1',' keyword1'),
(' artNo 1',' keyword2'),
(' artNo 1',' keyword3 '),
(' artNo 1',' keyword4'),
(' artNo 1',' keyword5'),
(' art No 2',' keyword1'),
( artNo 2', keyword2'),
(' artNo 2', ' keyword5'),
(' artNo 2',' keyword6'),
(' artNo 3',' keyword1'),
(' artNo 3',' keyword2'),
(' artNo 3',' keyword3'),
(' artNo 4',' keyword3')
SELECT DISTINCT 文章,STUFF(( SELECT TOP ( 3 )' ,' + keywrd
FROM @ tmp AS t2
WHERE t2.article = t1 .article
FOR XML PATH(' ' )), 1 , 1 ,' ') AS Keywrds
FROM @ tmp AS t1
SELECT 文章,[ 1 ],[ 2 ],[ 3 ]
FROM (
SELECT 文章,keywrd,ROW_NUMBER() OVER ( PARTITION BY article ORDER BY keywrd) AS KeyId
FROM @ tmp
) AS DT
PIVOT(MAX(keywrd) FOR KeyId IN ([ 1 ],[ 2 ],[ 3 ])) AS PT
ORDER < span class =code-keyword> BY [ 1 ],[ 2 ], [ 3 ]
1.查询产生结果:
artNo 1 keyword1,keyword2,keyword3
artNo 2 keyword1,keyword2,keyword5
artNo 3 keyword1,keyword2,keyword3
artNo 4 keyword3
2.查询产生结果:
artNo 1 keyword1 keyword2 keyword3
artNo 3 keyword1 keyword2 keyword3
artNo 2 keyword1 keyword2 keyword5
artNo 4 keyword3 NULL NULL
仍然我不确定它是否符合您的要求。请让我知道你想要达到的目标。
[/ EDIT]
I want to know the items where the first 2 "code" are identical with the first 2 "code" of other rows. (should be flexible, can also be TOP 5 OR TOP 25)
e.g. somthing like
SELECT itemNo FROM Table WHERE
(SELECT TOP 2 code FROM Table WHERE item=1 ORDER BY code)
=
(SELECT TOP 2 code FROM Table WHERE item<>1 ORDER BY code)
e.g.
using itemNo=1:
{aaa,bbb) = itemNo2 {aaa,bbb} = itemNo3 {bbb, xxx}
Table:
itemNo;code
1;aaa
1;bbb
1;ccc
1;ddd
2;aaa
2;bbb
3;bbb
3;xxx
3;yyy
I hope someone will understand the problem... :-)
Andi
解决方案
Please, read my comment to the question, because i'm not sure i understand you well ;(
[EDIT]
DECLARE @tmp TABLE (article VARCHAR(30), keywrd VARCHAR(30)) INSERT INTO @tmp (article, keywrd) VALUES('artNo 1', 'keyword1'), ('artNo 1', 'keyword2'), ('artNo 1', 'keyword3'), ('artNo 1', 'keyword4'), ('artNo 1', 'keyword5'), ('artNo 2', 'keyword1'), ('artNo 2', 'keyword2'), ('artNo 2', 'keyword5'), ('artNo 2', 'keyword6'), ('artNo 3', 'keyword1'), ('artNo 3', 'keyword2'), ('artNo 3', 'keyword3'), ('artNo 4', 'keyword3') SELECT DISTINCT article, STUFF((SELECT TOP(3) ',' + keywrd FROM @tmp AS t2 WHERE t2.article = t1.article FOR XML PATH('')),1,1,'') AS Keywrds FROM @tmp AS t1 SELECT article, [1], [2], [3] FROM ( SELECT article, keywrd, ROW_NUMBER() OVER(PARTITION BY article ORDER BY keywrd) AS KeyId FROM @tmp ) AS DT PIVOT(MAX(keywrd) FOR KeyId IN([1], [2], [3])) AS PT ORDER BY [1], [2], [3]
1. query produces result:
artNo 1 keyword1,keyword2,keyword3 artNo 2 keyword1,keyword2,keyword5 artNo 3 keyword1,keyword2,keyword3 artNo 4 keyword3
2. query produces result:
artNo 1 keyword1 keyword2 keyword3 artNo 3 keyword1 keyword2 keyword3 artNo 2 keyword1 keyword2 keyword5 artNo 4 keyword3 NULL NULL
Still i'm not sure it meets yous requirements. Please, let me know is that what you want to achieve.
[/EDIT]
这篇关于比较两个SELECT TOP 2查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文