Home Database Mysql Tutorial 一个有关DISTINCT的问题解答_MySQL

一个有关DISTINCT的问题解答_MySQL

Jun 01, 2016 pm 02:06 PM
bb from id select

经常会有朋友问到类似于这样的问题,
表中的数据如下
ID AA BB
1 123 456
1 4535 54
1 60 6564
1 60 656
2 50 664
2 60 6
3 89 89
4 40 4242
希望得到的结果是
ID AA BB
1 123 456
2 50 664
3 89 89
4 40 4242
当然了,环境是SQL Server
解答及分析如下:
1, 不少朋友希望用distinct就解决问题,但不可能,disctinct将重复的记录忽略,
但它忽略的是完全一致的重复记录,而不是其中某个字段重复的记录,所以也只有
这样的语法
select distinct ID,AA,BB from tName
其它诸如select distinct(ID),AA,BB from tName 或
select ID,distinct AA,BB的写法都是无效的
2, 使用group by和聚合函数
select ID,MAX(AA) AS AA,MAX(BB) AS BB from tName group by ID
可以得到如下结果
ID AA BB
1 4535 6564
2 60 664
3 89 89
4 40 4242
ID是唯一了,但不一定后面的字段是同一条记录的
3, 使用临时表
select IDENTITY(INT,1,1) as TID,ID,AA,BB into #Tmp from tName
select t1.ID,t1.AA,t1.BB from #Tmp t1 where t1.TID in
(select min(T2.TID) from #Tmp t2 group by t2.ID)
这样可以得到符合要求的结果
不过用了两个T-SQL语句,
而且如果是大数据量的话,性能问题将很突出
到目前为止,我还没找到用一个T-SQL语句实现同样功能的方法,
如果谁有,希望补充

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

This Apple ID is not yet in use in the iTunes Store: Fix This Apple ID is not yet in use in the iTunes Store: Fix Jun 10, 2024 pm 05:42 PM

This Apple ID is not yet in use in the iTunes Store: Fix

Fix event ID 55, 50, 98, 140 disk error in event viewer Fix event ID 55, 50, 98, 140 disk error in event viewer Mar 19, 2024 am 09:43 AM

Fix event ID 55, 50, 98, 140 disk error in event viewer

Where can I find Alibaba ID? Where can I find Alibaba ID? Mar 08, 2024 pm 09:49 PM

Where can I find Alibaba ID?

Event ID 4660: Object deleted [Fix] Event ID 4660: Object deleted [Fix] Jul 03, 2023 am 08:13 AM

Event ID 4660: Object deleted [Fix]

Where to check Tencent Video ID Where to check Tencent Video ID Feb 24, 2024 pm 06:25 PM

Where to check Tencent Video ID

Asynchronous processing method of Select Channels Go concurrent programming using golang Asynchronous processing method of Select Channels Go concurrent programming using golang Sep 28, 2023 pm 05:27 PM

Asynchronous processing method of Select Channels Go concurrent programming using golang

How to hide the select element in jquery How to hide the select element in jquery Aug 15, 2023 pm 01:56 PM

How to hide the select element in jquery

What are the solutions for redis distributed ID? What are the solutions for redis distributed ID? Jun 03, 2023 am 10:14 AM

What are the solutions for redis distributed ID?

See all articles