A B C D
001 BM 1 1001
001 AM 0 1002
002 BM 0 1003
002 AM 0 1004
002 BM 2 1005
003 BM 1 1006
003 AM 3 1007
........就这样的一个表现在想达到如下效果:
001 AM 1 1001
002 AM 2 1005
003 AM 4 1007也就是说A是group by字段 B字段的内容AM优先级别最高, C字段是每个汇总数量,D是关联A,B,C取出的字段!!
怎么样做到??? 各位????
001 BM 1 1001
001 AM 0 1002
002 BM 0 1003
002 AM 0 1004
002 BM 2 1005
003 BM 1 1006
003 AM 3 1007
........就这样的一个表现在想达到如下效果:
001 AM 1 1001
002 AM 2 1005
003 AM 4 1007也就是说A是group by字段 B字段的内容AM优先级别最高, C字段是每个汇总数量,D是关联A,B,C取出的字段!!
怎么样做到??? 各位????
select * from (select a,b,(select sum(c) from m1 a where a.a=b.a ),d from m1 b) c where c.b='AM'
结果为:
+------+------+------------------------------------------+------+
| a | b | (select sum(c) from m1 a where a.a=b.a ) | d |
+------+------+------------------------------------------+------+
| 001 | AM | 1 | 1002 |
| 002 | AM | 2 | 1004 |
| 003 | AM | 4 | 1007 |
+------+------+------------------------------------------+------+
我觉得你的D有问题,D是关联A,B,C,因为C是计算出来的,那就是A,B
如
001 AM 0 1002 //AM与1002关联
001 BM 1 1001
那D就应该是1002而非1001
这两个不是很明白
001 AM 1 1001
002 AM 2 1005
003 AM 4 1007
*/
create table Table1 (sa varchar(3),sb varchar(2),sc smallint,sd varchar(4))
insert into table1(sa,sb,sc,sd)
select '001' , 'BM' , 1 , '1001'
union
select '001' , 'AM' , 0 , '1002'
union
select '002' , 'BM' , 0 , '1003'
union
select '002' , 'AM' , 0 , '1004'
union
select '002' , 'BM' , 2 , '1005'
union
select '003' , 'BM' , 1 , '1006'
union
select '003' , 'AM' , 3 , '1007'select sa,sb,sc=(select sum(sc) from table1 where sa=ta.sa),sd=(select sd from table1 where sa=Ta.sa and sb=ta.sb) from table1 ta where sb=(select top 1 sb from table1 where sa=ta.sa)drop table table1
---- ---- ----------- ----
001 AM 1 1002
002 AM 2 1004
003 AM 4 1007
不好意思 我写的是是对的
我要比较B的字段,这个字段的AM优先级是最高,
可是这个比较完了 还要比较C字段,C的字段优先级别是数量最大的,所以我的
我从新排下:
A B C D
001 AM(换了)1 1001
001 AM 0 1002
002 BM 0 1003
002 AM 0 1004
002 BM 2 1005
003 BM 1 1006
003 AM 3 1007结果应该是:
001 AM 1 1001(因为这个B优先级别一样,这就要比较C字段了!!)
002 AM 2 1004(这个只比较B优先级别,因为B优先级别最高!!)
003 AM 4 1007(这个也和上面一样!!!)各位明白了吗??
select a,b,(select sum(c) from m1 e where e.a=f.a) c,d
from(select c.a,c.b,c.d from (select a.a,a.b,a.c,a.d from m1 a,
(select a,min(b) b from m1 group by a) b
where a.a=b.a and a.b=b.b ) c where c in (select max(c) from
(select a.a,a.b,a.c,a.d from m1 a,(select a,min(b) b from m1 group by a)
b where a.a=b.a and a.b=b.b ) d
where c.a=d.a)) f;
结果为:
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 001 | AM | 1 | 1001 |
| 002 | AM | 2 | 1004 |
| 003 | AM | 4 | 1007 |
+------+------+------+------+
3 rows in set (0.00 sec)
----------------------------------------------
我说过,sql是无所不能的
select e.a,e.b,f.c,e.d from
(select c.a,c.b,c.d from (select a.a,a.b,a.c,a.d from m1 a,
(select a,min(b) b from m1 group by a) b where a.a=b.a and a.b=b.b) c where c in
(select min(c) from
(select a.a,a.b,a.c,a.d from m1 a,
(select a,min(b) b from m1 group by a) b where a.a=b.a and a.b=b.b) d where c.a=d.a)) e,
(select a,sum(c) c from m1 group by a) f
where e.a=f.a
/
结果为:
A B C D
--- -- ---------- ----
001 AM 1 1002
002 AM 2 1004
003 AM 4 1007该语法同样适用与mysql