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 AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

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

When logging into iTunesStore using AppleID, this error saying "This AppleID has not been used in iTunesStore" may be thrown on the screen. There are no error messages to worry about, you can fix them by following these solution sets. Fix 1 – Change Shipping Address The main reason why this prompt appears in iTunes Store is that you don’t have the correct address in your AppleID profile. Step 1 – First, open iPhone Settings on your iPhone. Step 2 – AppleID should be on top of all other settings. So, open it. Step 3 – Once there, open the “Payment & Shipping” option. Step 4 – Verify your access using Face ID. step

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

If you find event ID 55, 50, 140 or 98 in the Event Viewer of Windows 11/10, or encounter an error that the disk file system structure is damaged and cannot be used, please follow the guide below to resolve the issue. What does Event 55, File system structure on disk corrupted and unusable mean? At session 55, the file system structure on the Ntfs disk is corrupted and unusable. Please run the chkMSK utility on the volume. When NTFS is unable to write data to the transaction log, an error with event ID 55 is triggered, which will cause NTFS to fail to complete the operation unable to write the transaction data. This error usually occurs when the file system is corrupted, possibly due to the presence of bad sectors on the disk or the file system's inadequacy of the disk subsystem.

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

In Alibaba software, once you successfully register an account, the system will assign you a unique ID, which will serve as your identity on the platform. But for many users, they want to query their ID, but don't know how to do it. Then the editor of this website will bring you detailed introduction to the strategy steps below. I hope it can help you! Where can I find the answer to Alibaba ID: [Alibaba]-[My]. 1. First open the Alibaba software. After entering the homepage, we need to click [My] in the lower right corner; 2. Then after coming to the My page, we can see [id] at the top of the page; Alibaba Is the ID the same as Taobao? Alibaba ID and Taobao ID are different, but the two

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

Some of our readers encountered event ID4660. They're often not sure what to do, so we explain it in this guide. Event ID 4660 is usually logged when an object is deleted, so we will also explore some practical ways to fix it on your computer. What is event ID4660? Event ID 4660 is related to objects in Active Directory and will be triggered by any of the following factors: Object Deletion – A security event with Event ID 4660 is logged whenever an object is deleted from Active Directory. Manual changes – Event ID 4660 may be generated when a user or administrator manually changes the permissions of an object. This can happen when changing permission settings, modifying access levels, or adding or removing people or groups

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

Where can I check the Tencent Video ID? There is an exclusive ID in the Tencent Video APP, but most users do not know how to check the Tencent Video ID. Next is the graphic tutorial on how to check the Tencent Video ID brought by the editor for users who are interested. Users come and take a look! Tencent Video Usage Tutorial Where to check Tencent Video ID 1. First open the Tencent Video APP and enter the special area through [Personal Center] in the lower right corner of the main page; 2. Then enter the Personal Center page and select the [Settings] function; 3. Then go to Settings page, click [Exit Account] at the bottom; 4. Finally, you can view the exclusive ID number on the page shown below.

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: 1. hide() method, introduce the jQuery library into the HTML page, you can use different selectors to hide the select element, the ID selector replaces the selectId with the ID of the select element you actually use; 2. css() method, use the ID selector to select the select element that needs to be hidden, use the css() method to set the display attribute to none, and replace selectId with the ID of the select element.

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 SelectChannelsGo concurrent programming using golang Introduction: Concurrent programming is an important area in modern software development, which can effectively improve the performance and responsiveness of applications. In the Go language, concurrent programming can be implemented simply and efficiently using Channels and Select statements. This article will introduce how to use golang for asynchronous processing methods of SelectChannelsGo concurrent programming, and provide specific

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

Commonly used distributed ID solutions In distributed systems, it is very important to generate globally unique IDs, because in distributed systems, multiple nodes generating IDs at the same time may cause ID conflicts. The following introduces several commonly used distributed ID solutions. UUIDUUID (Universally Unique Identifier) ​​is an identifier composed of 128 digits, which can guarantee global uniqueness because its generation algorithm is based on factors such as timestamp, node ID and so on. UUID can be generated using Java's own UUID class, as shown below: javaCopycodeimportjava.util.UUID;publicclassUuidGenerator{publicstat

See all articles