<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
原贴:http://community.csdn.net/Expert/topic/3717/3717577.xml?temp=.4141199
有这样的一张表(有三列a、b、c):
a b c
1 two 2003
1 two 2005
1 two 2004
2 four 2006
3 four 2008
现在我想把a、b列有相同值的记录合成一条记录,结果集如下:
a b c
1 two 2004(该值也可以取2003或2005)
2 four 2006
3 four 2008
这样的sql语句怎么写??
--测试:
createtable表(avarchar(20),bvarchar(20),cvarchar(20))
insert表select'1', 'two', '2003'
unionallselect'1', 'two', '2005'
unionallselect'1', 'two', '2004'
unionallselect'2', 'four', '2006'
unionallselect'3', 'four', '2008'
go
selecta,b
,(selecttop1cfrom表wherea=d.aandb=d.borderbynewid())asc
--随机取c列的值,也可以用Max(c),Min(c), AVG(c)
from表d
groupbya,b --a,b组合分组
droptable表
--第一次测试结果:
a b c
------------------------------------------------------------
1 two 2004
2 four 2006
3 four 2008
(所影响的行数为3行)
--第二次测试结果:
a b c 1
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
原贴:http://community.csdn.net/Expert/topic/3717/3717577.xml?temp=.4141199
有这样的一张表(有三列a、b、c):
a b c
1 two 2003
1 two 2005
1 two 2004
2 four 2006
3 four 2008
现在我想把a、b列有相同值的记录合成一条记录,结果集如下:
a b c
1 two 2004(该值也可以取2003或2005)
2 four 2006
3 four 2008
这样的sql语句怎么写??
--测试:
createtable表(avarchar(20),bvarchar(20),cvarchar(20))
insert表select'1', 'two', '2003'
unionallselect'1', 'two', '2005'
unionallselect'1', 'two', '2004'
unionallselect'2', 'four', '2006'
unionallselect'3', 'four', '2008'
go
selecta,b
,(selecttop1cfrom表wherea=d.aandb=d.borderbynewid())asc
--随机取c列的值,也可以用Max(c),Min(c), AVG(c)
from表d
groupbya,b --a,b组合分组
droptable表
--第一次测试结果:
a b c
------------------------------------------------------------
1 two 2004
2 four 2006
3 four 2008
(所影响的行数为3行)
--第二次测试结果:
a b c 1
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>