Original posted: http://community.9cbs.net/expert/topic/3717/3717577.xml? Temp = .4141199 has such a table (there are three columns A, B, C): A B C 1 Two 20031 TWO 20051 TWO 20042 Four 20063 Four 2008 Now I want to synthesize a record of the same value as the same value, as follows: A B C 1 TWO 2004 (this value can also take 2003 or 2005) 2 Four 20063 Four 2008 How to write SQL statement? ? --test:
Create Table Table (a varchar (20), b varchar (20), c varchar (20)) INSERT table select '1', 'two', '2003'Union all Select' 1 ',' two ',' 2005 ' Union all select '1', 'two', '2004'Union All Select' 2 ',' Four ',' 2006'Union All Select '3', 'Four', '2008'go
SELECT A, B, (Select Top 1 C from WHERE A = da and b = db ORDER BY NEWID ()) AS C - Random Take the value of the C column, or MAX (C), MIN (C), AVG (C) FROM table DGROUP BY A, B --A, B combined group
DROP TABLE Table
- First test results: a b c ------------------------- - -------------------- 1 Two 20042 Four 20063 Four 2008
(The number of rows affects is 3 lines)
- Second test results:
A B C ------------------------------------------- ------------- 1 Two 20052 Four 20063 Four 2008
- Tenth test results:
A B C ------------------------------------------- ------------- 1 TWO 20032 Four 20063 Four 2008 (the number of rows affected is 3 lines)
- All values are random .........
