代码之家 › 专栏 › 技术社区 › FranAguiar

获取具有重复数据的表的n个元素的SQL查询

sql mysql

FranAguiar · 技术社区 · 6 年前

我有一个以这种格式返回数据的查询

| Name | SomeData | MoreStuff | |--------|-------------|---------------| | asset1 | I need this | And also this | | asset1 | I need this | And also this | | asset1 | I need this | And also this | | asset2 | I need this | And also this | | asset2 | I need this | And also this | | asset3 | I need this | And also this | | asset3 | I need this | And also this | | asset3 | I need this | And also this | | asset4 | I need this | And also this | | asset5 | I need this | And also this | | asset5 | I need this | And also this | | ...... | ........... | ............. |

假设我需要20种不同的资产,但每行的数据也是如此。这里的“限制”不起作用,“分组依据”也不起作用。

我还有其他的选择吗?

-----编辑----

例如,如果我需要3种不同的资产,产出应该是

| Name | SomeData | MoreStuff | |--------|-------------|---------------| | asset1 | I need this | And also this | | asset1 | I need this | And also this | | asset1 | I need this | And also this | | asset2 | I need this | And also this | | asset2 | I need this | And also this | | asset3 | I need this | And also this | | asset3 | I need this | And also this | | asset3 | I need this | And also this |

4 回复 | 直到 6 年前

M Khalid Junaid 6 年前

您可以通过对同一个表进行联接来获得所需的结果集,但行数有限,如

select a.*
from demo a
join (
  select distinct Name
  from demo 
  order by Name
  limit 3
) b on a.Name = b.Name

Demo

MT0 6 年前

这将获取前3个资产的行,而不必使用自联接:

SQL Fiddle

MySQL5.6架构设置 :

CREATE TABLE table_name (
  Name      VARCHAR(20),
  SomeData  VARCHAR(20),
  MoreStuff VARCHAR(20)
);


INSERT INTO table_name VALUES ( 'asset4', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset5', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset2', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset3', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset1', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset3', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset2', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset1', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset1', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset3', 'I need this', 'And also this' );
INSERT INTO table_name VALUES ( 'asset5', 'I need this', 'And also this' );

查询1 :

SELECT Name, SomeData, MoreStuff
FROM   (
  SELECT @asset_num := IF( @prev_name = t.name, @asset_num, @asset_num + 1 ) AS an,
         t.*,
         @prev_name := Name
  FROM   table_name t
         CROSS JOIN
         ( SELECT @prev_name := '', @asset_num := 0 ) r
  ORDER BY Name
) t
WHERE an <= 3

Results :

|   Name |    SomeData |     MoreStuff |
|--------|-------------|---------------|
| asset1 | I need this | And also this |
| asset1 | I need this | And also this |
| asset1 | I need this | And also this |
| asset2 | I need this | And also this |
| asset2 | I need this | And also this |
| asset3 | I need this | And also this |
| asset3 | I need this | And also this |
| asset3 | I need this | And also this |

Elyor Murodov 6 年前

试试这个:

select *
from TABLE
where Name in (
    select distinct Name 
    from TABLE
    limit 3
)

Abhilash Ravindran C K 6 年前

这将为每个资产提供最多3行。您可以将3更改为任意数字,然后您将获得每个资产的许多行。

SELECT Name, SomeData, MoreStuff
FROM (
       SELECT  @name_number := IF(@Name = Name, @name_number + 1, 1) AS name_number, 
               @Name := Name as Name, SomeData, MoreStuff
       FROM 
              (SELECT @name_number := 1) x, 
              (SELECT SomeData, MoreStuff, @Name := Name as Name FROM your_table ORDER BY Name) y
      ) z
WHERE name_number <= 3;

Sql Fiddle Demo Here!