如何编写查询以抑制结果集列中的连续重复值? [英] how to write a query to suppress consecutive repeating values in resultset columns?

问题描述

选择表格时，我需要将列名(TGroup_Name)下的重复值设为空或空

例如:

在下表中，列名 (TGroup_Name) 在两行中具有重复值 (ED_1).所以我想让第二行值(ED_1)为空或为空.但不应该影响整行.

<前>TGID TParent_Group_ID TGroup_Name TGroup_Type TEID TEmp_Group_ID TEmp_Name TEmp_Type1 0 MD G 10 1 马利克 GH10 1 ED_1 G 5 10 Thevan GH10 1 ED_1 G 7 10 古普塔 U11 1 ED_2 G 11 11 Dinesh GH

解决方案

大多数开发人员认为最好使用应用程序端过程代码(如 Java 或 PHP)抑制结果集中此类连续重复值.

但是你可以在像这样的 SQL 的 MySQL 变体中做到这一点 (http://sqlfiddle.com/#!2/691f5/4/0).

SELECT TGID,TParent,IF(Group_ID=@PREV,'',@PREV:=Group_ID) AS Group_ID,TGroup_Name从表1，(SELECT @PREV:='xxxx') AS i按表1.TGID排序

IF 与@prev 的运行值相结合，可以抑制连续重复.额外的 (SELECT...) 子查询用于初始化 @prev.

这是一项棘手的工作.例如，如果你只说 ORDER BY TGID 而不是我所展示的，它就会出错.它也完全不可移植到其他品牌和型号的表服务器.

I need to make repeated values be empty or null under Column Name (TGroup_Name),when selecting Table

For Example:

In below table Column Name(TGroup_Name) having repeated values (ED_1) in two rows. So i want to make null or empty second row value(ED_1).But should not affect entire row.

TGID    TParent_Group_ID    TGroup_Name TGroup_Type TEID    TEmp_Group_ID   TEmp_Name   TEmp_Type
1              0                MD            G      10           1           Mallik        GH
10             1                ED_1          G       5          10           Thevan        GH
10             1                ED_1          G       7          10           Gupta          U
11             1                ED_2          G      11          11           Dinesh        GH

解决方案

Most developers think it's best to suppress such consecutive repeating values in their result sets using application-side procedural code (like Java or PHP).

But you can do it in the MySQL variant of SQL like this (http://sqlfiddle.com/#!2/691f5/4/0).

SELECT TGID,
       TParent,
       IF(Group_ID=@PREV,'',@PREV:=Group_ID) AS Group_ID,
       TGroup_Name
  FROM TABLE1,
       (SELECT @PREV:='xxxx') AS i
 ORDER BY TABLE1.TGID 

The IF combined with the running value of @prev suppresses consecutive duplicates. The extra (SELECT...) subquery serves to initialize @prev.

This is a tricky business to get right. For example, if you just say ORDER BY TGID instead of what I have shown it will come out wrong. It's also entirely nonportable to other makes and models of table server.

这篇关于如何编写查询以抑制结果集列中的连续重复值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！