A special query (http://searchdatabase.techtarget.com.cn/tips/206/2295706.shtml) for SQL Server)

I have a question about SQL Server queries. The commodity code of a query statement is unique, and the unit price of the same commodity is the lowest record. The problem is as follows:

Product Code quantity unit price supplier 001 0.3 Industrial and Commercial Enterprises 001 50 0.1 AB Enterprise 002 100 1.2 OK Enterprise 003 200 2.4 AB Enterprise 003 500 1.2 SQ Enterprise

The product code of the query statement is unique. The unit price of the same product is the lowest. The result is as follows:

Product Code quantity unit price supplier 001 50 0.1 AB Enterprise 002 100 1.2 OK Enterprise 003 500 1.2 SQ Enterprise

Answer 1:

-- Test environment Declare @ t table (product code varchar (10), quantity int, unit price decimal (4, 2), supplier varchar (10 )) Insert into @ t select '001', 20, 0.3, 'business' Union all select '001', 50, 0.1, 'AB enterprise' Union all select '002 ', 100, 1.2,' OK enterprise' Union all select '003 ', 200, 2.4,' AB enterprise' Union all select '003 ', 500, 1.2, 'sq enterprise' -- Query Select * from @ t Where not exists (select 1 from @ t where commodity code = A. Commodity Code and unit price <A. Unit Price) -- Result Product Code quantity unit price supplier ------------------------------------- 001 50. 10 AB Enterprise 002 100 1.20 OK Enterprise 003 500 1.20 SQ Enterprise (The number of affected rows is 3)

Answer 2:

Declare @ t table (product code varchar (10), quantity int, unit price decimal (4, 2), supplier varchar (10 )) Insert into @ t select '001', 20, 0.3, 'business' Union all select '001', 50, 0.1, 'AB enterprise' Union all select '002 ', 100, 1.2,' OK enterprise' Union all select '003 ', 200, 2.4,' AB enterprise' Union all select '003 ', 500, 1.2, 'sq enterprise' Union all select '004 ', 500, 1.2, 'sq enterprise' Union all select '003 ', 500, 1.2, 'sq enterprise' Select a. * from @ t a, (select Product Code, min (unit price) aaa from @ t group by product code) as aa Where aa. Commodity Code = a. Commodity Code and a. Unit Price = aa. aaa