如何从单个表中选择多个列值相同的行

发布于 2024-12-23 03:55:52 字数 789 浏览 0 评论 0原文

我有一个 SQL Server 表。现在这个表有像主键Id，A，B，C，D，E，F，G

这样的列现在我想从这个表中选择行，这样

A=A, B=B, C=C, D=D and G > 132

我我试图从此表中选择行 A,B,C,D 列具有相同的数据且 G 列数据 > 132.

那么我该怎么做呢？谢谢。

我尝试了这个查询，但返回相同的 Id 行

    SELECT TableA.Id,TableA.UserId,TableA.MaximumHp,TableA.Attack,TableA.Defense,TableA.SpAttack,TableA.SpDefense,TableA.Speed
FROM myTable as TableA
Inner Join myTable as TableB on 
TableA.MaximumHp = TableB.MaximumHp
  AND TableA.Attack = TableB.Attack
  AND TableA.Defense = TableB.Defense
    AND TableA.SpAttack = TableB.SpAttack
      AND TableA.SpDefense = TableB.SpDefense
        AND TableA.Speed = TableB.Speed
       AND TableA.Id != TableB.Id

SQL Server 2008 R2

原文

I have a SQL Server table. Now this table has columns like primary key Id, A, B, C, D, E, F, G

Now I want to select rows from this table like this

A=A, B=B, C=C, D=D and G > 132

So I am trying to select rows from this table which rows A,B,C,D columns has same data and G column data > 132.

So how can I do that ? Thank you.

I tried this query but returning same Id rows

    SELECT TableA.Id,TableA.UserId,TableA.MaximumHp,TableA.Attack,TableA.Defense,TableA.SpAttack,TableA.SpDefense,TableA.Speed
FROM myTable as TableA
Inner Join myTable as TableB on 
TableA.MaximumHp = TableB.MaximumHp
  AND TableA.Attack = TableB.Attack
  AND TableA.Defense = TableB.Defense
    AND TableA.SpAttack = TableB.SpAttack
      AND TableA.SpDefense = TableB.SpDefense
        AND TableA.Speed = TableB.Speed
       AND TableA.Id != TableB.Id

SQL Server 2008 R2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

全部不再 2024-12-30 03:55:52

听起来您想将表连接到自身

SELECT *
FROM Table t1
Inner Join Table t2 on t1.A = t2.A
  AND t1.B = t2.B
  AND t1.C = t2.C
  AND t1.D = t2.D
  AND t1.G > 132
  AND t1.ID <> t2.ID

Sounds like you want to join the table to itself

SELECT *
FROM Table t1
Inner Join Table t2 on t1.A = t2.A
  AND t1.B = t2.B
  AND t1.C = t2.C
  AND t1.D = t2.D
  AND t1.G > 132
  AND t1.ID <> t2.ID

回复收藏 0 原文

三岁铭 2024-12-30 03:55:52

我认为你的意思是重复。告诉我这是否是您要找的。

SELECT [Table].A, [Table].B, [Table].C, [Table].D, [Table].E, [Table].F, [Table].G
FROM [Table] LEFT JOIN (SELECT A, B, C, D FROM [Table] 
GROUP BY A, B, C, D
HAVING count(*) > 1)
AS sub ON ([Table].A=sub.A) AND ([Table].B=sub.B) AND ([Table].C=sub.C) AND ([Table].D=sub.D)
WHERE G>132 and sub.A is not null;

这将为您提供 a、b、c 和 D 等于表中另一行的所有行...并且 G > > 132

I THINK what you mean is duplicates. Tell me if this is what you are looking for.

SELECT [Table].A, [Table].B, [Table].C, [Table].D, [Table].E, [Table].F, [Table].G
FROM [Table] LEFT JOIN (SELECT A, B, C, D FROM [Table] 
GROUP BY A, B, C, D
HAVING count(*) > 1)
AS sub ON ([Table].A=sub.A) AND ([Table].B=sub.B) AND ([Table].C=sub.C) AND ([Table].D=sub.D)
WHERE G>132 and sub.A is not null;

This will give you all the rows where a,b,c, and D are equal to another row in the table...and G > 132

回复收藏 0 原文

~没有更多了~