Wednesday, 7 August 2013

How to select multi record depand on some column's condition?

How to select multi record depand on some column's condition?

say there is a sql server table ,which contain 2 columns names: ID,value
it's content is :
ID value
1 30
1 30
2 50
2 50
3 50
when i run this query:
select ID, NEWID(),value from table1 order by ID
it's result seems like
1 30 E152AD19-9920-4567-87FF-C4822FD9E485
1 30 54F28C58-ABA9-4DFB-9A80-CE9C4C390CBB
2 50 ........
2 50 ........
3 50 4E5A9E26-FEEC-4CC7-9AC5-96747053B6B2
but what i want is : how many record of ID depand on (sum of value /30 )'s
result,for example of ID 2 ,it's value's sum is 50+50=100,and 100/30=3 ,so
ID 2 will display in query result three times
the final result i want is like this:
1 E152AD19-9920-4567-87FF-C4822FD9E485
1 54F28C58-ABA9-4DFB-9A80-CE9C4C390CBB
2 4E5A9E26-FEEC-4CC7-9AC5-96747053B6B2
2 ....
2 ....
3 D861563E-E01A-4198-9E92-7BEB4678E5D1
pls note ID of 2 display three times,wait for your helps,thanks.

No comments:

Post a Comment