试试这个:
--1.第一个问题
select BM,PH,GYS,sum(RKS) as RKS
from 表B
where BM in (select WL 表A where ZJ = '是')
group by BM,PH,GYS
--2.第二个问题
select BM,PH,GYS,sum(RKS) as RKS
from 表B
where BM in (select WL 表A where ZJ = '否')
group by BM,PH,GYS
--1.第一个问题
select BM,PH,GYS,sum(RKS) as RKS
from 表B
where BM in (select WL 表A where ZJ = '是')
group by BM,PH,GYS
--2.第二个问题
select BM,PH,GYS,sum(RKS) as RKS
from 表B
where BM in (select WL 表A where ZJ = '否')
group by BM,PH,GYS
解决方案 »
- SQL2008 存储过程 传入起始日期,终止日期.返回一个由日期天的个位数组成的数组
- 求sql语句。。
- 求一SQL语句,急!!直接在SQLSERVER中同步将一个表中的两个字段的值插入到另外一个数据库中的想对应的两字段中
- MS SQL2000的数据占用内存以及CPU时间问题.
- 安装完vs2005后,再安装上sql server2005没有management studio 怎么办?
- 关于sql server2000的菜鸟问题。。
- 通过视图去查询,能用到索引吗
- 真的是巨难问题!!!如果能解决另开帖子补分!!
- 要访问jsp的网站,必须要打端口号吗?比如,我的ip是61.13.123.123,要访问jsp网页,就要打http://61.13.123.123:8080/index.jsp吗?谢!
- 如何删除指定数据库名下所有表记录信息?帮项有分,在线等
- SQL Server 2012安装完成后无法打开
- 新手求教问题~~
-- 质检表1
select b.BM,b.PH,b.GYS,sum(b.RKS) '汇总数'
from 表B b
inner join 表A a on b.BM=a.WL
where a.ZJ='是'
group by b.BM,b.PH,b.GYS
-- 非质检表2
select b.BM,b.PH,b.GYS,sum(b.RKS) '汇总数'
from 表B b
inner join 表A a on b.BM=a.WL
where a.ZJ='否'
group by b.BM,b.PH,b.GYS
CRKDH FPH LOTNO SWRKS
90006 IN13-0885 ABC 90.00
90067 IN13-0844 ZZZ 150.00
90088 IN13-0899 FFF 420.00
但是BO_ZHKCSJBZY 中还有其他的字段,想根据上面的结果带出其他所有字段的数据出来,包括总数SWRKS也一起带出来。
想要效果是下面,该怎么改?
CRKDH FPH LOTNO SWRKS ziduan1 ziduan2 ziduan3...
90006 IN13-0885 ABC 90.00 a b c
90067 IN13-0844 ZZZ 150.00 b c a
90088 IN13-0899 FFF 420.00 w f f
下面是执行的语句:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO。
CRKDH FPH LOTNO SWRKS
90006 IN13-0885 ABC 90.00
90067 IN13-0844 ZZZ 150.00
90088 IN13-0899 FFF 420.00
但是BO_ZHKCSJBZY 中还有其他的字段,想根据上面的结果带出其他所有字段的数据出来,包括总数SWRKS也一起带出来。
想要效果是下面,该怎么改?
CRKDH FPH LOTNO SWRKS ziduan1 ziduan2 ziduan3...
90006 IN13-0885 ABC 90.00 a b c
90067 IN13-0844 ZZZ 150.00 b c a
90088 IN13-0899 FFF 420.00 w f f
下面是执行的语句:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO。
from tb a
where exists (select 1 from (group by 的结果集) b where a.主键=b.主键)
这样可以带出其他数据
CRKDH FPH LOTNO SWRKS
90006 IN13-0885 ABC 90.00
90067 IN13-0844 ZZZ 150.00
90088 IN13-0899 FFF 420.00
但是BO_ZHKCSJBZY 中还有其他的字段,想根据上面的结果带出其他所有字段的数据出来,包括总数SWRKS也一起带出来。
想要效果是下面,该怎么改?
CRKDH FPH LOTNO SWRKS ziduan1 ziduan2 ziduan3...
90006 IN13-0885 ABC 90.00 a b c
90067 IN13-0844 ZZZ 150.00 b c a
90088 IN13-0899 FFF 420.00 w f f
下面是执行的语句:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO。是这样吗,把其他字段加进去:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS ,ziduan1 ziduan2 ziduan3...
from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO,ziduan1 ziduan2 ziduan3...
CRKDH FPH LOTNO SWRKS
90006 IN13-0885 ABC 90.00
90067 IN13-0844 ZZZ 150.00
90088 IN13-0899 FFF 420.00
但是BO_ZHKCSJBZY 中还有其他的字段,想根据上面的结果带出其他所有字段的数据出来,包括总数SWRKS也一起带出来。
想要效果是下面,该怎么改?
CRKDH FPH LOTNO SWRKS ziduan1 ziduan2 ziduan3...
90006 IN13-0885 ABC 90.00 a b c
90067 IN13-0844 ZZZ 150.00 b c a
90088 IN13-0899 FFF 420.00 w f f
下面是执行的语句:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO。是这样吗,把其他字段加进去:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS ,ziduan1 ziduan2 ziduan3...
from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO,ziduan1 ziduan2 ziduan3...
但是这样是根据CRKDH,FPH,LOTNO,ziduan1 ziduan2 ziduan3...这些好多个字段来分组了呢,
而不是根据CRKDH,FPH,LOTNO这三个字段分组。怎么看?
CRKDH FPH LOTNO SWRKS
90006 IN13-0885 ABC 90.00
90067 IN13-0844 ZZZ 150.00
90088 IN13-0899 FFF 420.00
但是BO_ZHKCSJBZY 中还有其他的字段,想根据上面的结果带出其他所有字段的数据出来,包括总数SWRKS也一起带出来。
想要效果是下面,该怎么改?
CRKDH FPH LOTNO SWRKS ziduan1 ziduan2 ziduan3...
90006 IN13-0885 ABC 90.00 a b c
90067 IN13-0844 ZZZ 150.00 b c a
90088 IN13-0899 FFF 420.00 w f f
下面是执行的语句:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO。是这样吗,把其他字段加进去:
select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS ,ziduan1 ziduan2 ziduan3...
from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO,ziduan1 ziduan2 ziduan3...
但是这样是根据CRKDH,FPH,LOTNO,ziduan1 ziduan2 ziduan3...这些好多个字段来分组了呢,
而不是根据CRKDH,FPH,LOTNO这三个字段分组。怎么看?select CRKDH,FPH,LOTNO,SUM(SWRKS) AS SWRKS ,max(ziduan1) ziduan1, max(ziduan2) ziduan2, max(ziduan3) ziduan3 ...
from BO_ZHKCSJBZY
where HWBM in
(select WL from BO_ZSJB where ZJ = '是' AND
BINDID=(SELECT TOP (1) BINDID from BO_ZSJBBT GROUP BY BINDID,SCSJ ORDER BY SCSJ DESC))
and isend=1 group by CRKDH,FPH,LOTNO