隐藏 MySQL 查询中的重复结果 [英] Hide duplicate results in MySQL query

问题描述

我有一个对结果进行分组的查询:<代码>选择品牌、描述、产品代码发件人表按品牌、产品代码分组现在的结果是:

I have a query in which I group results: SELECT brand, description, prodcode FROM table GROUP BY brand, prodcode Now the results are:

brand | description | prodcode
------------------------------
Brand1, Description1, Product1
Brand1, Description2, Product2
Brand1, Description3, Product4

我想看到的是:

brand | description | prodcode
------------------------------
Brand1, Description1, Product1
        Description2, Product2
        Description3, Product4

有什么想法可以在将这些重复数据发送到应用层之前抑制它吗?

Any ideas how to suppress this duplicate data before sending it to the application layer?

推荐答案

总有办法.问题是我们应该这样做吗".请注意，这样做会隐藏品牌数据.如果数据被重新排序、排序，排序器就失去了知道哪个品牌对应哪一行的能力.如果这只是一个打印的报告，不能使用或预期为用户无法操作数据的 PDF，那么就可以了..但如果是这样，用户可以转储到 excel 并稍后进行操作......最好有所有数据.

There's always a way. The question is "Should we do this". Note by doing this you hide the brand data. If the data is reordered, sorted, the sorter loses the ability to know which brand is for what row. If this is just a printed report that is not to be resorted or expected to a PDF where a user can not manipulate the data then ok.. But if this is so a user can dump to excel and manipulate later... better to have all the data.

我个人觉得电子数据上的这种信息显示令人反感，但在印刷表格或静态报告上还可以.原因:在电子版上，我可以导入 excel 排序并处理数据；但如果列缺失"了冗余数据，那么电子操作就会出现问题.

I personally find this display of information distasteful on electronic data, but ok on printed forms or static reports. The reason: on electronic I am able to import into excel sort and play with the data; but if columns are "missing" redundant data then electronic manipulation becomes problematic.

工作 SQLfiddle

CREATE Table foo (
 brand varchar(20),
 description varchar(20),
 prodcode varchar(20));


Insert into foo values ('Brand1', 'Description1', 'Product1'),
('Brand1', 'Description2', 'Product2'),
('Brand1', 'Description3', 'Product4'),
('Brand2', 'Description3', 'Product4'),
('Brand2', 'Description3', 'Product4'),
('Brand1', 'Description3', 'Product3');

查询:

SELECT case when@var <> f.brand then @Var :=brand end as Brand
     , f.description
     , f.prodcode
FROM (SELECT * FROM foo ORDER BY brand, description, prodcode) f
CROSS JOIN  (SELECT @var := '') b

结果:

Brand   description     prodcode
Brand1  Description1    Product1
(null)  Description2    Product2
(null)  Description3    Product3
(null)  Description3    Product4
Brand2  Description3    Product4
(null)  Description3    Product4

为什么会这样:

神奇发生在交叉连接和案例评估中.

The magic happens in the cross join and on the case evaluation.

有一张桌子.我们在加入之前对记录进行排序.我们正在创建一个名为@var 的用户变量，并将其设置为 '' 表 foo 中的第一条记录.然后我们将@var 分配给第一条记录的品牌.当 SQL 交叉连接到第二个字段时，@var 现在是第一条记录的品牌.如果品牌匹配，则不显示记录，如果品牌不匹配，我们更改@var 的值并显示新值.

There's 1 table. We're sorting the records before we join. We're creating a user variable called @var and setting it to '' on the first record in in table foo. Then we assign @var to the brand of the first record. When the SQL cross joins to the 2nd filed @var is now the brand of the first record. If the brands match, no record is displayed, if the brands don't match, we change the value of @var and display the new value.

风险:

• 如果发生其他表连接，或者表中的顺序不符合要求，我们需要先对foo"进行子查询以按品牌进行排序
• 将任何涉及brand1的订单应用于此现在毫无价值
• 它不能重复使用.另一个可能想要利用数据(视图)的模块不再能够这样做，因为品牌已隐藏在某些记录中.

这篇关于隐藏 MySQL 查询中的重复结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！