Create table #sales_details
(
	sales_id int identity(1,1),
	item_id int not null,
	qty int not null,
	unit_price decimal(12,2) not null,
	sales_date datetime not null
)

insert into #sales_details (item_id,qty,unit_price,sales_date)
select 1001,5,200,'2012-09-03 11:16:28' union all
select 1001,2,200,'2012-09-04 19:22:11' union all
select 1002,15,1300,'2012-09-06 14:26:40' union all
select 1003,8,78,'2012-09-19 15:11:19' union all
select 1001,6,200,'2012-09-22 16:36:11' union all
select 1004,22,1000,'2012-09-23 16:51:34' union all
select 1004,11,1000,'2012-09-23 17:29:38' union all
select 1002,29,1300,'2012-09-23 18:20:10' union all
select 1002,6,1300,'2012-09-26 19:40:41' union all
select 1002,33,1300,'2012-09-30 20:26:29' 

Assume that you want to find out distinct item_id from the above table. You can use many methods. Some of them are listed below

--Method 1 : Use DISTINCT keyword

select distinct item_id from #sales_details 

--Method 2 : Use GROUP BY Clause
select item_id from #sales_details
Group by item_id

--Method 3 : UNION the same table
select item_id from #sales_details
UNION
select item_id from #sales_details 

--Method 4 : UNION the same table with not selecting any rows from secondly sepecified table
select item_id from #sales_details
UNION    --union会做一个distinct
select item_id from #sales_details where 1=0

--Method 5 : UNION the table with Empty result

select distinct item_id from #sales_details 

select item_id from #sales_details
UNION
select 0 where 1=0

--Method 6 : Use Row_number() function
select item_id from
(
	select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details
) as t
where sno=1

参考：distinct

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。