首先,phoneNo 和 UID 是有关联性的,就别放这张表里了,分两张表 然后你的要求实际上是行转列,网上很多的例子,请参考我的例子:http://topic.csdn.net/u/20090218/14/fa4cda82-8e7b-4838-9053-3f427bea9e94.html UID ArtName Quantity 101 A 8 101 B 15 101 E 1 102 D 6 104 F 5 104 D 6 UID phoneNo 101 11111 101 11111 101 11111 102 22222 104 99999 104 99999
首先,phoneNo 和 UID 是有关联性的,就别放这张表里了,分两张表 然后你的要求实际上是行转列,网上很多的例子,请参考我的例子:http://topic.csdn.net/u/20090218/14/fa4cda82-8e7b-4838-9053-3f427bea9e94.html UID ArtName Quantity 101 A 8 101 B 15 101 E 1 102 D 6 104 F 5 104 D 6 UID phoneNo 101 11111 101 11111 101 11111 102 22222 104 99999 104 99999
这个可以用行列转换来实现, select uid ,sum(case when ArtName=A then Quantity end) as A数量 ,sum(case when ArtName=B then Quantity end) as B数量 ,sum(case when ArtName=C then Quantity end) as c数量 ,sum(case when ArtName=D then Quantity end) as d数量 ,sum(case when ArtName=E then Quantity end) as e数量 ,sum(case when ArtName=F then Quantity end) as f数量 ,min(phoneNo) as phoneNo from 表名 group by uid
然后你的要求实际上是行转列,网上很多的例子,请参考我的例子:http://topic.csdn.net/u/20090218/14/fa4cda82-8e7b-4838-9053-3f427bea9e94.html UID ArtName Quantity
101 A 8
101 B 15
101 E 1
102 D 6
104 F 5
104 D 6 UID phoneNo
101 11111
101 11111
101 11111
102 22222
104 99999
104 99999
然后你的要求实际上是行转列,网上很多的例子,请参考我的例子:http://topic.csdn.net/u/20090218/14/fa4cda82-8e7b-4838-9053-3f427bea9e94.html UID ArtName Quantity
101 A 8
101 B 15
101 E 1
102 D 6
104 F 5
104 D 6 UID phoneNo
101 11111
101 11111
101 11111
102 22222
104 99999
104 99999
select
uid
,sum(case when ArtName=A then Quantity end) as A数量
,sum(case when ArtName=B then Quantity end) as B数量
,sum(case when ArtName=C then Quantity end) as c数量
,sum(case when ArtName=D then Quantity end) as d数量
,sum(case when ArtName=E then Quantity end) as e数量
,sum(case when ArtName=F then Quantity end) as f数量
,min(phoneNo) as phoneNo
from
表名
group by
uid