{"id":2352,"date":"2012-11-17T13:44:14","date_gmt":"2012-11-17T13:44:14","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2352"},"modified":"2012-11-17T13:44:14","modified_gmt":"2012-11-17T13:44:14","slug":"%e5%8e%bb%e9%87%8d%e7%9a%84%e5%a4%9a%e7%a7%8d%e6%96%b9%e6%b3%95","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/11\/17\/%e5%8e%bb%e9%87%8d%e7%9a%84%e5%a4%9a%e7%a7%8d%e6%96%b9%e6%b3%95\/","title":{"rendered":"\u53bb\u91cd\u7684\u591a\u79cd\u65b9\u6cd5"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nCreate table #sales_details\r\n(\r\n\tsales_id int identity(1,1),\r\n\titem_id int not null,\r\n\tqty int not null,\r\n\tunit_price decimal(12,2) not null,\r\n\tsales_date datetime not null\r\n)\r\n\r\ninsert into #sales_details (item_id,qty,unit_price,sales_date)\r\nselect 1001,5,200,&#39;2012-09-03 11:16:28&#39; union all\r\nselect 1001,2,200,&#39;2012-09-04 19:22:11&#39; union all\r\nselect 1002,15,1300,&#39;2012-09-06 14:26:40&#39; union all\r\nselect 1003,8,78,&#39;2012-09-19 15:11:19&#39; union all\r\nselect 1001,6,200,&#39;2012-09-22 16:36:11&#39; union all\r\nselect 1004,22,1000,&#39;2012-09-23 16:51:34&#39; union all\r\nselect 1004,11,1000,&#39;2012-09-23 17:29:38&#39; union all\r\nselect 1002,29,1300,&#39;2012-09-23 18:20:10&#39; union all\r\nselect 1002,6,1300,&#39;2012-09-26 19:40:41&#39; union all\r\nselect 1002,33,1300,&#39;2012-09-30 20:26:29&#39; \r\n\r\nAssume that you want to find out distinct item_id from the above table. You can use many methods. Some of them are listed below\r\n\r\n--Method 1 : Use DISTINCT keyword\r\n\r\nselect distinct item_id from #sales_details \r\n\r\n--Method 2 : Use GROUP BY Clause\r\nselect item_id from #sales_details \r\nGroup by item_id\r\n\r\n--Method 3 : UNION the same table\r\nselect item_id from #sales_details \r\nUNION\r\nselect item_id from #sales_details \r\n\r\n--Method 4 : UNION the same table with not selecting any rows from secondly sepecified table\r\nselect item_id from #sales_details \r\nUNION    --union\u4f1a\u505a\u4e00\u4e2adistinct\r\nselect item_id from #sales_details where 1=0\r\n\r\n--Method 5 : UNION the table with Empty result\r\n\r\nselect distinct item_id from #sales_details \r\n\r\n\r\nselect item_id from #sales_details \r\nUNION\r\nselect 0 where 1=0\r\n\r\n--Method 6 : Use Row_number() function\r\nselect item_id from\r\n(\r\n\tselect row_number() over (partition by item_id order by item_id) as sno,* from #sales_details \r\n) as t\r\nwhere sno=1\r\n\r\n\r\n\u53c2\u8003\uff1a<a href=\"http:\/\/beyondrelational.com\/modules\/2\/blogs\/70\/posts\/18189\/different-ways-to-find-distinct-values.aspx?utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+beyondrelationalmain+%28Technology+stream+from+beyondrelational.com%29\">distinct<\/a>\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Create table #sales_details ( sales_id int identity(1,1 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2352","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2352"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2352\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}