如何在sql中设置Multiple Distinct [英] How to set Multiple Distinct In sql

问题描述

大家好我是sqlserver的新手，我遇到了一个问题



I.ID     |      InvoiceNo   |    ProductName    | &NBSP&NBSP产品&NBSP&NBSP

页 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

页 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

页 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

 2         &NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile的Drum&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile的Drum&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile的Drum&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&N BSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP Botile的Drum&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP



这是我的纪录，我想表明这样的



记录

页 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 3&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP

 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 00001&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP COLOR2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 4&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP





如何其有可能请尽快回答谢谢

解决方案

是的。这是可能的





你需要这个

行号



聚合功能





< pre lang =SQL> 选择 ROW_NUMBER（） over （订单 按 i_id） as 行，InvoiceNo，productname，COUNT（product） as product_count 来自 s group by InvoiceNo，productname，I_ID

试试这个：

从table1组中选择不同的lid，invoiceno，productname，count（product）by 
 lid，invoiceno，productname 

阅读： http://www.w3resource.com/sql /select-statement/queries-with-distinct-multiple-columns.php [ ^ ]

Hello Everyone i am new in sqlserver and i am facing a problem that

I.ID       |     InvoiceNo   |   ProductName    |    Product   
 1                    00001                Color1              Botile       
 1                    00001                Color1              Botile       
 1                    00001                Color1              Botile       
 2                    00001                Color2              Botile Drum       
 2                    00001                Color2              Botile Drum       
 2                    00001                Color2              Botile Drum       
 2                    00001                Color2              Botile Drum       

This is my Records and i want to show records like this


 1                    00001                Color1              3       
 2                    00001                Color2              4       


how its possible please answer me soon thank you

解决方案

Yes .it is possible


You need this
Row number

Aggregate functions

select ROW_NUMBER() over(order by i_id)as row,  InvoiceNo,productname,COUNT(product) as product_count from s group by InvoiceNo,productname,I_ID

Try this:

select distinct lid, invoiceno, productname,  count(product) from table1 group by 
lid, invoiceno, productname

Read this : http://www.w3resource.com/sql/select-statement/queries-with-distinct-multiple-columns.php[^]

这篇关于如何在sql中设置Multiple Distinct的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！